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.