Skip to content

Google Sheets

This page describes how to configure a Google Sheets data source. With Data Loader, you can replicate and load your source data into your target destination.

Schema Drift Support: Yes. Read Schema Drift to learn more.

Return to any page of this wizard by clicking Previous.

Click X in the upper-right of the UI and then click Yes, discard to close the pipeline creation wizard.

Prerequisites

  • Read the Allowed IP addresses topic before you begin. You may not be able to connect to certain data sources without first allowing the Batch IP addresses. In these circumstances, connection tests will always fail and you will not be able to complete the pipeline.
  • The Google Sheets and Google Drive APIs should be enabled for the service account, if connecting via it.

:::info{title='Note'}

  • Google Sheets target tables are dropped and recreated each run. This handles any metadata changes at the data source.
  • A _temp version of the target table is created, which represents the staging table. This is then copied into the target table, defined by the UI. This makes this a packaged load process where multiple sheets from a spreadsheet can be loaded in the same Pipeline—it's not an incremental load.
  • If a range specified is less than the number of columns in the sheet, the unspecified columns will be created, and the target will be empty. For example, if a sheet has columns A, B, C, and the range A1:B5 is specified, column C will be empty.
  • If a header and range are specified, the range will be ignored. :::

Create pipeline

  1. In Data Loader, click Add pipeline.
  2. Choose Google Sheets from the grid of data sources.
  3. Choose Batch Loading.

Connect to Google Sheets

Configure the Google Sheets database connection settings, specifying the following:

Property Description
Google Sheets Connection Select a connection from the drop-down menu, or click Add Connection if one doesn't exist.
Connection Name Give a unique name for the connection, and click Connect. A new browser tab will open, where Google will ask you to confirm authorization using valid credentials.
Spreadsheet Use the drop-down menu to select the Google Sheet you want to use. The pipeline will fail if the Google Sheet name has a leading or trailing space.
Advanced settings Additional JDBC parameters or connection settings. Expand the Advanced settings, and choose a parameter from the drop-down menu. Enter a value for the parameter, and click Add parameter for any extra parameters you want to add. For a list of compatible connection properties, read Allowed connection properties.

Click Continue.

Configure spreadsheet

  • All sheets of a Google Sheets file are available. Define a range for each sheet by entering a value in the Range column.
  • You can indicate which sheets use a header row by toggling Contains header.
  • You can remove a sheet from the pipeline by selecting the - symbol alongside the sheet.
  • You can add or replace a sheet by clicking +Add another sheet.

Review your data set

  • Here you can review all of the sheets included in the pipeline, and the columns of each sheet. If you don't need to make any changes, click Continue.
  • Make changes to columns in each sheet by clicking Configure alongside the appropriate sheet. Use the arrow buttons to move columns to the Columns to extract and load listbox and then reorder any columns with click-and-drag. Additionally, select multiple columns using the SHIFT key. Use the Filter field to locate columns with a text string. Click Done to finish.

Choose destination

  1. Choose an existing destination or click Add a new destination.
  2. Select Snowflake or Amazon Redshift as the destination.

Set frequency

Property Description
Pipeline name A descriptive label for your pipeline. This is how the pipeline appears on the pipeline dashboard and how Data Loader refers to the pipeline.
Sync every The frequency at which the pipeline should sync. Day values include 1—7. Hour values include 1—23. Minute values include 5—59. The input is also the length of delay before the first sync.

Currently, you can't specify a start time.

Once you are happy with your pipeline configuration, click Create pipeline to complete the process and add the pipeline to your dashboard.