Google Drive

Google Drive is a file storage and synchronization service. Fivetran supports syncing files from your Google Drive to your destination. You can sync multiple files either as unique tables or as a single table in your destination.

Features

Setup guide

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

Sync overview

The Google Drive connector offers dual mode sync strategies. You can opt to:

• sync each file as a unique table in your destination (Magic Folder Mode)
• sync multiple files into a single destination table that you specify (Merge Mode)

We sync all files in the Google Drive folder to your destination, even if someone without a Fivetran account uploaded them. Anyone with write access to the folder can drop files into that folder; Fivetran automatically syncs the files from your Google Drive folder to your destination.

Magic Folder Mode

We retrieve all the files from the folder that you specify into your destination.

Each file is synced as a separate table in the destination. For spreadsheet files, we sync each worksheet in the spreadsheet as a unique table in your destination.

We don't sync empty worksheets. We don't sync an Excel worksheet if the entire first row of the worksheet is empty. We don't sync a column if its first row is empty.

For Google Sheets, we detect the data range to be synced.

We only sync the files inside the specified folder. We ignore nested folders.

Magic Folder Mode doesn't support incremental syncs. To detect changes in the files of your cloud folder, we use the last modified date of the files. After the initial historical sync, we re-import only the recently modified files in every sync. For information about the sync strategy, see our Cloud Collaboration connectors documentation.

Merge Mode

We retrieve all the files from each folder and its subfolders into a single table into your destination.

Merge Mode supports incremental syncs.

Syncing spreadsheets

To sync Excel files and Google Sheets in Merge Mode, you must select xls/xlsx/xlsm/Google Sheet as the file type in the connector setup form and then specify the cell reference during connector configuration. We use the cell reference to sync contiguous data to the right of the cell and below it in the Excel worksheet.

Example:

Let's assume there are 4 Excel worksheets in a workbook:

• Data1
• Data2
• Data3
• Data4

If you want to sync your data from cell C3 of the "Data2" worksheet, then your cell reference will be "Data2”!C3. So, the connector syncs data from the "Data2" worksheet starting at cell C3.

NOTE: We do not support finding data ranges if the rows contain merged cells. We do not consider the rows with merged cells and skip them while finding data ranges.

Schema information

Magic Folder Mode

If you select the Magic Folder Mode, we use the file name (without the extension) as the destination table name. For example, we sync a sample.csv file as SAMPLE table.

For spreadsheet files, we use a combination of the file name and worksheet name as the destination table name. For example, two worksheets called Sheet1 and Sheet2 in a spreadsheet My Workings are synced as MY_WORKINGS_SHEET_1 and MY_WORKINGS_SHEET_2 tables. We use the values present in the first row of the worksheet as the column names in the destination table.

For more information about the schema and table naming rule set, see our naming conventions documentation.

If we find files of the same name but with different extensions in the folder, we check the last modified dates of the files and sync only the least recently modified file.

Merge Mode

If you select the Merge Mode, we use the table name that you specify as the destination table name.

Upsert with custom primary keys Beta

For upserting with primary keys, use the Primary Key used for file process and load field.

You can select the custom primary keys during the connector setup and use them to update or insert new rows to the destination. We process the files in order of their modified time.

If we detect that one of the selected primary keys has a null value in your files, we display a warning on the connector dashboard but insert the record with a null value in your destination.

If the destination does not support null values, we insert the row with the default value for the column.

You can choose the primary keys only before the initial sync. Once you perform the initial sync, you won't be able to modify the primary keys.

NOTE: You can modify the primary keys only if your initial sync fails. If your initial sync is successful, the option to modify the primary keys is not available.

Types of primary keys

The Google Drive connector uses the following types of primary keys:

• Primary key - a column that contains values that uniquely identify each row in your data.
• Composite primary key - a set of columns which, in combination, uniquely identifies a row in your data.
• Surrogate primary key - a column or columns that the system adds to provide a unique identifier for a row in your data.

Sync limitations

We do not sync links to files from the cloud folder. Your Google Drive folder must contain the file.

We don’t sync hyperlink values from a worksheet.

We don't sync worksheets with pivot tables.

We don't sync files uploaded through the Google Drive app.

Google Sheets limitations

• We don't support incremental syncs for worksheets created using IMPORTRANGE inside Google Drive because it does not update the modification time of such Google Sheets that use the IMPORTRANGE function to import the data from another sheet.
• If you update your Google Sheets file through Google Forms, then the last modified date for the file is not updated. Fivetran uses this date to detect updates to your Google Sheets file, and therefore may not sync the file regularly. To sync your Google Sheets files populated by Google Forms, make sure their last modified date is updated regularly.