The easy way to replicate Google Sheets to your data warehouse using Stitch

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. You could use Talend Cloud Integration, but you don’t need to do complex transformations and you don’t want to spend a lot of time. What about Stitch Data Loader? Stitch is a great ELT platform, able to move millions of rows from more than 100 data sources with just a few mouse clicks, but it doesn’t provide a native integration for Google Sheets. But Stitch does have an Import API that facilitates all kinds of data ingestion, and that makes this an easy problem to solve.

The solution involves a Google Apps Script that grabs all the data from a sheet and sends it to Stitch’s Import API. Stitch then loads the data to Amazon Redshift, Google BigQuery, Snowflake, Microsoft Azure SQL Data Warehouse, PostgreSQL, or any of the other destinations Stitch supports. Let’s walk through the process step by step.

Prep your Google Sheet

The first task is to 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.

  1. Copy the script to your clipboard.
  2. Open the spreadsheet you’d like to push to Stitch. Click Tools > Script Editor.
  3. Delete the barebones contents of the file that Google Sheets creates and replace it with the contents of the script. Save it.
  4. 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.
  5. 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.

Generate an API token

The script will prompt you for your Stitch API token. Sign up for Stitch: It’s a self-serve SaaS platform, nothing to download and no credit card required. You can then generate an API token by setting up an Import API integration within your Stitch account.

Generate Stitch API token 


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.

Generate Stitch API token 


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.”

Generate Stitch API token


Sync and swim

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 `{“status”:”ERROR”,”error”:”Not Authorized”,”errors”:null}`, that means that either the token or the client ID you entered is incorrect or the client isn’t authorized for the specified integration.

The Google Sheets integration is freely available on Stitch’s GitHub repository.


Join The Conversation


Leave a Reply