Google Sheets
Google Sheets is a web-based application that allows users to create, update, and modify spreadsheets and share the data live online.
NOTE: The Google Sheets connector syncs data from a specific named range in your file to a single destination table. To sync data from multiple sheets within a spreadsheet as unique destination tables, use our Google Drive connector.
Features
| Feature Name | Supported | Notes |
|---|---|---|
| Capture deletes | check | |
| History mode | ||
| Custom data | check | |
| Data blocking | check | |
| Column hashing | check | |
| Re-sync | check | |
| API configurable | check | API configuration |
| Priority-first sync | ||
| Fivetran data models | ||
| Private networking | ||
| Authorization via API | check |
Setup guide
Follow our step-by-step Google Sheets setup guide to connect Google Sheets with your destination using Fivetran connectors.
Sync overview
After being authenticated, Fivetran connects to your Google Sheet, pulls the data from the designated named range, then creates a matching table in your destination, and loads its corresponding initial data. Fivetran then continues to check and sync changes to the named range on the update frequency that you specify in the Fivetran UI. If there is a change in the named range, we replace the entire data in the destination.
The Google Sheets connector is great for manually updating tables in your destination. It's easy to share with your entire team so that you can have lots of contributors.
Limitations
If you update your Google Sheets file through Google Forms, then the file's last modified date is not updated. Fivetran uses this date to detect updates to your Google Sheets file, so we may not sync the file regularly. To sync your Google Sheets files populated by Google Forms, ensure their last modified date is updated regularly.
Schema information
The schema maps directly from the named range in your sheet. Each Google Sheets connector maps one named range to a table in the schema that you designate. Each column of the named range will map to a column in its target table. We ignore the _fivetran_synced column because we use that name for the system column that keeps track of when each row was last successfully synced.
Naming
If you change the name of your named range, the connector will break and you will need to edit the setup form with the new named range.
NOTE: The name of the workbook or the sheets do not affect your target tables.
Type transformations and mapping
On the initial load, Fivetran parses all the data in each column and automatically assigns the appropriate type for the column.
When the connector updates, Fivetran will rescan all the data in the sheet, and update the column types if value is found with a wider type. If a widening change is made, Fivetran will NOT make any narrowing changes. For example, if you have a column of integers it will be cast as a integer in the destination. If you accidentally add a string to the column and the integration is updated, Fivetran will widen the entire column in your target table to now be TEXT.
This widening behavior can be disruptive to your analytics if mistakes are made (which is easy to do in spreadsheets). A strategy to prevent this is to create a VIEW of your table in your destination where you cast every column to TEXT, then you create a second VIEW on top of the first VIEW, where you cast each column to the final type that you want it to be. In this case, an incorrect value will be nullified.
We only write dates in the ISO 8601 format as TIMESTAMP or DATE data types – everything else is interpreted as STRING. For instance, "2020-10-04T16:05:30Z" will be correctly written as a timestamp in the destination, but "2020-10-4 4:05:30PM" will be written as a string.
Changes to your sheet
If you change the name of the sheet or tab, it will not affect the named range.
Initial sync
Syncs all data in the chosen named range.
Updating data
Fivetran checks for updates in the named range of the spreadsheet on the interval that you select in the Fivetran dashboard.
Adding data
- When you add a new row to the named range, we add the new row to the destination table.
- When you add a new column to the named range, we add the new column to the destination table.
Deleting data
When you delete a row from the named range, we hard delete the row from the destination table.
When you remove a column from the named range, we do the following:
- We stop syncing the column
- We don't drop the column in the destination table but insert a
nullvalue
Excluding data
Columns with a title that is left empty or contains only spaces (the first cell of the column) are ignored. This can be used to ignore particular columns on purpose.