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