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.
- 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.
- 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.
Info
The Google Sheets destination only supports sending new or updated rows to your spreadsheet. Deleting rows is not supported.
Segment's v2 Action, Post Sheet v2, supports Sync modes, which allow you to select a strategy for updating your data in Google Sheets.
Sync modes allow users to define how Segment updates the data in your destination.
Available sync modes for the Post Sheet v2 Action includes:
- Update: Update a record if a match with the specified identifier is found. Segment does nothing if the row doesn't exist.
- Upsert: If a record with the specified identifier is found, it is updated. If not, a new row is created.
- Add: Add a new record when the specified identifier doesn't exist. If it does, the record is skipped.
Build your own Mappings. Combine supported triggers with the following Google Sheets-supported actions:
Mapping limits per destination
Write values to a Google Sheets spreadsheet.
Post Sheet is a Cloud action. The default Trigger is event = "updated" or event = "new"
Property which uniquely identifies each row in the spreadsheet.
The 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
The 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.
The 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.
The 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.
Optional
Set as true to ensure Segment sends data to Google Sheets in batches. Please do not set to false.
Write values to a Google Sheets spreadsheet.
Post Sheet V2 is a Cloud action. The default Trigger is event = "updated" or event = "new"
Property which uniquely identifies each row in the spreadsheet.
The 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
The 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.
The 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.
The 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.
Optional
Set as true to ensure Segment sends data to Google Sheets in batches. Please do not set to false.
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.
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.
When syncing data to Google Sheets, the columns will be arranged alphabetically, based on the names defined in the Fields mapping.
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.
You can't send JavaScript objects as they're not a supported data type in Google Sheets. You need to stringify the property first. Failure to do so results in a 400 error. Segment's Actions mapping framework supports encoding objects as strings through the json(properties, encode) method. Alternatively, you can use an Insert Function to modify the property.