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: Approve 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. SundaySky supports three methods of personalization: using a personalization token, message by audience, and show/skip scene.

• For on-screen text personalization, you only need to add data fields.
• For voice-over, message by audience, and show/skip scene, you need to have data fields in the data library along with their respective 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.

---

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

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

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.

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.

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.

 

---

Step 3: Select the Google Sheets Data Connector

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.
4.	Select the Google Sheets data connector and then click Select at the bottom of the window.
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.
	The Sheet drop-down list is populated with all the sheets in the spreadsheet.
7.	Select the relevant sheet and then click Select at the bottom of the window.
	The data connector is attached to the library and the following message is displayed.
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.

---

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

---

Step 4: Integrate Google Sheets with SundaySky

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.
2.	Click Begin Authentication.
3.	Select the Google account that you want to use for the integration.
4.	Click Continue.
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.
	The Sheet drop-down list is populated with all the sheets in the spreadsheet.
6.	Select the relevant sheet and then click Select at the bottom of the window.
	The integration is completed and the following success message is displayed.
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.

---

Step 5: Map the Data Fields

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

---

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.

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

---

Step 7: Approve 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 approve the video in order to lock in all the edits that you've made. Approving the video is always a prerequisite to sharing your video with viewers.

You will also need to approve the video each time you make any change so that your viewers can see the updated content.

---

Step 8: Get (Share) the Video Links

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.

Generating Video Links for a SundaySky Landing Page
Generating Video Links for a Non-SundaySky Landing Page

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.	On the Video Page, select the Share tab.
2.	Make sure that the status of the video is Approved to share.
3.	On the right side of the page, click the Share icon by the Landing Page option. ▶ If you have not yet created the landing page, a plus sign (+) is displayed.
4.	(Optional) If you have not yet created the landing page, click the plus sign and create the page.
5.	Copy and paste the landing page URL into your distribution platform.
6.	In the landing page URL, replace INSERT_VALUE with the unique identifier. For example: https://myvideo.sundaysky.com/?programId=bd0c11b7-1eed-4b21-9ee6-46f496f8b7f6&id=100

---

Generating Video Links for a Non-SundaySky Landing Page

1.	On the Video Page, select the Share tab.
2.	Make sure that the status of the video is Approved to share.
3.	On the right side of the page, click the Share icon by the Embed Video option. ▶ If you have not yet created the code snippet, a plus sign (+) is displayed.
4.	(Optional) If you have not yet created the code snippet, click the plus sign and create the snippet.
5.	Copy and paste the embed code into your website editor. ▶ Skip this step if the code is already embedded.
6.	Copy the URL query parameters.
7.	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=bd0c11b7-1eed-4b21-9ee6-46f496f8b7f6&id=109