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.

Google Service Account

Enable the Sheets API.

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

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

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

Data source setup

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

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

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

Panel setup

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

Now create a new Database panel and enter the ID.

And run the panel to fetch the data!

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.

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.

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