Google Sheets

The Google Sheets Destination is configured to push data to a single Google Sheets spreadsheet with multiple Worksheets as streams. To replicate data to multiple spreadsheets, you can create multiple instances of the Google Sheets Destination in your Airbyte instance.

:::warning

Google Sheets imposes rate limits and hard limits on the amount of data it can receive, which results in sync failure. Only use Google Sheets as a destination for small, non-production use cases, as it is not designed for handling large-scale data operations.

Read more about the limitations of using Google Sheets below.

:::

Supported sync modes

Prerequisites

• Google Account or GCP Service Account for authentication
• Google Spreadsheet URL

Step 1: Set up Google Sheets

Google Account

To create a Google account, visit Google and create a Google Account.

Google Sheets (Google Spreadsheets)

1. Once you are logged into your Google account, create a new Google Sheet. Follow this guide to create a new sheet. You may use an existing Google Sheet.
2. You will need the link of the Google Sheet you'd like to sync. To get it, click "Share" in the top right corner of the Google Sheets interface, and then click Copy Link in the dialog that pops up.

Step 2: Set up the Google Sheets destination connector in Airbyte

For Airbyte Cloud:

1. Select Google Sheets from the Source type dropdown and enter a name for this connector.
2. Select Sign in with Google.
3. Log in and Authorize to the Google account and click Set up source.
4. Copy the Google Sheet link to Spreadsheet Link

For Airbyte Open Source:

Authentication to Google Sheets is available using OAuth or GCP Service Account token.

Using OAuth

1. Create a new Google Cloud project.
2. Enable the Google Sheets API.
3. Create a new OAuth client ID. Select Web application as the Application type, give it a name and add https://developers.google.com/oauthplayground as an Authorized redirect URI.
4. Add a Client Secret (Add secret), and take note of both the Client Secret and Client ID.
5. Go to Google OAuth Playground
6. Click the cog in the top-right corner, select Use your own OAuth credentials and enter the OAuth Client ID and OAuth Client secret from the previous step.
7. In the left sidebar, find and select Google Sheets API v4, then choose the https://www.googleapis.com/auth/spreadsheets scope. Click Authorize APIs.
8. In step 2, click Exchange authorization code for tokens. Take note of the Refresh token.
9. Set up a new destination in Airbyte, select Google Sheets and enter the Client ID, Client Secret, Refresh Token and Spreadsheet Link from the previous steps.

Using GCP Service Account:

1. In the Google Cloud console, go to the Service accounts page.
2. Select a project.
3. Click the email address of the service account that you want to create a key for.
4. Click the Keys tab.
5. Click the Add key drop-down menu, then select Create new key.
6. Select JSON as the Key type and click Create. Clicking Create downloads a service account key file. After you download the key file, you cannot download it again.

The key should look like:

{
  "type": "service_account",
  "project_id": "PROJECT_ID",
  "private_key_id": "KEY_ID",
  "private_key": "-----BEGIN PRIVATE KEY-----\nPRIVATE_KEY\n-----END PRIVATE KEY-----\n",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "CLIENT_ID",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL"
}

Output schema

Each worksheet in the selected spreadsheet will be the output as a separate source-connector stream.

The output columns are re-ordered in alphabetical order. The output columns should not be reordered manually after the sync, as this could cause future syncs to fail.

All data is coerced to a string format in Google Sheets. Any nested lists and objects will be formatted as a string rather than normal lists and objects. Further data processing is required if you require the data for downstream analysis.

Airbyte only supports replicating Grid Sheets, which means only text is replicated. Objects like charts or images cannot be synced. See the Google Sheets API docs for more info on all available sheet types.

Rate Limiting & Performance Considerations

The Google API rate limit is 60 requests per 60 seconds per user and 300 requests per 60 seconds per project, which will result in slow sync speeds. Airbyte batches requests to the API in order to efficiently pull data and respects these rate limits.

Limitations {#limitations}

Google Sheets imposes hard limits on the amount of data that can be synced. If you attempt to sync more data than is allowed, the sync may fail or, in some cases, data will be truncated to comply with limits.

Maximum of 10 Million Cells

A Google Sheets document can contain a maximum of 10 million cells. These can be in a single worksheet or in multiple sheets. If you already have reached the 10 million limit, it will not allow you to add more columns (and vice versa, i.e., if the 10 million cells limit is reached with a certain number of rows, it will not allow more rows).

Maximum of 50,000 characters per cell

There can be at most 50,000 characters in a single cell. Airbyte will automatically truncate any value exceeding this limit, appending ...[TRUNCATED] to the end of the value. Do not use Google Sheets if you have fields with long text in your source.

Maximum of 18,278 Columns

There can be at most 18,278 columns in Google Sheets in a worksheet.

Maximum of 200 Worksheets in a Spreadsheet

You cannot create more than 200 worksheets within single spreadsheet.

Note:

• The underlying process of record normalization is applied to avoid data corruption during the write process. This handles two scenarios:

1. UnderSetting - when record has less keys (columns) than catalog declares
2. OverSetting - when record has more keys (columns) than catalog declares

EXAMPLE:

- UnderSetting:
    * Catalog:
        - has 3 entities:
            [ 'id', 'key1', 'key2' ]
                        ^
    * Input record:
        - missing 1 entity, compare to catalog
            { 'id': 123,    'key2': 'value' }
                            ^
    * Result:
        - 'key1' has been added to the record, because it was declared in catalog, to keep the data structure.
            {'id': 123, 'key1': '', {'key2': 'value'} }
                            ^
- OverSetting:
    * Catalog:
        - has 3 entities:
            [ 'id', 'key1', 'key2',   ]
                                    ^
    * Input record:
        - doesn't have entity 'key1'
        - has 1 more enitity, compare to catalog 'key3'
            { 'id': 123,     ,'key2': 'value', 'key3': 'value' }
                            ^                      ^
    * Result:
        - 'key1' was added, because it expected be the part of the record, to keep the data structure
        - 'key3' was dropped, because it was not declared in catalog, to keep the data structure
            { 'id': 123, 'key1': '', 'key2': 'value',   }
                            ^                          ^

Data type mapping

Namespace support

This destination does not support namespaces.

Changelog