Google sheets component

Overview

The Google Sheets component is used to read and write to Google sheets.

Requirements

Google preparations

Before building any integration flow, you must at first configure the app from inside the Google Developers Console.

  1. To do this, go to the API & Service page and enable the following:

    • Google Drive API

    • Google Sheets API

  2. Go to the Credentials section and create a new credential of type OAuth client ID.

    • Set Application type to Web application.

    • Add Authorized redirect URI as: https://{your-tenant-address}/callback/oauth2.

In the case of new domains, you may get a This app isn’t verified error. Read the Unverified apps help article for information on how to proceed.

Environment variables

Here are the environment variables to configure for the component to connect with the Google API:

The following environment variables are required:

OAUTH_CLIENT_ID

OAuth app ID.

OAUTH_CLIENT_SECRET

Oauth app secret.

TENANT_DOMAIN

Your Google API tenant domain.

REQUEST_TIMEOUT_PERIOD

If you want to slow down requests to your API, you can set delay value (in ms), and the component will delay the next request. The time for the delay is calculated as REQUEST_TIMEOUT_PERIOD/ REQUEST_TIMEOUT_QUOTA and should not be more than 1140 seconds (19 minutes due to platform limitation). The current values of these variables can be found in Google documentation. The REQUEST_TIMEOUT_PERIOD value by default is 100000 (100 sec).

REQUEST_TIMEOUT_QUOTA

the field can be used in pair with REQUEST_TIMEOUT_PERIOD, default to 500.

If the result quota restriction is less than 1 request/min component, the Retrieve sample task will not be complete.
The component LOG_LEVEL environment variable is deprecated. Now you can control the log level per each step of the flow.

To get these, please use the Google Developers Console. As a callback, please use https://your-tenant.address/callback/oauth2.

Recommended environment variables:

EIO_REQUIRED_RAM_MB

The recommended value of allocated memory is 512MB.

Credentials

Google Sheet works with OAuth2 authentication configured at your Google Developer Console. To authenticate the component, click the Authentication button. You will be taken to Google to sign-in and give permissions to the platform to access your sheets.

Triggers

Google Sheets component includes the following triggers:

Get spreadsheet row trigger

This trigger reads the data in each row of a given Google sheet and passes it to the next stage of your integration flow.

The Rows trigger is deprecated in favor of the Get spreadsheet row trigger.

Read the Google sheets triggers documentation for more information.

Actions

Google Sheets component includes the following actions:

Create new spreadsheet

Action to create a new Google Sheet.

Add spreadsheet row

Action to create a new Google Sheet row.

The Add row action is deprecated in favor of the Add spreadsheet row action.

Read the Google Sheets actions documentation for more information.

Recommendations

Here are some general recommendations to help you avoid potentially confusing cases where you might get unexpected results while using Google Sheets connector.

Sheet country format

Depending on your Google account settings, your Google Drive and especially Google Sheets would have some specific default formatting applied to the account language or country in use.

By default, Google assumes U.S. formatting, which would mean not only the default currency is USD ($) but also, the date format will be MM/DD/YYYY, not DD/MM/YYYY which is widely used in European and other countries.

Default language settings

If the data you are planning to write has values in a different language or country formatting than that set in Google sheets, you may encounter unexpected results.

Ensure you change it to the desired one by selecting File  Settings.

Sheet structure

Do not change the sheet structure while your flow is active.

Making structural changes to the sheet while it is in use will create a number of errors and may cause the flow to cease functioning.

Decide the structure of your spreadsheet file in advance and avoid making any structural changes during the integration. In particular, avoid adding or removing additional columns since you would need to repeat the flow design process to properly map or link your changes.

If you still wish to change the structure of your sheet, then follow these steps:

  1. Stop the integration flow if it is running.

  2. Make the required changes in the sheet.

  3. Go through the integration design stage again to ensure that all columns in the modified sheet are properly linked with the required fields or values necessary to run your integration flow.

  4. Activate the flow again.

Inserting a row

Do not insert a row between the records while your flow is active.

If you insert a new row between the existing structure, the system will fail to recognize it as an update. Instead, this will cause the system to lose the connection between the unique IDs and the records since the unique ID is the row number.

If you wish to insert a row between existing records, you must first stop the integration flow in your Dashboard. Then proceed to make the changes in your sheet. You can activate your flow after you make the necessary changes. However, we recommend not to insert a row between the records even if you have deactivated it.

A newly inserted row will cause an additional data transfer.

Avoid inserting a row in between the records during the integration since it is new, it will trigger an additional data transfer. Also, not only will the newly inserted row be regarded as a new record but everything after the inserted row would be considered a new data.