The Google Sheets Widget connects your custom Google Sheet data to a Composition. Basically, a Google Sheet can serve as a data source for your overlays.
To learn how to add a Widget to your Composition, click here.
As seen above, select the "Google Sheets" tab in the Property Panel to update this widget's following property types:
Spreadsheet Id - Id number of your Google Sheet spreadsheet. This value can be found in the url between "/d/" and "/edit".
Value Range - The range of your desired columns and rows of data in A1 Notation.
API Key - API key
Check the "Enable" box to activate this property feature set.
Request every # sec - Every number of seconds Singular will request data from your Google Sheet.
Check the "Enable" box to activate this property feature set.
Row sequencing should be enabled if you want to visualize your Sheet data in either single row format or as a ticker that runs through your rows of data.
Sequence options - Two options include "Manual row #" and "Advance every # sec". Manual row # is for selecting a specific row to be visualized. Advance every # sec is for sequencing through all rows of data. Each option has their corresponding settings available below.
Manual row # - Select your row number from your Sheet.
Advance every # sec - The selected number of seconds it takes to sequence from one row to the next.
The Singular Google Sheet Widget creates a connection to rows and columns within a Google Sheet data and a Singular Composition.
Basically, it can turn spreadsheet data into an beautiful looking overlay.
Before using this widget, you must do some prep work in your own Google account.
Prepare your Google Account
1. Create a Project
Creating a new project is not necessarily required. You can skip this step if you want to use an existing project and continue with “Enable Google Sheets API”.
Open Google Chrome and log into your Google Account. Create a project, here, and define a project name e.g., “Google Sheets Widget”.
Creating the project can take one or two minutes. Your project will be listed in the Cloud Resource Manager.
2. Enable Google Sheets API
Open Google Chrome and log into your Google Account. Next, open the Google Developer Console and select your project.
Search in the toolbar for "Google Sheets API" and add it.
Next, enable “Google Sheets API”.
3. Generate an API Key
There are a few steps in your Google account to start, specifically generating a Google API key. Essentially the API is the code or "key" that governs the access point(s) between Singular's Google Sheets Widget and Google.
Open the “Credentials” menu for your “Google Sheets” project in the .Note, your project is listed in the top toolbar.
Select “Create Credentials” and then hit “API key”.
Next, select “Restrict Key”.
Then, select "HTTP referrers (web sites)" in the Application restrictions menu.
Next, in the Website restrictions section, hit "Add an Item" and type in two new items (as seen pictured below):
Define API restrictions. Select your Google Sheets API.
Save your settings.
Now you can select the clipboard icon to the right of your newly created Google Sheets Widget API key. You'll need this for later use in your Singular Composition.
Prepare your Google Sheet
1. Create a new Google Sheet
Go ahead and create a brand new Google Spreadsheet.
Make sure your share settings are set for everyone and not restricted to your organization. We recommend setting it to the setting pictured below:
Link Sharing settings
Once you have the sheet connected to the GS Widget you can go back to the sharing permissions of the Google Sheet and set it accordingly. If you mess up and lose connection it's usually due to the fact that the GS Widget can't access the sheet. Keep the browser console open when doing this troubleshooting. It's an essential as it lists access errors if the GS Widget can't connect to the GSheet.
2. Prep the data
Once you have created your new spreadsheet and set the share permissions, copy the data from this sheet into yours (pictured below).
Note that there are two "tabs" of data. Copy each tab over and make sure to keep the naming of the tabs as "Sheet1" and "Sheet2".
1 - Download the Google Sheets Widget Example Theme
Head over to the Singular Dashboard and open up the Singular Marketplace. Go to the Resources section and download the "Google Sheets Widget Example" Theme.
Google Sheets Widget Example Theme
Go ahead and open up your newly downloaded theme into Composer. In this example, we have prepared three Subcompositions, each using their own Google Sheets Widget (they aren't visible at the root level because they have not yet been connected to data).
This tutorial will teach you how to connect your Google Sheet data to these prepped overlays. When populated with data, they will look like the following:
Lower Third, Ticker and Fullscreen Overlays with Google Sheet Data
2 - Complete the Lower Third Overlay
2A - Select the Google Sheets Widget
Dig down one level into the "Lower Third" Subcomposition and select the "Google Sheets" widget in the Composition Tree. Then make sure you have the "Google Sheets" tab selected in the Property Panel.
Google Sheets Widget selected
2B - Fill in the Google Sheets Connection Values
Notice that the "Google Sheets Connection" fields are blank or may have default text. This is what you will have to populate so that your Composition knows where to get the data.
Spreadsheet ID: You can find your Sheet's URL between /d/ and /edit
Value Range: This has to be in A1 notation so if typed incorrectly, the Google Sheet Widget will not work. For this tutorial, go ahead and type in "Sheet1!A2:I7".
Take notice that we are accounting for the row of header information - this is vital. This row is where the Google Sheets Widget will pull data to create Widget Nodes. Also note that custom Sheet names must be non-empty strings and cannot contain ".", "#", "$", "/", "[", or "]"
API Key: If you forgot where your newly created API key is, jump back to your Google API (pictured below) and hit the clipboard button next to your newly created Google Sheets Widget API.
2C - Edit the Google Sheets Widget
Now select the "Edit" button to dig into the Google Sheets Widget.
Once the connection between the Google Sheet and your Google Sheets Widget has been made, you should see the proper Widget Nodes appear in two different styles within the Google Sheets Widget in the Data Interface and Control Panel.
A list of other individual Widget Nodes:
Now you need to connect each pre-built overlay element inside of this Google Sheets Widget to the individual Widget Nodes that have been pulled from the header values in your Google Sheet. Note that for this specific overlay, we won't need to use the provided JSON Widget Node.
Each pre-built overlay element except the "matchweekBase" should be connected to a Widget Node.
Connecting Elements to Widget Nodes
You might get tripped up with the Bases elements. These elements should be connected to the club color Widget Nodes.
2D - Test Your Work
Once you've finished connecting all the pre-built elements to the Widget Nodes, move back up a level in the Composition Tree to the Lower Third Subcomposition. Now go ahead and change the Manual row # (in the Row Sequencing Box) to another number, like 2.
Changing Manual row #
Notice that the overlay changes with the data from the second row in your Google Sheet!
Overlay Data Updates
You've now completed the connection between each overlay element and each row of data in your Google Sheet. Congrats! You can now manually change the row number and see the overlay update.
3 - Complete the Ticker Overlay
The process for connecting the Ticker Overlay to the Google Sheet data is essentially the same process as for the Lower Third.
3A - Select the Google Sheets Widget
Dig down one level into the "Ticker" Subcomposition and select the "Google Sheets" widget in the Composition Tree. Then make sure you have the "Google Sheets" tab selected in the Property Panel.
3B - Fill in the Google Sheets Connection Values
Again, for this Google Sheets Widget, fill in the Spreadsheet ID, Value Range and API key. These should all be the same as defined in your Lower Third's values.
3C - Edit the Google Sheets Widget
Now, dig into the the Google Sheets Widget (by selecting the "Edit" button) and see all the prepared elements. Just like with the Lower Third overlay, these just need to be connected to the newly created Widget Nodes. Remember, all the Base elements should be connected to either the club1Color or club2Color.
connecting prepared elements to Widget Nodes
3D - Test Your Work
Great! Now move back up a level in the Composition Tree and notice that your ticker is running with your Google Sheet data!
successfully running Ticker
But why is this automatically running through rows of data instead of manually staying on one row? Take a look at the "Row Sequencing Box" and see that the Sequence Options have been set to "Advance every # sec". It is also set to advance every 5 seconds.
This setting makes the Google Sheet Widget sequence through each row of your data at 5 second intervals instead of staying at a Manual row #. Essentially turning your data into a ticker!
4 - Complete the Fullscreen Overlay - For Advanced Users
The process for connecting the Fullscreen Overlay to the Google Sheet data is slightly different than the process for the Lower Third and the Ticker. This section of the tutorial is for advanced users. You'll see why in a minute.
4A - Select the Google Sheets Widget
Dig down one level into the "Fullscreen" Subcomposition and select the "Google Sheets" widget in the Composition Tree. Then make sure you have the "Google Sheets" tab selected in the Property Panel.
4B - Fill in the Google Sheets Connection Values
Again, fill in the Spreadsheet ID, Value Range and API key. These should all be the same as defined in your Lower Third and Ticker overlays (we are using the same set of data, aren't we?).
4C - Edit the Google Sheets Widget
Now, dig into the the Google Sheets Widget (by selecting the "Edit" button) and take notice of the added Table Widget. You'll now be working with Google Sheet's JSON data (as seen pictured below in the Data Interface and Control Panel).
Preparing to work with the "_tableJSON" Widget Node
This JSON data from your Google Sheet is perfect to use with a Table Widget!
We have prepared the overlay elements inside of the provided Table Widget, all you have to do is dig into the Table Widget's Subcomposition (hit the Edit button in the Style property box) and create connected Control Nodes for most of the elements provided. These Control Nodes will be necessary for the Table Widget to do its job properly.
Creating connected Control Nodes for all table elements
Note that for all the "Base" elements, these will have to be connected to Control Nodes titled either "club1Color" or "club2Color". The image below shows the "club2ScoreBase" element being connected to a "club2Color" Control Node. This Control Node name is important because it has to match the header text provided in the Google Sheet.
"Base" elements being connected to "club1Color" or "club2Color" Control Nodes
Your final set of connected Control Nodes should the same as pictured below:
Final list of Table Widget Control Nodes
Ok, you just completed the hardest step. Now go up a level in the Composition Tree (back to the root of the Google Sheets Widget).
The last thing you need to do to get this thing to work properly is connect your Table Widget content to the Google Sheets Widget JSON. Do this by selecting the "Content" property title within the Table tab and choose the "_tableJson" Widget Node option.
Connecting Google Sheets Widget JSON to Table Widget
4D - Test Your Work
Ok, perfect! If you followed all the steps correctly, the overlay will start to populate with your Google Sheet data!
Fullscreen overlay populated with Google Sheet data
Really quick, notice that there isn't a title for your Fullscreen. Go back down a level into the Google Sheets Widget and see that there is a Text element titled "headerText".
We didn't need this to be inside the Table Widget but for it to access Google Sheet data, it still has to live inside the Google Sheets Widget. Go ahead and connect it to the "matchweek" Widget Node.
Go back up a level in the Composition Tree and. . . voila! Your Fullscreen now has a title linked to your Google Sheet data!
Fullscreen overlay title populated with Google Sheet data
You're done! You've learned several different ways to work with the Google Sheets Widget in Composer. Now, let the creative juices flow and build some beautiful overlays, populated with your own Google Sheet data.
If you want to play around with this Widget some more, you can work with "Sheet2" of the provided Google Sheet data.