GoogleSheets.png

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.

Property Settings

As seen above, select the "Google Sheets" tab in the Property Panel to update this widget's following property types: 

Google Sheets Connection

Screen_Shot_2020-04-24_at_9.03.48_AM.png

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

Request Frequency

Check the "Enable" box to activate this property feature set.

Screen_Shot_2020-04-24_at_9.03.55_AM.png

Request every # sec - Every number of seconds Singular will request data from your Google Sheet.

Row Sequencing

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.

Screen_Shot_2020-04-24_at_9.03.59_AM.png

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.

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.

GoogleSheetWidgetExample.png

Before using this widget, you must do some prep work in your own Google account.

Prepare your Google Account and Google Sheet

Prepare your Google Account

1. Create a Project

Note

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”.

02-create-project.jpg

Creating the project can take one or two minutes. Your project will be listed in the Cloud Resource Manager.

03-project-overview.jpg

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.

05-add-google-sheets-api.jpg

Next, enable “Google Sheets API”.

06-enable-google-sheets-api.jpg

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.
07-open-project-credentials.jpg

Select “Create Credentials” and then hit “API key”.

08-select-create-api-key.jpg

Next, select “Restrict Key”.

09-select-restrict-api-key__1_.jpg

Then, select "HTTP referrers (web sites)" in the Application restrictions menu.

10-define-application-restrictions.jpg

Next, in the Website restrictions section, hit "Add an Item" and type in two new items (as seen pictured below):

https://app.singular.live

https://beta.singular.live

11-define-website-restrictions.jpg

Define API restrictions. Select your Google Sheets API.

12-define-api-restrictions.jpg

Screen_Shot_2020-04-27_at_11.05.32_AM.png

Save your settings.

13-google-sheets-api-key.jpg

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:

Screen_Shot_2020-04-27_at_4.45.38_PM.png

Link Sharing settings

 

Note

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). 

Screen_Shot_2020-04-27_at_4.25.30_PM.png

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.

Thumbnail_GoogleSheets_01.jpg

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:

Screen_Shot_2020-04-27_at_4.29.05_PM.png Screen_Shot_2020-04-27_at_4.29.25_PM.png
Screen_Shot_2020-04-28_at_3.35.25_PM.png

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.

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.

Screen_Shot_2020-04-27_at_4.42.05_PM.png

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.

Google_Sheets_Connection.png

Spreadsheet ID: You can find your Sheet's URL between /d/ and /edit

Screen_Shot_2020-04-27_at_4.44.32_PM.png

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.

13-google-sheets-api-key.jpg

2C - Edit the Google Sheets Widget

Now select the "Edit" button to dig into the Google Sheets Widget.

Screen_Shot_2020-04-28_at_4.10.26_PM.png

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:

Screen_Shot_2020-04-28_at_9.48.21_AM.png

A list of other individual Widget Nodes:

Screen_Shot_2020-04-28_at_9.49.11_AM.png

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. 

Screen_Shot_2020-04-28_at_9.53.36_AM.png

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.

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.

Screen_Shot_2020-04-28_at_9.22.02_AM.png

Changing Manual row #

 

Notice that the overlay changes with the data from the second row in your Google Sheet!

Screen_Shot_2020-04-28_at_9.16.05_AM.png

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.

Google_Sheets_Connection.png

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.

Screen_Shot_2020-04-28_at_9.42.39_AM.png

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! 

Screen_Shot_2020-04-28_at_12.27.34_PM.png

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.

Screen_Shot_2020-04-28_at_12.27.38_PM.png

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?).

Google_Sheets_Connection.png

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).

Screen_Shot_2020-04-28_at_2.45.03_PM.png

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.

Screen_Shot_2020-04-28_at_2.48.21_PM.png

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.

Screen_Shot_2020-04-28_at_2.48.51_PM.png

"Base" elements being connected to "club1Color" or "club2Color" Control Nodes

 

Your final set of connected Control Nodes should the same as pictured below:

Screen_Shot_2020-04-28_at_2.49.45_PM.png

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.

Screen_Shot_2020-04-28_at_2.50.28_PM.png

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!

Screen_Shot_2020-04-28_at_3.33.14_PM.png

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".

Screen_Shot_2020-04-28_at_3.37.24_PM.png

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.

Screen_Shot_2020-04-28_at_3.37.30_PM.png

Go back up a level in the Composition Tree and. . . voila! Your Fullscreen now has a title linked to your Google Sheet data!

Screen_Shot_2020-04-28_at_3.35.25_PM.png

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.

Was this article helpful?
6 out of 6 found this helpful

Comments

0 comments

Please sign in to leave a comment.