Articles in this section

See more

Google Sheets connector

  • Updated
Follow

Use this connector to read from and write to Google Sheets as part of your ETL workflows in Etlworks. It supports flexible configuration, authentication via OAuth2 or service accounts, and automatic spreadsheet creation when needed.

When to Use

Use the Google Sheets connector when you need to:

  • Extract structured data from Google Sheets and load it into databases, APIs, or files.

  • Load data from external systems into new or existing spreadsheets.

  • Work with one or multiple worksheets in the same flow.

  • Automatically create a spreadsheet if it doesn’t exist.

For a complete walkthrough of working with Google Sheets in Etlworks, refer to the Getting Started with Google Sheets guide.

Step 1: Create a Google Sheets Connection

1. Go to Connections.

2. Click + to add a new connection.

3. In the search field, type google sheets and select Google Sheets from the gallery.

Step 2: Authenticate

Etlworks supports both OAuth2 and Google Service Account authentication methods.

OAuth2 (Recommended for interactive use)

  • In the connection settings, select OAuth2 as the Authentication Type.

  • Click Sign in with Google and authorize access.

Learn more about Google OAuth2 authentication

Google Service Account (Recommended for automation)

  • Select Service Account as the Authentication Type.

  • Enter your Service Account Email, or use the default:

etl-framework@api-project-201080624425.iam.gserviceaccount.com

  • Share the spreadsheet with the service account:

    1. Open the spreadsheet.

    2. Click SHARE.

    3. Add the service account email.

    4. Click Send.

You can also use your own service account created in the Google API Console. If not specified, the default will be used.

Learn more about Google Service Account authentication

Step 3: Configure Connection Parameters

  • Spreadsheet ID or Title: Either the spreadsheet’s ID (from the URL) or its title.

  • Find Spreadsheet ID by Title: If enabled, the connector will try to locate the spreadsheet using the title. If it doesn’t exist, it will create a new one.

  • Sheet Name or 1-based Index: Specify the worksheet by name (e.g., Sheet1) or by its index (e.g., 1).

  • Range (optional): Restrict the range of cells to read/write, e.g., A1:E10.

  • Determine how input data should be interpreted:

    • RAW: Values are stored exactly as entered.

    • USER_ENTERED: Values are interpreted like they would be in the Google Sheets UI (dates, numbers, etc.).

  • Column Names Compatible with SQL: Removes unsupported characters to make column names safe for SQL.

  • Skip Empty Rows: Skips rows with no data.

  • Treat ‘null’ as null: Converts the string 'null' into an actual null value.

  • Date and Time Format: Format for full timestamps (e.g., yyyy-MM-dd HH:mm:ss).

  • Date Format: Format for date-only values.

  • Time Format: Format for time-only values.

  • Timeout: Request timeout in minutes (default is 3).

  • Number of Retries: Number of retry attempts if the request fails (default is 0, max is 10).

  • Delay Between Retries: Wait time between retries in milliseconds (default is 0, max is 300000).

Related Articles

Read from and write into different worksheets using the same Google Sheets connection

Overview

In Etlworks, the name or index of the Google Sheets worksheet is configured in the Google Sheets Connection.

Specify worksheet name or index in the FROM or TO

If you want to use the same Connection to read from or write into different Google Sheets worksheets,  use the Spreadsheet ID|worksheet notation in the FROM or TO when creating the source-to-destination transformation.

For example: 1Ni3C3nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74ab|income tells the system to read data from the worksheet income in the spreadsheet with the ID 1Ni3C3nO6Qtuc6wGXVd3WLKIdApXQ4B-S6r7RAK74ab. 

mceclip0.png

Note: You can use the worksheet index instead of the name. Index starts from 1.

Select data from the specific worksheet using Source query

The alternative to using the worksheet name or index in the FROM is to select data from the specific worksheet using the Source query. 

For example: select * from [income], where income is a worksheet name. Always enclose the worksheet name or index in [].

Note: While it does look intuitive to use the SQL, it automatically disables the data streaming, so all the data in the worksheet will be stored in memory. Large worksheets could cause the Out of Memory error so we recommend specifying the worksheet name or index in the FROM.

mceclip1.png

Select data from the specific worksheet in the Etlworks Explorer

Use select fields from [worksheet_name_or_index] optional_where_clause to view the data in the specific worksheet when browsing data in the Etlworks Explorer.

For example: select * from [invoices], where invoices is a worksheet name. 

Rules:

  • Always enclose the worksheet name or index in []. 
  • You can use the worksheet index instead of the name. Index starts from 1.

mceclip0.png

Related articles