Google Sheets

Setup

To add a Google Sheets data source to Cast you first need to create a Service Account with Google. Service Accounts allow third-party applications like Redash to read data from your Google apps without needing to log-in each time. During Service Account setup you will be provided with a JSON key file. You need to upload this file to Redash when setting up the data source.

How to create a Google Service Account?

  1. Open the API Credentials Page. If prompted, select or create a project.
  2. Click the “Create credentials” button. On the dropdown that appears, chose “Service account key”.
  3. On the following page, use the dropdown to select the project you elected in step 1. For role select Project > Viewer from the tree menu.
  4. Under key type, select JSON and hit “Create”

A .json file will then download to your computer. In Cast under Datasource, add a new data source for GoogleSpreadsheet. In the modal that appears, name this connection and upload the .json file you downloaded from the Google credentials console.\

Querying

Once you have set up the data source, you can load spreadsheets into Cast. To do so, you need to share the spreadsheet with the Service Account’s email address. This can be found in the Google Sheets API credentials page or in the JSON file under the "client_email" key. Sharing is done as you would share with any regular user.

After the spreadsheet is shared with your Service Account email address, create a new query in Cast and select your Google Sheets data source. In the query editor text box, type your desired Spreadsheet ID. You can optionally select a specific tab of your spreadsheet by adding its tab position as a zero-indexed number separated by a vertical bar or pipe symbol.

For example:

DFuuOMFzNoFQ5EKJUE1zB79-0uR6zVKvc0EikmvnDgk|0

to load the first sheet or

DFuuOMFzNoFQ5EKJUE1zB79-0uR6zVKvc0EikmvnDgk|1

to load the second. That’s the whole query. Leave out any SQL at this point.

What is the Spreadsheet ID?

You can find your Spreadsheet ID in its URL. So if the spreadsheet URL is:

https://docs.google.com/spreadsheets/d/ b94d27b9934d3e04a62e5ad9da0mabfac484efe37

Then the ID will be:

b94d27b9934d3e04a62e5ad9da0mabfac484efe37

This procedure might fail if your organization has restrictions on sharing spreadsheets with external accounts. To improve outcomes, be sure to create the Service Account with a Google account from the same organization.

A Note About Dates

Redash uses Python-datutil to parse dates from Google Spreadsheets. If you experience issues where Redash parses the date incorrectly, try adjusting the date formatting in your sheet to ISO8601 or one of the formats shown here.