Google Sheets

Google Sheets, a free offering from Google, is a spreadsheet program that allows users to create, update, and modify spreadsheets. Users can collaborate in real-time and share data, create reporting dashboards, and so on to track business activities.

You can configure Google Sheets as a Target in Activate to synchronize data from your data warehouse and automatically update the relevant Google worksheets and spreadsheets, thereby saving time and effort.

---

Prerequisites

• An active Google user account or service account. Read Creating a Google service account to know how to set up a service account if you do not already have one.

• The Google Sheets and Google Drive APIs are enabled for the service account if connecting via it.

• The essential scopes are granted to Activate to access your Google account.

---

Scopes

Activate uses OAuth to access your Google Drive’s data when connecting with a user account. OAuth requires Activate to have the following permissions for accessing the different Google Sheets API endpoints:

Scope	Description
https://www.googleapis.com/auth/drive.readonly	Provides Hevo read-only access to your files’ metadata and content. Hevo uses this scope to list the folders available to the connected user.
https://www.googleapis.com/auth/drive.file	Provides Hevo the access to view and manage the Google Drive files and folders that you viewed or created. Hevo uses this scope to create new spreadsheets in the folders that you specify.
https://www.googleapis.com/auth/spreadsheets	Provides Hevo the access to view, edit, create and delete your spreadsheets in Google Drive. Hevo uses this scope to read and inform the user of any errors found in the worksheet.

Activate requests for these scopes when you configure Google Sheets as a Target with a user account. Read OAuth Scopes. For service accounts, you need to enable the Google Sheets and Google Drive APIs. Read Enabling API access for a Google service account.

---

Configuring Google Sheets as a Target

Perform the following steps to configure Google Sheets as the Target in your Activation:

1. Click Activate in the Navigation Bar.

2. Do one of the following:

   • Select the TARGETS tab in the Targets List View, and click + CREATE TARGET.

     

   • Select the ACTIVATIONS tab in the Activations List View, and click + CREATE ACTIVATION.

     

     1. In the Select Warehouse page, select your Activate Warehouse or click + ADD WAREHOUSE to add a new warehouse. Read Activate Warehouses to configure the selected Warehouse type.

     2. In the Select a Target page, click + ADD TARGET.

        

3. In the Select a Target Type page, click on Google Sheets.

   

4. In the Configure your Google Sheets account page, select the type of account you want to use to connect to Google Sheets.

   

5. Do one of the following:

   • Connect with a user account:

     1. Click + ADD GOOGLE SHEETS ACCOUNT.

     2. Select the Google account associated with your Google Sheets data, and click Allow to authorize Hevo to access your data.

        

   • Connect with a service account:

     

     1. Attach the Service Account Key. Read Downloading the key file for the steps to create a new key.

        Note: Hevo supports only JSON format for the key file.

     2. Click CONFIGURE GOOGLE SHEETS ACCOUNT.

6. In the Configure your Google Sheets Target page, specify the following:

   

   • Target Name: A unique name for your Target.

   • (Optional) Folder: Your Google Drive folder. You can select an existing folder from the drop-down or create one now. The new folder is created in your My Drive folder.

     Note: If you have connected with a user account, all the folders created by or shared with you are listed. If you have connected with a service account, only the folders shared with the service account are visible in the drop-down.

   • Spreadsheet: The Google spreadsheet with which you want to synchronize the data. You can select an existing spreadsheet from the drop-down or create one now. If you had specified a folder earlier, the spreadsheet is created in that folder, else, in your My Drive folder.

     Note: If you have connected with a user account, all the spreadsheets created by or shared with you are displayed.

     If you have connected with a service account, only the spreadsheets shared with the service account are visible in the drop-down. To create a spreadsheet, the folder in which you want to create it must be shared with the service account.

7. Click TEST & CONTINUE.

   You can view the new Target in the Targets List View. If you are creating an Activation, you return to the Select Data to Synchronize page. Refer to section, Field Mapping in Google Sheets to know how to map the Warehouse fields to your selected Google spreadsheet.

   

---

Field Mapping in Google Sheets

Once you provide the SQL query to synchronize data with the Google Sheets Target, you need to map the query fields in the Field Mapping page to create the Activation.

To do this:

1. In the Field Mapping page, specify the following:

   

   • In the left pane:

     • Worksheet: Select an existing worksheet or create one. The new worksheet is created in the spreadsheet specified while creating the Target. Default value: Sheet1.

     • Select the Sync Behavior: Select an option to define how data must be synchronized:

       • Full Sync: Synchronizes all the data received from the Warehouse in each Activation run.

       • Append Only: Appends new records after the last row that contains data in each Activation run.

     • Select the Data Type: Select an option to define how the data received from the Warehouse should be interpreted:

       • Raw: The data received is not parsed and is stored as strings in the worksheet. For example, “=123” is stored in the cell as the string value, ‘=123.

       • User-entered: The data received is parsed by Google Sheets as per its data entry formats and rules. For example, “=123” is parsed as a formula and is stored in the cell as the numeric value, 123.

     • (Optional) Custom Range: Select the check box to specify the range of cells in the worksheet where the data must be written.

       Note: If enabled, Hevo writes data from the first cell of the specified range, else from cell A1. For example, if the specified custom range is B20:J45, Hevo writes data from B20.

       Provide the following information:

       • Start: Specify the starting column and row number of the range. For example, B20.

         Note: You cannot change the Start value once the Activation is created.

       • (Optional) End: Specify the ending column name and row number of the range. For example, J45. Leave this field blank to specify an unlimited number of rows. The number of cells in the range cannot exceed 5 Million. Read Target Considerations.

         Note: You can modify the End value after the Activation is created.

   • In the Field Mapping pane:

     • Query Fields: Select the check box next to the fields to be mapped. All unselected fields are skipped.

     • Column Mapping: Select a column from the drop-down to which you want to map the corresponding query field. If a custom range is specified, then the first query field is mapped to the starting column name.

     • Target Column Name: The names of the respective query fields are displayed here. You can change these names.

2. Specify a unique Activation Name.

3. Click CREATE ACTIVATION.

Modifying an Activation in Google Sheets

After an Activation is created, you can modify the Activation SQL query as well as some of the existing settings in the Field Mapping page. To modify the Activation SQL query, select the QUERY sub-tab in the Overview tab. Read Modifying the Activation Query. You can change the existing field mappings by selecting the MAPPING sub-tab in the Overview tab.

The following settings can be modified for a created Activation:

• In the left pane:

  • Custom Range: Select or unselect the check box to enable or disable this option, respectively.

    Note: Hevo does not recommend enabling or disabling the custom range after you have created the Activation, as this may lead to data inconsistency.

    • Enable the Custom Range: The Start value automatically defaults to A1. You can specify an End value or leave this field blank.

    • Disable the Custom Range: The Start value is set to the starting value of the range that was specified earlier. You can specify an End value or leave this field blank.

      Note: If the specified custom range has no rows to write new data, the records are FAILED and an error is displayed.

• In the Field Mapping pane:

  • Query Fields: Select the check box next to the field name(s) to add or skip the existing fields.

  • Column Mapping: Select one of the enabled column name(s) from the drop-down. Column names are available for selection only if you added a previously skipped field. Read Updating Field Mapping.

  • Target Column Name: You can change the Target column names only if you had selected the sync behavior as Full Sync.

For the changes to take effect, click UPDATE EXISTING MAPPING.

---

Activate Behavior

• Values specified in the Target Column Name form the headers in the selected worksheet. The headers are written in the first row of the selected worksheet, and the first row of the range if a range is specified.

• If the custom range provided is not sufficient, the first cell in the last row of the range is colored red, and a note is made on it. The note has the date, the time (in UTC) when the situation occurred, and the actions that you need to take.

  For example, suppose the specified custom range is B5:M10 (six rows). Now, if the Activation SQL query returned 20 rows, then the first cell of the range in the 10th row, B10 will be marked as shown in the following image:

  

• The custom range must contain at least as many columns as the number of query fields. Else, an error is displayed. If you want to map fewer fields, you need to modify your Activation SQL query.

  For example, suppose there are 16 query fields to be mapped, and you skipped four of these fields. The specified custom range must still contain at least 16 columns.

• In the Append Only sync behavior, if you are using a custom range, ensure that the specified range does not contain any data. Also, in this behavior, data is appended after the last row that contains data. Therefore, you must not modify the worksheet being used in an Activation, as it may lead to data inconsistency.

  For example, suppose in the previous Activation run, data was written till the 20th row. Now, if some cell of the 32nd row contains data, then in the next Activation run, data is written from the 33rd row.

• Activate only supports writing to spreadsheets in Google Sheets format.

• In an Activation created with the data type, User-entered, any errors resulting from operations performed on the data written to the worksheet are displayed in the worksheet.

  For example, suppose the data written in a cell is + 91 98897. As the data contains a ‘+’ character, Google Sheets attempts to perform addition. However, it is unable to interpret the formula, and it writes an error to the cell as shown in the following image:

  

---

Data Replication

Activate supports the following synchronization behaviors for Google Sheets:

• Full Sync

• Append Only

Activate synchronizes data with the spreadsheets using Google Sheets batch API endpoints. No identifiers are used, and all the data received from Warehouse, depending on the sync behavior is written or appended to the selected worksheet. Read Synchronization Behaviors.

In either of the sync behaviors, you can specify a custom range in which data should be written. The first row of the range or the worksheet always contains the headers, which are the Target column names specified during Activation creation.

If a range is not specified, then in:

• Full Sync: Data is written from the first cell of the worksheet. The entire data is refreshed in every Activation run.

• Append Only: In the first Activation run, data is written from the first cell of the worksheet. Thereafter, in subsequent runs, data is appended in the sheet after the last row that contains data.

---

Data Model

There is no data model in Google Sheets. Activate writes data to the selected worksheet based on the Activation SQL query results. The selected query fields, their corresponding Target column names, and their respective column mappings form the schema in which the data is written in the selected worksheet.

---

Target Considerations

• There are usage limits imposed by Google Sheets API on the connecting applications to prevent API traffic spikes. Since Activate uses Google Sheets’ public APIs for synchronizing data, it is subject to the rate limits imposed by Google. Read Usage Limits.

• Each Google spreadsheet, including all its worksheets, whether created in or converted to Google Sheets, can store up to 5 Million cells or 18,278 columns (column ZZZ). Any single cell can only contain up to 50,000 characters. For example, if the data to be written in cell A32 exceeds 50,000 characters then, A32 is left blank. Read Files you can store in Google Drive.

• Google Sheets APIs perform almost all operations, including writing data to a range of cells within a worksheet by referencing the unique worksheet name. For example, Sheet1!B2:Z3 is the range of cells from B2 to Z3 in Sheet1.

  Therefore, you must not rename a worksheet in use by an Activation, as renaming causes the Activation to fail.

---

Revision History