Querying a Google Spreadsheets Data Source

To load your spreadsheet in Redash share your ID with your Service Account’s email (it can be found in the credentials JSON file, for example, 43242343247-fjdfakljr3r2@developer.gserviceaccount.com).

Create a Service Account

    1
    Open the  Service accounts page. If prompted, select a project.
    2
    Click Create service account.
    3
    In the Create service account window, type a name for the service account, and select Furnish a New Private Key. When prompted, select JSON key file type. Then click Create.

The query format is “DOC_UUID|SHEET_NUM” (for example “kjsdfhkjh4rsEFSDFEWR232jkddsfh|0”) - this will be the equivalent of  SELECT * FROM db type of query and will show you the entire table.

What is the DOC_UUID?

It's the spreadsheet ID. You can find it in the spreadsheet URL. So for example, if the spreadsheet URL is:
https://docs.google.com/spreadsheets/d/1DFuuOMFzNoFQ5EJ2JE2zB79-0uR5zVKvc0EikmvnDgk/edit#gid=0

Then the id will be  1DFuuOMFzNoFQ5EJ2JE2zB79-0uR5zVKvc0EikmvnDgk.

To apply some manipulation on top of the data, you have two options:

    1
    Use the "Query Results" data source and your query id as a table ( SELECT ..... FROM query_123), then you'll need to query it with SQLite syntax. Read more about query results as data sources  here.
    2
    Create a new Google BigQuery table using the Google Spreadsheet in question as a source, and then use Redash’s BigQuery connector to query the spreadsheet indirectly. This way, the SQL used to query the spreadsheet (via BigQuery table) is far more flexible than the direct query of the type (“kjsdfhkjh4rsEFSDFEWR232jkddsfh|0”) mentioned above. ( BigQuery integrates with Google Drive). \

Don't forget to make sure you've shared the spreadsheet with the email address assigned to the service account you created. 

Please Note: If your organization has restrictions on sharing spreadsheets with external accounts, it might not work, but worth a try - especially if you created the service account with a Google account from the same organization.

Still need help? Contact Us Contact Us