Skip to main content

Google Sheets

Availability
Available CloudAvailable Self-Managed CommunityAvailable Self-Managed EnterpriseAvailable PyAirbyte
Support Level
Airbyte
Connector Version
0.11.0 (last updated a day ago)
CDK Version
6.36.4
Sync Success Rate
Usage Rate
Definition ID
71607ba1-c0ac-4799-8049-7f4b90dd50f7

This page contains the setup guide and reference information for the Google Sheets source connector.

info

The Google Sheets source connector pulls data from a single Google Sheets spreadsheet. Each sheet within a spreadsheet can be synced. To sync multiple spreadsheets, use our Google Drive connector or set up multiple Google Sheets source connectors in your Airbyte instance. No other files in your Google Drive are accessed.

Prerequisites​

  • Spreadsheet Link - The link to the Google spreadsheet you want to sync.
  • For Airbyte Cloud A Google Workspace user with access to the spreadsheet
  • For Airbyte Open Source:
  • A GCP project
  • Enable the Google Sheets API in your GCP project
  • Service Account Key with access to the Spreadsheet you want to replicate

Setup guide​

The Google Sheets source connector supports authentication via either OAuth or Service Account Key Authentication.

Step 1: Set up Google Sheets​

For Airbyte Cloud:

We highly recommend using OAuth, as it significantly simplifies the setup process and allows you to authenticate directly from the Airbyte UI.

For Airbyte Open Source:

We recommend using Service Account Key Authentication. Follow the steps below to create a service account, generate a key, and enable the Google Sheets API.

note

If you prefer to use OAuth for authentication with Airbyte Open Source, you can follow Google's OAuth instructions to create an authentication app. Be sure to set the scopes to https://www.googleapis.com/auth/spreadsheets.readonly. You will need to obtain your client ID, client secret, and refresh token for the connector setup.

Set up the service account key​

Create a service account​

  1. Open the Service Accounts page in your Google Cloud console.
  2. Select an existing project, or create a new project.
  3. At the top of the page, click + Create service account.
  4. Enter a name and description for the service account, then click Create and Continue.
  5. Under Service account permissions, select the roles to grant to the service account, then click Continue. We recommend the Viewer role.

Generate a key​

  1. Go to the API Console/Credentials page and click on the email address of the service account you just created.
  2. In the Keys tab, click + Add key, then click Create new key.
  3. Select JSON as the Key type. This will generate and download the JSON key file that you'll use for authentication. Click Continue.

Enable the Google Sheets API​

  1. Go to the API Console/Library page.
  2. Make sure you have selected the correct project from the top.
  3. Find and select the Google Sheets API.
  4. Click ENABLE.

If your spreadsheet is viewable by anyone with its link, no further action is needed. If not, give your Service account access to your spreadsheet.

Set up the Google Sheets connector in Airbyte​

For Airbyte Cloud:​

  1. Log into your Airbyte Cloud account.
  2. Click Sources and then click + New source.
  3. On the Set up the source page, select Google Sheets from the Source type dropdown.
  4. Enter a name for the Google Sheets connector.

For Airbyte Open Source:​

  1. Navigate to the Airbyte Open Source dashboard.
  2. Click Sources and then click + New source.
  3. On the Set up the source page, select Google Sheets from the Source type dropdown.
  4. Enter a name for the Google Sheets connector.
  1. Select your authentication method:
  • For Airbyte Cloud: (Recommended) Select Authenticate via Google (OAuth) from the Authentication dropdown, click Sign in with Google and complete the authentication workflow.
  • For Airbyte Open Source: (Recommended) Select Service Account Key Authentication from the dropdown and enter your Google Cloud service account key in JSON format:
  {
    "type": "service_account",
    "project_id": "YOUR_PROJECT_ID",
    "private_key_id": "YOUR_PRIVATE_KEY",
    ...
  }
  • To authenticate your Google account via OAuth, select Authenticate via Google (OAuth) from the dropdown and enter your Google application's client ID, client secret, and refresh token.
  1. For Spreadsheet Link, enter the link to the Google spreadsheet. To get the link, go to the Google spreadsheet you want to sync, click Share in the top right corner, and click Copy Link.
  1. For Batch Size, enter an integer which represents batch size when processing a Google Sheet. Default value is 1000000. Batch size is an integer representing row batch size for each sent request to Google Sheets API. Row batch size means how many rows are processed from the google sheet, for example default value 1000000 would process rows 2-1000002, then 1000003-2000003 and so on. Based on Google Sheets API limits documentation, it is possible to send up to 300 requests per minute, but each individual request has to be processed under 180 seconds, otherwise the request returns a timeout error. In regards to this information, consider network speed and number of columns of the google sheet when deciding a batch_size value.
  1. (Optional) You may enable the option to Convert Column Names to SQL-Compliant Format. Enabling this option will allow the connector to convert column names to a standardized, SQL-friendly format. For example, a column name of Café Earnings 2022 will be converted to cafe_earnings_2022. We recommend enabling this option if your target destination is SQL-based (ie Postgres, MySQL). Set to false by default.
  2. Click Set up source and wait for the tests to complete.

Configuration Options​

Stream Name Overrides (Rename Sheet/Stream Names)​

The Google Sheets connector allows you to optionally rename streams (sheet/tab names) as they appear in Airbyte and your destination. This is useful if your sheet names are not descriptive, contain special characters, or you want to standardize naming across sources.

How it works​

  • You can provide a list of overrides, each specifying a source_stream_name (the exact name of the sheet/tab in your spreadsheet) and a custom_stream_name (the name you want it to appear as in Airbyte and your destination).
  • If a source_stream_name is not found in your spreadsheet, it will be ignored and the default name will be used.
  • This feature only affects stream (sheet/tab) names, not field/column names.
  • If you want to rename fields or column names, you can do so using the Airbyte Mappings feature after your connection is created. See the Airbyte documentation for more details on how to use Mappings.
  • Renaming occurs before any other name conversion or sanitization options.

Example​

Suppose your spreadsheet has sheets named Sheet1, 2024 Q1, and Summary. You want to rename them to sales_data, q1_2024, and leave Summary unchanged. You would configure:

[
  { "source_stream_name": "Sheet1", "custom_stream_name": "sales_data" },
  { "source_stream_name": "2024 Q1", "custom_stream_name": "q1_2024" }
]

After discovery, your streams in Airbyte will be named sales_data, q1_2024, and Summary.

How to configure​

  • In the Airbyte UI, add your overrides in the Stream Name Overrides field as an array of objects.
  • If you do not wish to rename any streams, leave this field blank.
  • After adding or changing a stream name override, refresh your schema in Airbyte to see the new stream names take effect.
  • Overridden streams will default to Sync Mode: Full Refresh (Append), which does not support primary keys. If you want to use primary keys and deduplication, update the sync mode to "Full Refresh | Overwrite + Deduped" in your connection settings.

Google Sheets Connector Column Name Conversion​

The Google Sheets connector offers options to customize how column names from your spreadsheet are converted to be SQL-compliant. These settings can be configured in the Airbyte UI when setting up the connector.

1. Convert Column Names to SQL-Compliant Format​

  • Description: When enabled, this converts column names to a format compatible with SQL databases (e.g., lowercasing, replacing spaces with underscores). This is the primary toggle required to enable any column name conversion.
  • Default: Off

2. Additional Conversion Options​

The following options allow you to fine-tune the column name conversion process. They only take effect if "Convert Column Names to SQL-Compliant Format" is enabled.

  • Remove Leading and Trailing Underscores

    • Description: Removes leading and trailing underscores from column names. Note that leading underscores are preserved for column names starting with a number if "Allow Leading Numbers" is disabled.
    • Example:
      • Input: " EXAMPLE Domain "
      • Output: "example_domain"
    • Default: Off

  • Combine Number-Word Pairs

    • Description: Combines adjacent numbers and words into a single token without separators.
    • Example:
      • Input: "50th Percentile"
      • Output: "50th_percentile" (if "Allow Leading Numbers" is enabled)
      • Output: "_50th_percentile" (if "Allow Leading Numbers" is disabled)
    • Default: Off

  • Remove All Special Characters

    • Description: Removes all special characters (e.g., *, ?, !, $, %, (, )) from column names.
    • Example:
      • Input: "Example ID*"
      • Output: "example_id"
    • Default: Off

  • Combine Letter-Number Pairs

    • Description: Combines adjacent letters and numbers into a single token without separators.
    • Example:
      • Input: "Q3 2023"
      • Output: "q3_2023"
    • Default: Off

  • Allow Leading Numbers

    • Description: Allows column names to start with numbers. If disabled, a leading underscore is added to column names that begin with a number.
    • Example:
      • Input: "50th Percentile"
      • Output: "50_th_percentile" (if enabled)
      • Output: "_50_th_percentile" (if disabled)
    • Default: Off

Additional Details​

  • All converted column names are lowercased.
  • Multiple spaces or special characters are collapsed or removed, not replaced with multiple underscores.
  • Only single underscores are used to separate tokens.
  • The result is always SQL-friendly and readable.

These options provide flexibility to tailor column name conversions to your specific database requirements. Adjust them as needed in the Airbyte UI when configuring the Google Sheets connector.

Supported sync modes​

The Google Sheets source connector supports the following sync modes:

Supported Streams​

Each sheet in the selected spreadsheet is synced as a separate stream. Each selected column in the sheet is synced as a string field.

Airbyte only supports replicating Grid sheets.

Data type map​

Each sheet in the selected spreadsheet is synced as a separate stream. Each selected column in the sheet is synced as a string field.

Airbyte only supports replicating Grid sheets.

Integration TypeAirbyte TypeNotes
any typestring

Limitations & Troubleshooting​

Expand to see details about Google Sheets connector limitations and troubleshooting.

Connector limitations​

Rate limiting​

The Google API rate limits are:

  • 300 read requests per minute per project
  • 60 requests per minute per user per project

Airbyte batches requests to the API in order to efficiently pull data and respect these rate limits. We recommend not using the same user or service account for more than 3 instances of the Google Sheets source connector to ensure high transfer speeds.

Troubleshooting​

  • If your sheet is completely empty (no header rows) or deleted, Airbyte will not delete the table in the destination. If this happens, the sync logs will contain a message saying the sheet has been skipped when syncing the full spreadsheet.
  • Connector setup will fail if the spreadsheet is not a Google Sheets file. If the file was saved or imported as another file type the setup could fail.
  • Check out common troubleshooting issues for the Google Sheets source connector on our Airbyte Forum.

Reference​

Config fields reference

Field
Type
Property name
object
credentials
string
spreadsheet_id
boolean
allow_leading_numbers
integer
batch_size
boolean
combine_letter_number_pairs
boolean
combine_number_word_pairs
boolean
names_conversion
boolean
remove_leading_trailing_underscores
boolean
remove_special_characters
array<object>
stream_name_overrides

Changelog​

Expand to review
VersionDatePull RequestSubject
0.11.02025-06-1161489Feature: Added Streeam Name Override Options
0.10.02025-06-0960836Feature: Added additional sanitization flags when using Convert Column Names to SQL-Compliant Format (names_conversion)
0.9.62025-05-2260874Use custom backoff policy on 429 errors for single sheets
0.9.52025-05-1360259Fix whitespaces used for column names when enabling names_conversion
0.9.42025-03-0154989Update dependencies
0.9.32025-02-2254434Update dependencies
0.9.22025-02-1553720Update dependencies
0.9.12025-02-0851696Update dependencies
0.9.02025-02-0453154Promoting release candidate 0.9.0-rc.3 to a main version.
0.9.0-rc.32025-01-3152682Fix stream name typing
0.9.0-rc.22025-01-3152671Fix sheet id encoding
0.9.0-rc.12025-01-3050843Migrate to low-code
0.8.52025-01-1144270Starting with this version, the Docker image is now rootless. Please note that this and future versions will not be compatible with Airbyte versions earlier than 0.64
0.8.42024-12-0948835Implementing integration tests
0.7.42024-09-0945108Google Sheets API errors now cause syncs to fail
0.7.32024-08-1243921Update dependencies
0.7.22024-08-1043544Update dependencies
0.7.12024-08-0343290Update dependencies
0.7.02024-08-0242975Migrate to CDK v4.3.0
0.6.32024-07-2742826Update dependencies
0.6.22024-07-2241993Avoid syncs with rate limits being considered successful
0.6.12024-07-2042376Update dependencies
0.6.02024-07-1742071Migrate to CDK v3.9.0
0.5.112024-07-1341527Update dependencies
0.5.102024-07-0941273Update dependencies
0.5.92024-07-0641005Update dependencies
0.5.82024-06-2840587Replaced deprecated AirbyteLogger with logging.Logger
0.5.72024-06-2540560Catch an auth error during discover and raise a config error
0.5.62024-06-2640533Update dependencies
0.5.52024-06-2540505Update dependencies
0.5.42024-06-2240129Update dependencies
0.5.32024-06-0639225[autopull] Upgrade base image to v1.2.2
0.5.22024-06-0238851Emit state message at least once per stream
0.5.12024-04-1135404Add row_batch_size parameter more granular control read records
0.5.02024-03-2636515Resolve poetry dependency conflict, add record counts to state messages
0.4.02024-03-1936267Pin airbyte-cdk version to ^0
0.3.172024-02-2935722Add logic to emit stream statuses
0.3.162024-02-1235136Fix license in pyproject.toml.
0.3.152024-02-0734944Manage dependencies with Poetry.
0.3.142024-01-2334437Fix header cells filtering
0.3.132024-01-1934376Fix names conversion
0.3.122023-12-1433414Prepare for airbyte-lib
0.3.112023-10-1931599Base image migration: remove Dockerfile and use the python-connector-base image
0.3.102023-09-2730487Fix bug causing rows to be skipped when batch size increased due to rate limits.
0.3.92023-09-2530749Performance testing - include socat binary in docker image
0.3.82023-09-2530747Performance testing - include socat binary in docker image
0.3.72023-08-2529826Remove row batch size from spec, add auto increase this value when rate limits
0.3.62023-08-1629491Update to latest CDK
0.3.52023-08-1629427Add stop reading in case of 429 error
0.3.42023-05-1529453Update spec descriptions
0.3.32023-08-1029327Add user-friendly error message for 404 and 403 error while discover
0.3.22023-08-0929246Add checking while reading to skip modified sheets
0.3.12023-07-0628033Fixed several reported vulnerabilities (25 total), CVE-2022-37434, CVE-2022-42898
0.3.02023-06-2627738License Update: Elv2
0.2.392023-05-3126833Remove authSpecification in favour of advancedAuth in specification
0.2.382023-05-1626097Refactor config error
0.2.372023-02-2123292Skip non grid sheets.
0.2.362023-02-2123272Handle empty sheets gracefully.
0.2.352023-02-2323057Slugify column names
0.2.342023-02-1523071Change min spreadsheet id size to 20 symbols
0.2.332023-02-1323278Handle authentication errors
0.2.322023-02-1322884Do not consume http spreadsheets.
0.2.312022-10-0919574Revert 'Add row_id to rows and use as primary key'
0.2.302022-10-0919215Add row_id to rows and use as primary key
0.2.212022-10-0415591Clean instantiation of AirbyteStream
0.2.202022-10-1017766Fix null pointer exception when parsing the spreadsheet id.
0.2.192022-09-2917410Use latest CDK.
0.2.182022-09-2817326Migrate to per-stream states.
0.2.172022-08-0315107Expose Row Batch Size in Connector Specification
0.2.162022-07-0713729Improve configuration field description
0.2.152022-06-0213446Retry requests resulting in a server error
0.2.132022-05-0612685Update CDK to v0.1.56 to emit an AirbyeTraceMessage on uncaught exceptions
0.2.122022-04-2012230Update connector to use a spec.yaml
0.2.112022-04-1311977Replace leftover print statement with airbyte logger
0.2.102022-03-2511404Allow using Spreadsheet Link/URL instead of Spreadsheet ID
0.2.92022-01-259208Update title and descriptions
0.2.72021-09-278470Migrate to the CDK
0.2.62021-09-276354Support connecting via Oauth webflow
0.2.52021-09-125972Fix full_refresh test by adding supported_sync_modes to Stream initialization
0.2.42021-08-055233Fix error during listing sheets with diagram only
0.2.32021-06-093973Add AIRBYTE_ENTRYPOINT for Kubernetes support
0.2.22021-04-202994Formatting spec
0.2.12021-04-032726Fix base connector versioning
0.2.02021-03-092238Protocol allows future/unknown properties
0.1.72021-01-211762Fix issue large spreadsheet
0.1.62021-01-271668Adopt connector best practices
0.1.52020-12-301438Implement backoff
0.1.42020-11-301046Add connectors using an index YAML file
Was this page helpful?