Google Sheets Destination
The Google Sheets destination allows you to sync your warehouse data to a Google spreadsheet, and update the spreadsheet automatically as your data changes. This destination automates the workflow of exporting CSVs from your warehouse.
The Google Sheets destination can be connected to Reverse ETL warehouse sources only.
Getting started
Create a spreadsheet
- Create a new Google spreadsheet in your Google account.
- Copy the spreadsheet ID from the spreadsheet URL. The spreadsheet ID is the value after
d/and before/edit. For example, if your URL ishttps://docs.google.com/spreadsheets/d/1ejq5-UVP0SWZezRsdggzFxMqOmaJwZh7NkKPkQfi0Bb/edit#gid=0the ID is1ejq5-UVP0SWZezRsdggzFxMqOmaJwZh7NkKPkQfi0Bb. You will need this ID when you configure the destination.
Connect Google Sheets
- Create and configure your Reverse ETL source.
- Create a model for the data you plan to sync to Google Sheets.
- Navigate to the Reverse ETL > Destinations tab and click Add Destination.
- Select Google Sheets and click Next. Select the source you configured and name the destination.
- On the Settings tab, authenticate the Google Sheets API using OAuth. Select the email account that owns the spreadsheet you created above. Make sure you enable the
See, edit, create, and delete all your Google Sheets spreadsheetspermission. - On the Mappings tab, click Add Mapping and create a Post Sheet mapping. Within the mapping, configure how warehouse records should map to your Google Sheets spreadsheet.
- Enable the destination and configured mappings.
The Google Sheets destination only supports sending new or updated rows to your spreadsheet. Deleting rows is not supported.
Available Actions
Build your own Mappings. Combine supported triggers with the following Google Sheets-supported actions:
Mapping limits per destination
Individual destination instances have support a maximum of 50 mappings.
Post Sheet
Write values to a Google Sheets spreadsheet.
Post Sheet is a Cloud action. The default Trigger is: event = "updated" or event = "new"
| Field | Description |
|---|---|
| Record Identifier* | Type: STRINGProperty which uniquely identifies each row in the spreadsheet. |
| Spreadsheet ID* | Type: STRINGThe identifier of the spreadsheet. You can find this value in the URL of the spreadsheet. e.g. https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit |
| Spreadsheet Name* | Type: STRINGThe name of the spreadsheet. You can find this value on the tab at the bottom of the spreadsheet. Please provide a valid name of a sheet that already exists. |
| Data Format* | Type: STRINGThe way Google will interpret values. If you select raw, values will not be parsed and will be stored as-is. If you select user entered, values will be parsed as if you typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. |
| Fields* | Type: OBJECTThe fields to write to the spreadsheet. On the left-hand side, input the name of the field as it will appear in the Google Sheet. On the right-hand side, select the field from your data model that maps to the given field in your sheet. |
| Batch Data to Google Sheets | Type: BOOLEANSet as true to ensure Segment sends data to Google Sheets in batches. Please do not set to false. |
Post Sheet
Write values to a Google Sheets spreadsheet.
Post Sheet is a Cloud action. The default Trigger is: event = "updated" or event = "new"
| Field | Description |
|---|---|
| Record Identifier* | Type: STRINGProperty which uniquely identifies each row in the spreadsheet. |
| Spreadsheet ID* | Type: STRINGThe identifier of the spreadsheet. You can find this value in the URL of the spreadsheet. e.g. https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit |
| Spreadsheet Name* | Type: STRINGThe name of the spreadsheet. You can find this value on the tab at the bottom of the spreadsheet. Please provide a valid name of a sheet that already exists. |
| Data Format* | Type: STRINGThe way Google will interpret values. If you select raw, values will not be parsed and will be stored as-is. If you select user entered, values will be parsed as if you typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. |
| Fields* | Type: OBJECTThe fields to write to the spreadsheet. On the left-hand side, input the name of the field as it will appear in the Google Sheet. On the right-hand side, select the field from your data model that maps to the given field in your sheet. |
| Batch Data to Google Sheets | Type: BOOLEANSet as true to ensure Segment sends data to Google Sheets in batches. Please do not set to false. |
FAQ
How does Segment know if a row should be added or updated?
The Record Identifier mapping is used to make a distinction between adding a new row or updating an existing row. If two rows have the same Record Identifier, they are considered to be pertaining the same object and will live in the same row. Please ensure Record Identifier is set to a unique field.
How do I define the columns in my spreadsheet?
The Fields mapping controls which fields in your model will be written as columns. Input the desired column name(s) on the right, and select the data variable that will populate the value for that column on the left. Please note, at least one field must be configured to send data to Google Sheets or no columns will be created or synced.
How are columns formatted when synced to my spreadsheet?
When syncing data to Google Sheets, the columns will be arranged alphabetically, based on the names defined in the Fields mapping.
Can I add or remove columns after data has been synced?
Once data has been synced to Google Sheets, any subsequent addition or removal of columns in the RETL Model and/or Mapping may lead to misalignment of existing data, as Segment does not retroactively adjust previously synced data. For updates involving column modifications, Segment recommends starting with a new Sheet to ensure data integrity.
This page was last modified: 27 Aug 2024
Need support?
Questions? Problems? Need more info? Contact Segment Support for assistance!