Search documentation...

K

Google Sheets

Sync warehouse data to spreadsheets used by sales, ops, and marketing teams

Overview

Gone are the days when you had to manually update your Google Sheet spreadsheets every time your source data changed. With our Google Sheets integration, your spreadsheets can automatically pull updated data from your data warehouse on a predefined schedule, enabling your teams to never worry about data being stale or inaccurate.

Supported syncing

Supported Sync ModesDescription
MirrorSync the full result set of the model to the selected destination sheet
SnapshotCreate a new sheet on every sync with the full result set of the model

Getting started

Hightouch allows you to connect to Google Sheets in two ways:

  • User OAuth: The User OAuth authentication method allows Hightouch edit access to any Google Sheet your user has access to. This is for organizations that don't allow adding users from other domains to a Google Sheet.
  • Service Account: The Service Account authentication method provides fine-grained control over access to your data. You add a unique Hightouch service account as an editor user to specific sheets that you want to sync.

Connection selection

Authenticate with OAuth

Select the Google Sheets destination option. Provide Hightouch access to your Google Account with the following scopes:

OAuth example

After authorizing, Hightouch connects to Google Sheets.

Authenticate with a service account

Using the Google Sheets destination via the service account method allows you to limit what files Hightouch can access, instead of allowing access to all files belonging to a user.

Select the Google Sheets (Service Account) destination option. Then, after generating the service account, make note of the service account email.

Service Account settings

To give Hightouch access to a certain spreadsheet, share the spreadsheet with the service account email with the "Editor" permissions.

Share spreadsheet

You're all set. You'll be able to sync directly to the spreadsheets while keeping the rest of your data private.

Syncing data

The Google Sheets destination allows you to mirror your data to a spreadsheet. Hightouch does this by overwriting the entire contents of the selected sheet.

Please ensure that your sheet doesn't contain data that shouldn't be overwritten if you select Mirror mode.

Sheet configuration

In Hightouch, select the name of the spreadsheet that you would like to sync to. If you don't see your spreadsheet here, make sure you authorized the right account or invited the Hightouch service account email to your spreadsheet, depending on your setup mode.

Select the sheet within the spreadsheet that you would like to sync to. For Google Sheets, Hightouch deletes all the contents of the spreadsheet and writes your entire query result into the spreadsheet for every sync.

Sheet settings in sync configuration

Be sure your spreadsheet and sheet names don't contain any colons. Google Sheets is unable to parse names that contain colons.

Data format

Google Sheets accepts inputted data in two ways, raw or user entered.

  • Raw Won't parse the values the user has entered and stores values as-is. Keep in mind that Hightouch does some casting and may send numbers and dates as strings.
  • User Entered Parses values as if the user typed them into the UI. Numbers stay as numbers, but strings may convert to numbers, dates, etc. following the same rules that apply when entering text into a cell via the Google Sheets UI.

Data input settings in sync configuration

If you are inserting null values using the Raw data format, these won't register as blank fields though they may appear to be. Keep this in mind if you are using formulas that are looking for null values. If any of your formulas rely on blank fields, it's best to use User entered as the input mode.

If you wish for your formulas to use null values, make sure you use the User Entered data format.

For more information, read the Google documentation.

Selecting a data range

Hightouch supports selecting a manual range in your Google Sheets to mirror. This allows users to preserve formulas or notes in the margins of their spreadsheets.

To use a manual range enter a range into the Custom Cell Range input in the format A1:Z1000. If this field is blank Hightouch mirrors the entire spreadsheet.

Range setting in sync configuration

Tips and troubleshooting

Rate limits

You may receive an error from Google if you exceed your quota for write requests during a sync. For more information, read the Google usage limit documentation. To avoid this error, schedule your syncs to Google Sheets to run in sequence to avoid simultaneous write requests between syncs.

Can't find sheet originating from an Excel file upload

You may have trouble selecting that Google Sheet that was originally uploaded to Google as an Excel file. If you are the owner of the file, but are unable to find the spreadsheet in the drop-down, confirm that it's not still an .XLS or .XLSX file. You can see this in the Google Sheets UI.

File type example

If this is the case, the integration won't be able to update this file. You'll need to convert it to a proper Google Sheets file. Go to File > Save as Google Sheets. After converting the file to a Google sheet, it becomes available in the drop-down selector in the Sync Configuration.

Convert to Google Sheet dropdown

You may need to hit the refresh button next to the drop-down selector for spreadsheets after you convert it.

Common errors

Requested writing within range ['Example:Sheet'!A1], but tried writing to column [B]

This error happens if there is a colon in the name of the sheet that you're syncing to. Google Sheets is unable to parse names that contain colons. Replace colons in the name of the sheet with an underscore or remove the colon.

Sync alerts

Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.

Ready to get started?

Jump right in or a book a demo. Your first destination is always free.

Book a demoSign upBook a demo

Need help?

Our team is relentlessly focused on your success. Don't hesitate to reach out!

Feature requests?

We'd love to hear your suggestions for integrations and other features.

On this page

OverviewSupported syncingGetting startedAuthenticate with OAuthAuthenticate with a service accountSyncing dataSheet configurationData formatSelecting a data rangeTips and troubleshootingRate limitsCan't find sheet originating from an Excel file uploadCommon errorsSync alerts

Was this page helpful?