Back to documentation

Google Sheets + SQL: Querying Google Sheets with DataStation

Requires DataStation 0.10.0+.

In this tutorial we'll load a CSV of population data into Google Sheets, set up the permissions to be able to read this query from the Sheets API, pull the Sheet data into DataStation, and run SQL queries on the data using DataStation's builtin SQL engine.

Database initialization [Optional]

If you want to follow along with this tutorial verbatim, create a new sheet and import the CSV of world population data you can get from this page.

World population Google sheet

Google Service Account

Enable the Sheets API.

Enable Sheets API

If you don't already have a Google Service Account, make one.

New Google service account

Then download the service account JSON from the Keys page of the service account.

Download service account JSON

Finally, share the sheet you just created with the service account email you just created.

Share Sheet with service account

Data source setup

Now inside DataStation create a new Google Sheets data source in the left sidebar.

Creating a new data source

Give it a nice name so you can easily find it later.

Creating a Google Sheets data source

Copy the service account JSON file contents you just downloaded into the service account JSON field.

Filled out Google Sheets data source

Panel setup

Copy the sheet ID from the URL of the Google Sheet.

Copy sheet ID

Now create a new Database panel and enter the ID.

Create Google Sheets database panel

And run the panel to fetch the data!

Run Google Sheets panel

SQL query on top

Now that the data is loaded in DataStation we can use DataStation's scripting abilities to run a program using the Google Sheets data. We'll run a SQL program that counts the number of applied applicants.

Let's build a graph of world population over time. Since the population by year is given in columns we can write a few UNION queries to get the total population at each recorded decade:

SELECT SUM(pop1970) AS population, '1970' AS decade FROM DM_getPanel('Grab population sheet')
UNION
SELECT SUM(pop1980) AS population, '1980' AS decade FROM DM_getPanel('Grab population sheet')
UNION
SELECT SUM(pop1990) AS population, '1990' AS decade FROM DM_getPanel('Grab population sheet')
UNION
SELECT SUM(pop2000) AS population, '2000' AS decade FROM DM_getPanel('Grab population sheet')
UNION
SELECT SUM(pop2010) AS population, '2010' AS decade FROM DM_getPanel('Grab population sheet')
UNION
SELECT SUM(pop2020) AS population, '2020' AS decade FROM DM_getPanel('Grab population sheet')
UNION
SELECT SUM(pop2030) AS population, '2030' AS decade FROM DM_getPanel('Grab population sheet')

Create a new Code panel, select SQL as the language, enter this query and hit run.

SQL on top of Google Sheets

Graph the results

After running the query, a graph button will appear below the panel next to the New Panel button. Click it to generate a graph based on this panel.

Graph results

From here you can do more analysis or export results to clipboard, a file, HTTP server, etc.

About this page

See an error or want to add a clarification? This page is generated from this file on Github.

Last edited Jun 07, 2022.