Search documentation...

K
ChangelogBook a demoSign up

Google Sheets

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

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
AppendAppend the result set of a model to the selected sheet and add rows as the model grows

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.

Connection selection

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.

OAuth example

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.

Service Account settings

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.

Share spreadsheet

You can always access the service account email from the destination's overview page, should you need to share additional sheets.

Share spreadsheet

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.

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.

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

Select Google Sheet by ID

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.

Select Google Sheet by ID

Then, select the sheet within the spreadsheet that you would like to sync to.

Sheet settings in sync configuration

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.

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.

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.

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.

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.

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.

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.

Range setting in sync configuration

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:

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.

File type example

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.

Convert to Google Sheet dropdown

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.

Common errors

If you encounter an error or question not listed below and need assistance, don't hesitate to . 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.

Change column type

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.

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.

Last updated: Aug 27, 2024

On this page

Supported syncingConnect to Google SheetsAuthenticate with a user accountAuthenticate with a service accountSync configurationSheet selectionData formatEnable new sheet creationData range selectionTips and troubleshootingRate limitsFind sheets originating from an Excel file uploadCommon errorsSync alerts

Was this page helpful?