Advanced Analytica Advanced Analytica: IBOM
← All connectors
Google Sheets logo

Destination connector

Google Sheets

Send governed data into Google Sheets with a clear, practical setup guide.

Version 0.3.5 community alpha

Google Sheets

The Google Sheets Destination sends data to one Google Sheets spreadsheet, using multiple Worksheets as streams. To replicate data to multiple spreadsheets, create multiple Google Sheets Destination instances in your Airbyte instance.

:::warning

Google Sheets limits request rates and the amount of data it can receive. These limits can cause syncs to fail. Only use Google Sheets as a destination for small, non-production use cases. It isn’t designed for large-scale data operations.

Read more about the limitations of using Google Sheets below.

:::

Supported sync modes

Sync modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

Prerequisites

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

Step 1: Set up Google Sheets

Google Account

Visit Google to create a Google Account.

Google Sheets (Google Spreadsheets)

  1. After signing in to your Google account, create a new Google Sheet. Select Follow this guide to create one. You can also use an existing Google Sheet.
  2. You’ll need the link for the Google Sheet you want to sync. Select “Share” in the interface’s top-right corner. Then select Copy Link in the dialog.

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

For Airbyte Cloud:

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

For Airbyte Open Source:

You can authenticate with Google Sheets using OAuth or a 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 and give it a name. Add https://developers.google.com/oauthplayground as an Authorized redirect URI.
  4. Add a Client Secret (Add secret). Note both the Client Secret and Client ID.
  5. Go to Google OAuth Playground.
  6. Select the cog in the top-right corner, then select Use your own OAuth credentials. Enter the OAuth Client ID and OAuth Client secret from the previous step.
  7. Find and select Google Sheets API v4 in the left sidebar, then choose the https://www.googleapis.com/auth/spreadsheets scope. Select Authorize APIs.
  8. In step 2, select Exchange authorization code for tokens. Note the Refresh token.
  9. Set up a new destination in Airbyte and select Google Sheets. 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. Select the email address of the service account you want to create a key for.
  4. Select the Keys tab.
  5. Select the Add key drop-down menu, then select Create new key.
  6. Select JSON as the Key type, then select Create. Selecting Create downloads a service account key file. You can’t download the key file again after downloading it.

The key should look like this:

{
  "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 becomes a separate source-connector stream in the output.

Output columns are reordered alphabetically. You should not reorder them manually after syncing because this could cause future syncs to fail.

Google Sheets coerces all data into a string format. Nested lists and objects are formatted as strings instead of standard lists and objects. You’ll need further processing for downstream analysis.

Airbyte only replicates Grid Sheets, so only text is replicated. Objects such as charts or images can’t be synced. See the Google Sheets API docs for more information about all available sheet types.

Rate limits and performance

The Google API rate limit is 60 requests per 60 seconds per user and 300 requests per 60 seconds per project. These limits result in slow sync speeds. Airbyte batches API requests to pull data efficiently while respecting these limits.

Limitations {#limitations}

Google Sheets limits the amount of data you can sync. Exceeding these limits may cause the sync to fail. In some cases, data is truncated to meet the limits.

Maximum of 10 Million Cells

A Google Sheets document can contain up to 10 million cells across one or multiple worksheets. After reaching this limit, you can’t add more columns. You also can’t add rows if the existing rows reach the limit.

Maximum of 50,000 characters per cell

A single cell can contain up to 50,000 characters. Airbyte automatically truncates longer values and appends ...[TRUNCATED]. Don’t use Google Sheets if your source contains fields with long text.

Maximum of 18,278 Columns

A Google Sheets worksheet can contain up to 18,278 columns.

Maximum of 200 Worksheets in a Spreadsheet

A single spreadsheet can contain up to 200 worksheets.

Note

  • Record normalisation prevents data corruption during the write process. It handles two scenarios:
  1. UnderSetting: the record has fewer keys (columns) than the catalogue declares
  2. OverSetting: the record has more keys (columns) than the catalogue 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

Integration TypeAirbyte Type
Any Typestring

Namespace support

This destination doesn’t support namespaces.

Changelog

Expand to review
VersionDatePull RequestSubject
0.3.52025-04-3059647Truncate cell values exceeding 50,000 characters with warning
0.3.42025-04-2658280Update dependencies
0.3.32025-04-1257636Update dependencies
0.3.22025-04-0557166Update dependencies
0.3.12025-03-2956600Update dependencies
0.3.02025-02-2754689Support service account authentication
0.2.422025-03-2256149Update dependencies
0.2.412025-03-0855374Update dependencies
0.2.402025-03-0154847Update dependencies
0.2.392025-02-2254248Update dependencies
0.2.382025-02-1553892Update dependencies
0.2.372025-02-0152194Update dependencies
0.2.362025-01-1851748Update dependencies
0.2.352025-01-1151262Update dependencies
0.2.342025-01-0450912Update dependencies
0.2.332024-12-2850492Update dependencies
0.2.322024-12-2150168Update dependencies
0.2.312024-12-1448915Update dependencies
0.2.302024-11-2548678Update dependencies
0.2.292024-11-0448281Update dependencies
0.2.282024-10-2847042Update dependencies
0.2.272024-10-1246772Update dependencies
0.2.262024-10-0546464Update dependencies
0.2.252024-09-2846204Update dependencies
0.2.242024-09-2145772Update dependencies
0.2.232024-09-1445577Update dependencies
0.2.222024-09-0745325Update dependencies
0.2.212024-08-3144989Update dependencies
0.2.202024-08-2444736Update dependencies
0.2.192024-08-2244530Update test dependencies
0.2.182024-08-1744259Update dependencies
0.2.172024-08-1043603Update dependencies
0.2.162024-08-0343066Update dependencies
0.2.152024-07-2742819Update dependencies
0.2.142024-07-2042368Update dependencies
0.2.132024-07-1341721Update dependencies
0.2.122024-07-1041520Update dependencies
0.2.112024-07-0941076Update dependencies
0.2.102024-07-0640999Update dependencies
0.2.92024-06-2640529Update dependencies
0.2.82024-06-2540353Update dependencies
0.2.72024-06-2240172Update dependencies
0.2.62024-06-0439011[autopull] Upgrade base image to v1.2.1
0.2.52024-05-2238516[autopull] base image + poetry + up_to_date
0.2.42024-05-2138516[autopull] base image + poetry + up_to_date
0.2.32023-09-2530748Performance testing - include socat binary in docker image
0.2.22023-07-0628035Migrate from authSpecification to advancedAuth
0.2.12023-06-2627782Only allow HTTPS urls
0.2.02023-06-2627780License Update: Elv2
0.1.22022-10-3118729Fix empty headers list
0.1.12022-06-1514751Yield state only when records saved
0.1.02022-04-2612135Initial Release
Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.