Google Sheets

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

Supported syncing

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.

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.

Sync configuration

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.

Sheet selection

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.

Data format

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.

For more information, read the Google documentation.

Enable new sheet creation

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.

Data range selection

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.

Tips and troubleshooting

Rate limits

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
• set up Google Sheets (Service Account) destinations rather than authenticating with a user account
• reach out to Google to request a quota increase

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 .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.

If you want to work with Excel files, consider using the Microsoft Excel destination.

Common errors

If you encounter an error or question not listed below and need assistance, don't hesitate to reach out. We're here to help.

Action would increase the number of cells in the workbook above the limit of 10,000,000 cells

Make sure to enable Hightouch to create new sheets if your data surpasses the 10 million cell limit.

Cannot read properties of null (reading 'properties')

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.

Internal error encountered

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.

Invalid values [1][2]: 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.

Range {...} exceeds grid limits

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.

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.

Sync alerts

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