Google Sheets link

Updated 7 days ago

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.

Featureslink

Setup guidelink

Follow our step-by-step Google Sheets setup guide to connect Google Sheets with your destination using Fivetran connectors.

Sync overviewlink

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.

Schema informationlink

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.

Naminglink

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 mappinglink

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 sheetlink

If you change the name of the sheet or tab, it will not affect the named range.

Initial synclink

Syncs all data in the chosen named range.

Updating datalink

Fivetran checks for updates in the named range of the spreadsheet on the interval that you select in the Fivetran dashboard.

Adding datalink

• 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 datalink

• 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 null value

Excluding datalink

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.

Google Sheets limitationslink

Updates to Google Sheets files made by Google Forms will not update the Last Modified date of the file, and so may not sync regularly. Fivetran depends on the Last Modified date to see if a Sheets file has been updated. If you are syncing Sheets files populated by Google Forms you will need to set up a process to ensure that the Last Modified date of the file is also regularly updated.