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

Related articles

Comments

0 comments

Please sign in to leave a comment.