Google Sheets connector

When to use this connector

Etlworks Integrator can read and write data in Google Sheets.

Read how to create data integration Flows for Google Sheets.

Create a Connection

To work with Google Sheets, you will need to create a Connection. In the Connections window, click +, and type in google sheets in the search field. Select the Google Sheets Connection from the gallery.

Authentication

To connect to a specific Google service, you will either be using the OAuth2 authentication (default) or the authentication with a Google Service Account.   

OAuth2 authentication

To connect to the Google service using OAuth2 protocol, you will need to select OAuth2 as an Authentication type and click Sign in with Google.  Read more about OAuth2 authentication with Google Services.

Authentication using Google Service account

To connect to the Google service using the service account, select the Service Account as an Authentication type, then share the spreadsheet with the service account email. The default service account email is etl-framework@api-project-201080624425.iam.gserviceaccount.com. 

To share a spreadsheet with a service account email, click SHARE, add the service account email to the list, and click Send.

Parameters for authenticating with Google Service Account

• Service Account Email: the spreadsheet must be shared with the service account email. 
• Service Account: you can create your own service account in the Google API console, authorize it in the Google Analytics Admin and add it to this Connection. If nothing is entered in this field, the default account linked to the email etl-framework@api-project-201080624425.iam.gserviceaccount.com will be used.

Connection parameters

• Spreadsheet ID or Title: a unique ID of the spreadsheet or Title. For example, if the URL used to access a spreadsheet looks like the following https://docs.google.com/spreadsheets/d/1Nqi6B1nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74AB/edit?ts=5aae75ea#gid=1051122191 the Spreadsheet ID is going to be 1Nqi6B1nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74AB.

• Find Spreadsheet ID by Title: if this parameter is enabled the connector will try to locate the worksheet with the provided Title and if it does not exist it will create the new worksheet.
• Sheet Name or 1-based index: the name of the worksheet or 1-based index of the worksheet. Read how to read from and write into different worksheets using the same Connection.
• Range: an optional range for the data, for example, A1:E10.
• Determine how input data should be interpreted: there are two available options:
  • RAW: the values the user has entered will not be parsed and will be stored as-is.
  • USER_ENTERED: the values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc., following the same rules that are applied when entering text into a cell via the Google Sheets UI.
• Service Account Email: the spreadsheet must be shared with the service account email. The default service account email is etl-framework@api-project-201080624425.iam.gserviceaccount.com.

To share a spreadsheet with a service account email, click SHARE, add the service account email to the list, and click Send.

• Service Account: you can create your own service account in the Google API console, authorize it in the Google Analytics Admin and add it to this Connection. If nothing is entered in this field, the default account linked to the email etl-framework@api-project-201080624425.iam.gserviceaccount.com will be used.
• Column names compatible with SQL: convert column names to SQL compatible column names by removing all characters, except alphanumeric characters and spaces.
• Skip empty rows: skip rows with no data.
• Treat 'null' as null: if this option is enabled, Etlworks Integrator will parse string values equal to null as actual nulls (no value).
• Date and Time Format: a Format for timestamps (date+time).
• Date Format: a Format for the date (date only, no time).
• Time Format:  a Format for time (time only, no date).
• Timeout: the request timeout in minutes. The default value is 3 minutes. If the request is taking longer than configured or default value it will be automatically aborted.
• Number of Retries: the number of retries if the HTTP call was not successful. The maximum allowed number of retries is 10. The default is 0.
• Delay between retries: delay in milliseconds between retries if the HTTP call was not successful. The maximum allowed delay is 5 minutes(300000 milliseconds). The default is 0.