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.
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.
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.
You can also add data to an existing sheet with Append mode. In this mode, rows added to your model are added to the sheet. Rows changed and removed in your model are ignored. This may result in faster syncs.
Mirror mode replaces the entire selected sheet's contents with the rows from your model.
Ensure that your sheet doesn't contain data that shouldn't be overwritten if you select this mode.
You can choose to select the Google Sheets file you want to update either by its name or ID.
You can find your spreadsheet's ID in its URL: https://docs.google.com/spreadsheets/d/<spreadsheetId>/edit#gid=0
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.
Colons (:) in spreadsheet and sheet titles can cause errors. Ensure your spreadsheet and sheet titles don't include any.
Avoid setting up multiple syncs to the same sheet, as this can corrupt your data.
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.
Google Sheets has a limit of 10 million cells in a sheet.
If your sync contains more data than this, you can enable Hightouch to create new sheets with the additional data.
For each additional 10 million cells, Hightouch creates a new sheet with the original sheet's name suffixed with an underscore and a number.
For example, "sheetname_1" for the first additional 10 million cells, "sheetname_2" for the second 10 million cells, etc.
Ensure these names don't conflict with existing sheets' names to avoid them being overwritten.
To use this configuration option, ensure the user you authenticated with has access to the spreadsheet's folder, not just the spreadsheet itself.
Hightouch released enabling new sheet creation on June 22, 2023.
If you set up your Google Sheets destination before this date and want to use this option, you need to reauthorize your connection.
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 A1:Z1000.
If you leave this field blank, Hightouch mirrors data to the entire spreadsheet, dependent on your model's query results.
You may receive an error from Google if you exceed your quota for write requests during a sync: Quota exceeded for quota metric.
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
set up multiple Google Sheets destinations in your workspace so as to reduce the number of syncs that use the same destination
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 .XLS or .XLSX file.
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 .XLS or .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.
This error indicates that the sheet within the spreadsheet selected in the sync configuration has either been renamed or deleted in Google Sheets. Select a new sheet from the dropdown menu to restore your sync.
This error may occur when the spreadsheet you're syncing to contains sheets with charts or pivot tables.
Try moving the data you would like to update with Hightouch to a separate spreadsheet.
Then reference this data's range using a formula like IMPORTRANGE in the spreadsheet with your charts and analysis.
This way, the sheet that Hightouch updates won't have the timeout issue, and you can still see the data on the sheet that contains charts or pivot tables.
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 Stringin your model configuration.
This error indicates that the selected sheet or a data range in that sheet is protected.
Remove the protection from the sheet or range to enable syncing your data.
You can read more about this in Google's documentation.
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.