Sync warehouse data to spreadsheets used by sales, ops, and marketing teams
|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|
For more information about sync modes, refer to the sync modes docs.
Connect to Google Sheets
Go to the Destinations overview page and click the Add destination button. Hightouch provides two Google Sheets options:
- Google Sheets - User Account: Use this option if you want to authenticate access to Hightouch with a particular user's login. This gives Hightouch access to any Google Sheet the user has Editor access to. This option uses OAuth for authentication.
- Google Sheets - Service account (Recommended): This option provides fine-grained control over access to your data.
Using a service account is best practice for a few reasons:
- You can grant service accounts access to specific sheets. This means that only the necessary permissions are granted to the service account, instead of allowing access to all files belonging to a user. This reduces the risk of unauthorized access to sensitive data or functionality.
- Service accounts are designed to represent applications or services, not individuals. You don't have to worry about disruptions to your syncs if the user who authenticated has their permissions changed or if they leave the organization.
Authenticate with a user account
Select the Google Sheets - User Account destination option and click Continue. Then select to Log in to Google Sheets, select your Google account, and click Allow to the requested permissions.
Hightouch uses the same OAuth mechanism for all Google destinations. If you've set up other Google destinations, such as Google Ads, Campaign Manager, or Display & Video 360, they appear here.
After authorizing, Hightouch connects to Google Sheets. Click Continue and complete setup by giving the destination a descriptive name.
Authenticate with a service account
Select the Google Sheets (Service Account) destination option and click Continue. Then, click Generate a service account. Hightouch then generates a Google Service Account Email.
Click Continue and complete setup by giving the destination a descriptive name.
To give Hightouch access to a particular spreadsheet, share the spreadsheet with the service account email with Editor permissions.
You can always access the service account email from the destination's overview page, should you need to share additional sheets.
Once you've set up your Google Sheets destination and have a model to pull data from, you can set up your sync configuration to begin syncing data. Go to the Syncs overview page and click the Add sync button to begin. Then, select the relevant model and the Google Sheets destination you want to sync to.
The Google Sheets destination allows you to Mirror your data to a spreadsheet or create a new sheet on every sync with Snapshot mode.
If you select Mirror mode, Hightouch overwrites 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.
You can choose to select the Google Sheets file you want to update either by its name or ID.
You can find your sheet's ID in its URL:
If you don't see your spreadsheet's name in the dropdown, make sure you authorized the right account or invited the Hightouch service account email to your spreadsheet, depending on your setup mode. If you updated a sheet's permissions, click the refresh button to see newly authorized sheets.
Then, select the sheet within the spreadsheet that you would like to sync to.
:) in spreadsheet and sheet titles can cause errors. Ensure your spreadsheet and sheet titles don't include any.
Google Sheets accepts inputted data in two ways:
- Raw Won't parse the values the user has entered and stores values as-is. Keep in mind that Hightouch does some data casting and may send numbers and dates as strings. This process can add a leading apostrophe to values in Google Sheets cells.
- 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 want to sync numbers, dates, or other non-string values in their original data type and not converted to strings, use User entered mode.
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.
For more information, read the Google documentation.
Selecting a data range
Hightouch supports selecting a manual range in your Google Sheets to write data to. This allows you to preserve formulas, notes, or other data in your spreadsheets' margins.
To use a manual range enter a range into the Custom Cell Range input in the format
If you leave this field blank, Hightouch mirrors data to the entire spreadsheet, dependent on your model's query results.
Tips and troubleshooting
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, stagger or schedule your syncs to Google Sheets to run in sequence to avoid simultaneous write requests between syncs.
Find sheets 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 can't find the spreadsheet in the sheet selection dropdown, confirm that it's not an
You can see the file type of a file next to its name in the Google Sheets UI.
Hightouch can only update Google Sheets files, not
.XLSX files. To convert a file to a Google Sheets file, go to File > Save as Google Sheets.
After converting the file to a Google sheet, it becomes available in the dropdown selector.
You may need to click the refresh button next to the dropdown selector for spreadsheets after you've converted a sheet, or updated its access.
If you want to work with Excel files, consider using the Microsoft Excel destination.
If you encounter an error or question not listed below and need assistance, don't hesitate to . We're here to help.
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.
The caller does not have permission
This error happens when the sheet you are syncing to is private. To correct this, ensure you're using a sheet that is shared with the user or service account you used set up your Google Sheets destination.
Invalid values : list_value
The Google Sheets destination doesn't support syncing object or array type columns. To resolve the error, change the type of any object or array type columns to String in your model configuration.
Hightouch can alert you of sync issues via Slack, PagerDuty, SMS, or email. For details, please visit our article on alerting.