Sync warehouse data to spreadsheets used by sales, ops, and marketing teams
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 Sync Modes||Description|
|Mirror||Sync the full result set of the model to the selected destination sheet|
|Snapshot||Create a new sheet on every sync with the full result set of the model|
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.
Select the Google Sheets destination option. Provide Hightouch access to your Google Account with the following scopes:
After authorizing, Hightouch connects to Google Sheets.
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.
To give Hightouch access to a certain spreadsheet, share the spreadsheet with the service account email with the "Editor" permissions.
You're all set. You'll be able to sync directly to the spreadsheets while keeping the rest of your data private.
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.
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.
Be sure your spreadsheet and sheet names don't contain any colons. Google Sheets is unable to parse names that contain colons.
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.
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.
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.
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.
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.
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.
You may need to hit the refresh button next to the drop-down selector for spreadsheets after you convert it.
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.
Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.