Skip to main content

Google Sheets Source Connector

The Google Sheets source connector lets DataSync retrieve data from Google Sheets and load it into your data warehouse. Before you start, you need a Google account and an OAuth client set up in Google Cloud Console so DataSync can authenticate. Setup involves two parts that reference each other: you create an OAuth client in Google Cloud Console to grant DataSync access, then create a source connection in DataSync using the credentials from that client. It helps to keep both Google Cloud Console and DataSync open side by side while you work through the steps.

Once your source connection is ready, configure your destination connection to finish the setup.

important

Authenticate with the same Google account used to create the Google Cloud project and OAuth client.

Step 1: Set up Google Cloud​

Create or select a Google Cloud project​

  1. Go to the Google Cloud Console.
  2. Sign in with your Google account.
  3. Select Select a project, then do one of the following:
    • Select an existing project.
    • Select New project, enter a project name and location, then click Create.

Enable required APIs​

  1. In Google Cloud Console, select APIs & Services, then Enabled APIs & Services.
  2. If you have multiple projects, make sure the right one is selected.
  3. Select Enable APIs and services.
  4. Search for Google Sheets API and click Enable.
  5. Search for Google Drive API and click Enable.

Create OAuth credentials​

  1. In Google Cloud Console, go to APIs & Services, then Credentials.
  2. Select Create credentials, then OAuth client ID.
  3. Under Application type, select Web application.
  4. Under Authorized JavaScript origins, add your DataSync server URL and your company domain.
  5. Leave Authorized redirect URIs empty for now.
  6. Click Create, then copy the Client ID and Client Secret for later use.

note

Values in Authorized JavaScript origins must be valid URLs ending in a domain extension such as .com or .net.

Step 2: Create the source connection in DataSync​

Set up the connection​

  1. Log in to DataSync.
  2. From the welcome screen, select Connections.
  3. Next to Source Connections, click New.
  4. Select Google Sheets.
  5. Enter all required connection properties.
  6. Copy the Callback URL shown in Connection Properties.

Add the callback URL in Google Cloud Console​

  1. In Google Cloud Console, go to APIs & Services, then Credentials.
  2. Select your OAuth client.
  3. Under Authorized redirect URIs, add the Callback URL.
  4. Click Save.

Authenticate and authorize​

  1. (Optional) In the Additional Connection Properties panel, click Add property and enter any extra parameters you need.
  2. Configure the advanced settings to match your environment, including Tracking Type.
  3. Click Save and complete the Google sign-in and consent prompts.

Connection properties​

PropertyWhat to enter
DescriptionUnique name for the connection. Example: Google Sheets.
Callback URLRedirect URL automatically generated by DataSync. Must be added to Authorized redirect URIs in your Google Cloud OAuth client.
Client IDOAuth client ID from APIs & Services > Credentials in Google Cloud Console. Example: 1234567890-abc.apps.googleusercontent.com.
Client SecretOAuth client secret from APIs & Services > Credentials in Google Cloud Console. Example: xK8mNpL7vRtYw2cDjHs9bEuFgAiZo3qB.
VerbosityControls how much detail the connector writes to the log. Each level includes everything from the level below it, plus additional detail.

  • 1 logs queries, row counts, execution start and end times, and errors.
  • 2 adds cache queries and HTTP headers.
  • 3 adds request and response bodies.
  • 4 adds transport-level communication.
  • 5 adds all interface commands.
Enable PoolingEnables connection pooling, which keeps a set of database connections open and reuses them across extractions instead of opening a new connection each time. This reduces overhead and improves performance when multiple extractions run at the same time.
Pool idle timeoutTime in seconds a connection can stay idle before returning to the pool.
Max Pool SizeMaximum number of connections allowed in the pool at the same time.
Pool wait timeTime in seconds DataSync waits for an available connection before throwing an error.

Additional connection properties​

Use this panel to enter connection string properties not available in the Connection Properties panel. For sensitive values such as passwords, set the type to Encrypted. The value is hidden in the interface and stored encrypted in the back end.

PropertyWhat to enter
PropertyConnection string property that defines the action or behavior. Example: ReadOnly
ValueValue for the property. Example: True
TypeVisibility settings: Visible or Encrypted.

Advanced settings​

These settings control how the connector tracks data changes, handles time and regional configuration, and processes records during extraction. Configure them to match your Google Sheets environment so that results stay accurate and consistent.

SettingWhat to select
Tracking TypeMethod for tracking data changes: None or Date.
RegionRegion setting for the connector, if required by your setup.
Time ZoneTime zone matching your Google Sheets environment.
Time OffsetRefresh offset in seconds to compensate for timing issues in record selection. Minimum 0, maximum 3600.
Batch SizeNumber of records processed per batch during extraction. Larger batches can improve performance but use more memory. Default is 2000, maximum is 10000. Adjust based on your network speed and disk performance. The default works well in most cases.

Example setup​

Google Sheets source connection in DataSync
Completed Google Sheets source connection in DataSync, with all properties and settings filled in.