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.
Remember, any underlined widget properties can be connected to a Control Node or a Data Node.
Properties
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. For example, Sheet1!A1:B2
API Key - Google account API Key. This can be created following this tutorial.
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.
Quota Limits
It is important to be aware that Google defines a quota of 60 requests per 60 seconds for each API key. Learn more about Google API limits here.
If you are using multiple Google Sheet widgets in a single Composition, you could hit this limit quickly. In these cases, the Google Sheets Widget prints tons of error messages in the console indicating the issue.
You can avoid running into API quotas by:
- Reducing the number of Google Sheet Widgets in the same Composition
- Reducing the refresh frequency and increasing the duration between requests
- Reducing the number of Output URLs to a minimum
- Using multiple Google Accounts (and subsequently using different API Keys)
Usage Tutorial
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.
1. Create a Google API Key
You'll need to generate an API Key in your Google Account and then copy that key.
2. 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 the permissions to "Anyone on the internet can find and view"
Next, copy the sample data from this sheet into yours. Note that there are multiple "tabs" of data. Copy each tab over and make sure to keep the naming of the tabs the same.
Please Note
the following characters can not be included in Sheet Header naming:
- . (period)
- $ (dollar sign)
- [ (left square bracket)
- ] (right square bracket)
- # (hash or pound sign)
- / (forward slash)
3 - 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).
4 - Complete The Overlays
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
Let's connect these overlays with your Google Sheet data. This next portion of the tutorial is broken up into three parts - Completing a Lower Third, Completing a Ticker and Completing a Fullscreen.
A - 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
B - 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.
C - 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.
JSON:
A list of other individual Widget Nodes:
Each pre-built overlay element except the "matchweekBase" should be connected to a Widget Node. 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.
Connecting Elements to Widget Nodes
Important
You might get tripped up with the Bases elements. These elements should be connected to the club color Widget Nodes.
D - 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.
The process for connecting the Ticker Overlay to the Google Sheet data is essentially the same process as for the Lower Third.
A - 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.
B - 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.
C - 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
D - 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!
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.
6A - 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.
6B - 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?).
6C - 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
6D - 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.
Comments
2 comments