Google Sheets
This page contains the setup guide and reference information for the Google Sheets source connector.
The Google Sheets source connector pulls data from a single Google Sheets spreadsheet. Each sheet within a spreadsheet can be replicated. To replicate multiple spreadsheets, 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.
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.
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
- Open the Service Accounts page in your Google Cloud console.
- Select an existing project, or create a new project.
- At the top of the page, click + Create service account.
- Enter a name and description for the service account, then click Create and Continue.
- Under Service account permissions, select the roles to grant to the service account, then click Continue. We recommend the Viewer role.
Generate a key
- Go to the API Console/Credentials page and click on the email address of the service account you just created.
- In the Keys tab, click + Add key, then click Create new key.
- 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
- Go to the API Console/Library page.
- Make sure you have selected the correct project from the top.
- Find and select the Google Sheets API.
- 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 source connector in Airbyte
- Log in to your Airbyte Cloud account.
- In the left navigation bar, click Sources. In the top-right corner, click + New source.
- Find and select Google Sheets from the list of available sources.
- For Source name, enter a name to help you identify this source.
- 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.
- 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.
- For Batch Size, enter an integer which represents batch size when processing a Google Sheet. Default value is 200. 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 200 would process rows 1-201, then 201-401 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. Default value should cover most of the cases, but if a google sheet has over 100,000 records or more, consider increasing batch_size value.
- (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 tocafe_earnings_2022
. We recommend enabling this option if your target destination is SQL-based (ie Postgres, MySQL). Set to false by default. - Click Set up source and wait for the tests to complete.
Output schema
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.
Supported sync modes
The Google Sheets source connector supports the following sync modes:
Data type map
Integration Type | Airbyte Type | Notes |
---|---|---|
any type | string |