How to replicate Google Sheets to your data warehouse

Suppose you have data in Google Sheets that you want to bring into your data warehouse to join up to other data for better BI. Stitch doesn't yet provide a native integration for Google Sheets, but it's not hard to move that data, thanks to a Google Sheets integration coded by Stitch Director of Sales Shaun McAvinney.

Breaking news: Stitch now offers a Google Sheets integration! In the Stitch dashboard, choose Google Sheets and ask to be added to the public beta. Or you can keep reading.

Shaun's integration uses a Google Apps Script to grab all the data from a sheet and send it to Stitch's Import API. Stitch then loads the data to the destination you specify – Amazon Redshift, Google BigQuery, Snowflake, PostgreSQL, or any of the other destinations Stitch supports.

To use the integration, first prep your sheet. The name of the tab on which your data lives will be used as the table name at the destination. The first row should hold the column names, and those names must not include any spaces. One or more of your columns should serve as a key, whose values must be unique for all rows of the table. Make sure you have no blank data in any of columns you plan to use as part of your key. If you have multiple columns that make up a unique key, you can enter them separated by commas, or hash them together in a new column using the Google Sheets md5() function, as described in the script's documentation.

Copy the sheetstostitch.gs script to your clipboard.

Open the spreadsheet you'd like to push to Stitch. Click Tools > Script Editor.

Delete the barebones contents of the code.gs file that Google Sheets creates and replace it with the contents of the sheetstostitch.gs script. Save it.

Go back to the spreadsheet and refresh the page. If you don't see a new Stitch Import menu item at the rightmost end of the toolbar, press Shift-Refresh to reload the page.

Click Stitch Import > Set Up Spreadsheet for Push. To authorize the script to access the data, log in when prompted with the Google account that has access to this sheet.

The script will prompt you for your Stitch API token. You can generate an API token from an Import API integration within your Stitch account.

Note the part of the prompt that says "Press cancel if no change." The script stores the values you enter, so if you want to run it multiple times, you don't have to enter the values each time.

The next prompt asks for your Stitch client ID. It's the six-digit number you can find in the URL while you're logged into Stitch.

Next, specify the column(s) that act as the primary key for your table. Use the column name, not its letter – so if the primary key for your table is in column A, which is labeled "clientID" in cell A1, enter "clientID."

That's the final prompt. Now go back to Stitch Import on the menu bar and choose Sync with Stitch. This time the script sends your data to the Import API integration associated with the token you entered.

Visit the integration page in Stitch and reload it to get the current status. Stitch should replicate the data in your spreadsheet to your destination within minutes.

When you check the integration, it may be listed as pending, or you may see the number of rows loaded. If you don't, you can check the script's log by going to the spreadsheet and choosing Tools > Script editor > View > Logs. If you see an error like, that means that either the token or the client ID you entered is incorrect or the client isn't authorized for the specified integration.

While the Google Sheets integration is freely available, it's not a Stitch-certified integration. If you'd like to enhance it, you can visit the script's GitHub repository and submit a pull request.