Google Sheets
Overview
Integrate with Google Sheets using CData connector. We utilise official CData Python (opens in a new tab) connector to ingest your data. The connector creates a dedicated table for each spreadsheet that you ingest, allowing multiple spreadsheets to be configured in one source.
Features
| Feature Name | Supported |
|---|---|
| Full Import | Yes |
| Incremental Import | No |
Getting started
Requirements and prerequisites
Google account with access to spreadsheets to ingest.
Set up guide
Create a source
Create a new Source of type CData - Google Sheets.
Configure a secret
Add a new secret (or use an existing one). Select the only available authentication option - OAuth, and give your consent to extract data from spreadsheets on your behalf.
Define settings
Configure one of the three main settings:
- Spreadsheet - A comma-separated list of the IDs of the spreadsheets to be viewed. For example:
1SpQR7niJGLd11JxKtmqjHBIu4tLnpsG42Jh_o2T5I64 - FolderName - A comma-separated list of the folders' names from which to retrieve spreadsheets in the format FolderName=
'name1,name2'. - Folder Id - A comma-separated list of the folders' ids from which to retrieve spreadsheets in the format FolderId=
'id1,id2,id3'.
Configure optional settings
- Show Trashed Files - Indicates whether or not the trashed files will be listed.
- Skip Hidden Sheets - Indicates whether or not the hidden sheets will be listed.
- Supports All Drives - Determines whether or not to retrieve Drive items.
- Use Id As Table Name - Indicates whether or not to use Ids as Spreadsheet and Sheet name.
- Team Drive - A drive's names or ids from which to retrieve spreadsheets in the format TeamDrive =
'Shared drive 2, Shared drive 3', or TeamDrive ='0BKwyFj1j9FOsUk9EVO, 0ANMIP9RIe1LQUk9PVA'. - Domain Shared Files Only - Boolean determining if the exposed sheets are limited to only the sheets shared to the user's domain or not.
- Recurse Folders - Used in case FolderId/FolderName properties are defined. If set to
Truethis makes the driver return all the Spreadsheets inside nested folders, else the driver will return only the files directly to that folder. By default, this is set to false. - Ignore Error Values - When enabled, fields where the provider detects error values will be returned as
NULL. If disabled, the provider throws an error if an error value is detected in any field.
Limitations
- The recurse folder property is required in the case of nested folders. Otherwise, files inside nested folders won't be discovered and ingested.
- Please note that importing two files with the same name in multiple folders at once is not possible. In this case, we recommend creating multiple sources, one per folder with a duplicated file name.
Performance considerations
The Google API rate limit (opens in a new tab) is 100 requests per 100 seconds per user and 500 requests per 100 seconds per project. Y42 batches requests to the API in order to efficiently pull data and respect these rate limits. We recommended not using the same secret for more than 3 instances of the Google Sheets source connector to ensure high transfer speeds.