Home » Querying Google spreadsheets through SQL

Beto Dealmeida's avatar

Querying Google spreadsheets through SQL

I wrote a Python library to query Google spreadsheets using SQL

Some time ago I wrote a Python library called gsheetsdb[archived] that allows users to query Google spreadsheets using SQL. You can reference spreadsheets as if they were tables, and write queries like this one:

SELECT *
FROM "https://docs.google.com/spreadsheets/d/1_rN3lm0R_bU3NemO0s9pbFkY5LQPcuy1pscv8ZXPtg8/"

The library translates the SQL into the small subset of SQL supported by Google Charts[archived]. There's a lot of magic happening behind the scenes — even something as simple as a SELECT * is not supported — but it works really well. It tries to aggregate as much data as possible on the server, but if it can't it will simply download the whole spreadsheet and run the query against an in-memory SQLite table.

I wrote the library to integrate Apache Superset[archived] with spreadsheets, since a lot of people like creating spreadsheets for small datasets that need to updated manually. Preset.io[archived], a company that sponsors and develops Superset, wrote a nice blog post[archived] explaining how to use it to generate charts and dashboards. Here's an example from their post showing a bar chart:

If you ever need to access data from a Google spreadsheet programmatically check it out, it's easy to use and integrates with the Python ecosystem, also working with SQLAlchemy. And if you need a BI tool to visualize data, write SQL and create dashboards, it's worth taking a look at Superset.

Comments

You can engage with this post on Mastodon, Medium, Twitter, Webmention or WT.Social.

João S. O. Bueno's avatar
João S. O. Bueno replied on Twitter on 2020-06-07:

@dealmeida that sounds great - I always thought about google sheets as a fantastic UI for data flows - I just had… https://t.co/jRD1qpkUtR