Using Google Sheets to Connect Data

Introduction

Google Sheets, part of the Google Suite, is a spreadsheet application that can be used for storing and sharing data. Data regarding your viewers can be added to a spreadsheet and then merged with the video you created, generating a personalized link for each viewer in real time.

When using Google Sheets, your data is never stored on SundaySky servers. When a viewer clicks to watch a video, SundaySky pulls records from Google Sheets in real time and creates a personalized video, ensuring the use of the latest data.

The video link created for each viewer is unique and has a "sticky" ID. When you update viewer data in the spreadsheet or update the video content, the video is updated automatically. There is no need to resend viewers a new link as they will see the updated video when they revisit the original link that they received.

In this end-to-end article, you'll find comprehensive guidelines for working with Google Sheets, beginning with setting up a data library and concluding with generating video links for your viewers.


Step 1: Select or Create a Data Library
Step 2: Set up the Google Sheets Spreadsheet
Step 3: Select the Google Sheets Data Connector
Step 4: Integrate Google Sheets with SundaySky
Step 5: Map the Data Fields
Step 6: Configure the Content
Step 7: Publish the Video
Step 8: Get (Share) the Video Links

Step 1: Select or Create a Data Library

Based on how you want to personalize your video, you'll need to connect the video to a data library that includes the data fields for creating the personalization that you want. SundaySky supports two methods of personalization: using a personalization token and audience messaging.

  • When using a personalization token, you only need to add data fields to the data library before you can use them.
  • For audience messaging, you need to have data fields in the data library along with their corresponding values. For example: the data field department is added, along with its values: Finance, HR, and Marketing.

Before creating a new data library, we recommend that you review the existing data libraries in your account to see if one of them matches your requirements as the same data library can be used for multiple videos. If you are selecting an existing data library, it needs to meet all the following conditions:

  1. The data fields in the library are those that you require for personalization.
  2. The data library is connected to the Google Sheets data connector.
  3. The Google Sheets data connector is connected to the spreadsheet that holds your viewers' data.

If you find a data library that has the data fields that you need but the library's data connector is not Google Sheets, do not change the data connector in the library as this will negatively impact existing videos. In such a case, you will need to create a new data library.

To learn how to select an existing library, see Selecting an Existing Data Library.
If you need to create a new library from scratch, see Creating a New Data Library.

At the end of Step 1:
A data library is selected for the video. This can be an existing data library or a new one.

Data_library.png


Step 2: Set up the Google Sheets Spreadsheet

In this step, you'll set up the spreadsheet that holds the data which will be used for personalization.

Begin by reviewing the data fields that exist in the data library used by the video. The column headers in the spreadsheet need to match the data fields that are in the data library (Step 1).

In the example below, there are three data fields in the data library (First Name, Site, and Department).

Data_library_example.png

In the Google Sheets spreadsheet, the names of these data fields are the column headers:

Column_headers_example.png

In addition to the column headers based on the data fields, the spreadsheet must always include a column named id. This is the column that will hold each viewer's unique identifier, ensuring that viewers see a customized video created just for them. The column header id can be entered in lower or upper case.

id_column.png

In each row is the specific data pertaining to each viewer. Whenever required, this data can be edited or deleted. If a value in a cell is calculated using a formula, the output value is passed to SundaySky and not the formula on which it is based. At any time, new rows can also be added.

viewer_data.png

Following are general specifications that the spreadsheet needs to adhere to:

1. Every column must have a column header.
2. Each column header name must be unique and differ from the other column header names.
3. Every row (record) must have a unique id value. This is for two reasons:
a. A unique video link is created for each record.
b. The id value is used in detailed reports.
4. Values for all column headers (besides id) are optional. If the value is missing, the content in the video will be configured according to the default setting.

 

At the end of Step 2:
The Google Sheets spreadsheet, containing the viewers' data, is set up. As mentioned, the data in this spreadsheet can be updated continuously.

step_2_completed.png


Step 3: Select the Google Sheets Data Connector

bell-on.png If you selected a data library that is already connected to the Google Sheets data connector, skip to Step 5.

The connector is what enables the connection between the data fields that you defined in the data library and the actual viewer data saved in Google Sheets. Put simply, the connector is what determines how data flows from your spreadsheet to SundaySky.
For additional guidelines, refer to the notes for Step 3 below.

1.

Open the relevant video in the Studio.

2.

Select Data in the sidebar.

3.

Click Select in the data connector section.

 

Select_data_connector.png

4.

Select the Google Sheets data connector and then click Select at the bottom of the window.

 

Google_Sheets_Marketplace_120.png

5.

(Optional) If you have not yet integrated Google Sheets with your SundaySky account, you will now be prompted to do so. See Step 4: Integrate Google Sheets with SundaySky to learn how.

6.

Enter the URL of the Google spreadsheet and then click anywhere outside of the field.

 

Enter_URL.png

 

The Sheet drop-down list is populated with all the sheets in the spreadsheet.

 

Sheet_drop_down.png

7.

Select the relevant sheet and then click Select at the bottom of the window.

 

select_sheet.png

 

The data connector is attached to the library and the following message is displayed.

 

Data_connector_confirmation.png

8.

Click Map Fields to continue directly to field mapping.
▶ If you prefer mapping at a later time, click I'll Do It Later to return to the data library.

 

Map_fields.png


Notes for Step 3:

  • Once the Google Sheets data connector is connected to the data library, do not change the name of the sheet in the spreadsheet.
    For example:
    If the Group A sheet was defined as the sheet to use in the Google Sheets configuration, do not rename this sheet in the Google spreadsheet. Renaming the sheet will disconnect the configuration.

    Sheet_name.png

  • If required, you can change the sheet that you defined in the configuration or even select a new Google Sheets URL altogether. To do this, click the three-dot menu in the data library and then select Change Google Spreadsheet. After you make the change, you will need to remap the data fields according to the new sheet.
    Proceed with caution when performing this action as changing the spreadsheet holding the viewer data will affect all the videos that are using this data library.

    Change_Google_spreadsheet.png 

At the end of Step 3:
The Google Sheets data connector is attached to the data library.

Google_sheets_connector.png


Step 4: Integrate Google Sheets with SundaySky

bell-on.png If you have already integrated Google Sheets with your SundaySky account, skip to Step 5.

Before you begin the integration process, following are the key points that you should be aware of:

1. A single SundaySky account can be integrated with only one Google account.
2. The Google Sheets integration setup needs to be performed only one time. Once the integration has been configured, you can use Google Sheets as a data source with any data library in your account.
3. If you need to reauthenticate or remove the Google Sheets connector for any reason, see Data Connector Management to learn how.
4.

The integration should be configured with a dedicated user account for integration purposes and not a user tied to an individual employee. This will ensure continuity if the employee leaves the company.


To integrate Google Sheets with SundaySky:

1.

Select the Google Sheets data connector and then click Select at the bottom of the window.

 

Google_Sheets_Marketplace_120.png

2.

Click Begin Authentication.

  Begin_Authentication.png

3. Select the Google account that you want to use for the integration.

  Select_account.png

4.

Click Continue.

 

Click_continue.png

5.

Enter the URL of the Google spreadsheet that holds the viewer data and then click anywhere outside of the field.
▶ If required, this URL can be changed in the future.

 

Enter_URL.png

 

The Sheet drop-down list is populated with all the sheets in the spreadsheet.

 

Sheet_drop_down.png

6.

Select the relevant sheet and then click Select at the bottom of the window.

 

select_sheet.png

 

The integration is completed and the following success message is displayed.

 

Connection_done.png

7.

Click Map Fields to continue directly to field mapping.
▶ If you prefer mapping at a later time, click I'll Do It Later to return to the data library.

 

Connection_successful.png

At the end of Step 4:
The Google Sheets data connector is connected to your SundaySky account.

Google_sheets_connected.png


Step 5: Map the Data Fields

bell-on.png If you selected an existing data library and have verified that the mapping is correct, skip to Step 6.

The mapping process specifies which column header in your spreadsheet corresponds to the data field in the SundaySky data library. A precise mapping ensures that the right data is pulled from the spreadsheet to populate a personalized video.
For additional guidelines, refer to the notes for Step 5 below.

1.

Open the relevant video in the Studio.

2.

Select Data in the sidebar.

3.

Click Map Fields in the data connector section.

 

mapping_fields.png

 

In the mapping window, the data fields created for the data library are displayed on the right side. For each of these data fields, you'll need to select the column header in the spreadsheet to which you want to map this field.

 

mapping_window.png

4.

For each data field, open the Google Sheets Column Header drop-down list and select the column header to which it should be mapped.

 

First_name_mapped.png

5.

After all the fields are mapped, click Save at the bottom of the window.


Notes for Step 5:

  • The Google Sheets Column Header drop-down list includes the id column header. This is a mandatory column header in the spreadsheet. This column header is not used for mapping purposes as no corresponding data field exists.

    id_field_80.png

  • If you rename a data field after the mapping process, the column header name is not changed. This name change will not impact the existing mapping.

  • If you add a data field after the initial mapping process, you'll need to map it accordingly.

At the end of Step 5:
The column headers in the spreadsheet are mapped correctly to the data fields.

mapping_completed.png


Step 6: Configure the Content

Now that you've set up your data library, you can configure the content in the video's scenes using the data fields.

The on-screen text, voice-over, and media can be personalized using either a personalization token or audience messaging. In addition, you can also define that a specific scene will be shown or skipped according to an audience segment.

In the example below, a personalization token is used to customize the text according to the first name of the person watching the video.

Personalized_scene.png

In the following articles, you'll find step-by-step instructions on how to configure the different types of content:

Configuring Text with a Personalization Token
Text: Message by Audience

Configuring a Voice-over with a Personalization Token
Voice-over: Message by Audience

Configuring Media According to a Data Stream
Media: Message by Audience

Showing or Skipping a Scene by Audience

At the end of Step 6:
All the relevant content in the video is configured according to the desired personalization.


Step 7: Publish the Video

After you have finished creating the video—including the configuration of all the personalized content—we recommend that you preview the video using viewer profiles to make sure that it looks exactly as you want.

When you're happy with the result, you will need to publish the video in order to lock in all the edits that you've made. Publishing is always a prerequisite to sharing your video with viewers.

You will also need to republish the video after making any changes so that your viewers can see the updated content.

To learn how to publish, see Publishing and Sharing a Video—Overview and Publishing a Video.

At the end of Step 7:
The video is published.

Published_video.png


In this last step, you're ready to generate the links for the video so that each viewer can watch a customized version. The actions in this step depend on whether the SundaySky landing page is used to play the video or not.

As mentioned previously, each video link is generated with a "sticky" ID, unique to each viewer. Because of this, it is not necessary to resend links after you update the video content or update the viewer's data. After any update, viewers only need to revisit the original link they received in order to see the updated content.

Generating Video Links for a SundaySky Landing Page

1.

Make sure that the video is published.

2.

On the Video Page, click Share and then select SundaySky Landing Page.
▶ If you have not yet created a landing page, the option will be Create SundaySky Landing Page. You will need to create a landing page before continuing.

  Ssky_lp_option.png

3. Copy and paste the landing page URL into your distribution platform.

  Ssky_lp_1.png

4. Add the unique viewer ID to the end of each URL. For example:
https://myvideo.sundaysky.com/?programId=bd0c11b7-1eed-4b21-9ee6-46f496f8b7f6&id=100

  Ssky_lp_2.png

Generating Video Links for a Non-SundaySky Landing Page

1.

Make sure that the video is published.

2.

On the Video Page, click Share and then select Embed Video.
▶ If you have not yet created the code snippet, the option name is Create Embed Video.

3. (Optional) If you have not yet created the code snippet, follow steps 3-4 in Generating the Embed Code.

4. Copy and paste the embed code into your website editor.
▶ Skip this step if the code is already embedded.

  step_1_new.png

5. Copy the URL query parameters.

  Copy_URL_parameters.png

6. Add the unique viewer ID to the end of each URL, and then concatenate the entire string to your page URL. For example:
https://video.YourWebsite.com/?programId=fb4f3197-b59d-43fd-a385-b549a190da1adr&id=109

  step_2_new.png

 

At the end of Step 8:
The personalized video links are generated, ready to be distributed by the method you prefer.

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

Still Have Questions?

SundaySky Support is here for you

contact support