Google Sheet Sync

How to sync a table to a Google Sheet destination (reverse ETL)

Syncing a table to a Google Sheet

1. Select the table you wish to sync from the Warehouse page (homepage in Mozart Data). 

2. Select the "Sheet Sync" tab

3. Select "Add Destination" and you should see the following form:

4. In a new tab or window in your browser, open the Google spreadsheet you want to sync to.

5. Share the Google sheet with google-sheet-syncer@mariodata.iam.gserviceaccount.com as an editor

share

set_as_editor

6. Copy the entire spreadsheet url into the form.

spreadsheet_url

7. Click "Test Connection" and you should see the following form if successful:

8. Enter the name of the tab that you want the table to sync to. If it doesn't exist, the tab will be created. If it is an existing tab, we will overwrite it. tab_name

Specify columns to order by

By default, Snowflake does store a sort order for the data, so it may appear to be in random order when synced to Google Sheets (this is because when syncing we use SELECT * on that table). 

If you want the data to be consistently ordered, specify the column(s) you want to order by just as you would in a SQL query.

This can be done when adding a Google Sheet destination or by editing an existing destination.

 

How to best use your synced data

We recommend not making any edits to the tab that your table syncs to. This is because every sync will overwrite the tab - any additional work or formatting on that tab will be lost. You can create a new tab that pulls data from your synced tab, then perform analyses and create charts from the second tab.

Troubleshooting Google Sheet Sync

If the table no longer contains your order by column(s):

The Google Sheet sync will break and the data will not be synced to the sheet.

You will receive an email notification about this error. 

Solution: 

  • remove the ORDER BY column(s) by editing the Google sheet destination, OR
  • edit your SQL to ensure that the columns you want are present in the table (don't forget to run your transform to make sure the table in Snowflake has the columns)

If you or someone else deleted your Google sheet: 

The Google Sheet sync will break and you will receive an email notification.

Solution:

  • remove the Google Sheet from your trash if it hasn't been permanently deleted (so long as it has the same id this should restore the sync), OR
  • delete the Google Sheet destination in the Mozart Data table and create a new one

If you or someone else revoked Mozart Data's editor permissions to the sheet: 

The Google Sheet sync will break and you will receive an email notification.

Solution:

  • Share the Google sheet with google-sheet-syncer@mariodata.iam.gserviceaccount.com as an editor again

If you hit the cell limit on a Google Sheet 

There is a cell limit for each spreadsheet (not for an individual tab but the sheet as a whole). The sync will not take place if your table would exceed this limit.

You will receive an email notification for this error.

Solution:

  • If you are syncing tables to multiple tabs in the same Google Sheet, try splitting up your syncs. Create a separate destination sheet for each table.
  • If this table is one of your transforms, you may want to create another transform that references the first table and selects only the most relevant data columns
  • If this table is one of your transforms, you may want to create another transform that uses ORDER BY and LIMIT to capture only the most recent or most relevant data