Plot query results in Google Sheets

What you will build in this guide

time required: 3 minutes

How quickly is the city of Miami growing? Questions like these solicit an exploration of a topic rather than a single answer. Querying the neumark/city-of-miami repository gives us the raw data, which we can chart in Google Sheets using the freely available sheets extension.

How it works

The Splitgraph Google Sheets extension adds an sgr() function which expects an SQL query to run in the DDN.

To learn more about the extension, watch the short introduction video.

Example sheet

The New construction sheet in the demo doc runs a query on Splitgraph and displays the results as a chart. It really is as simple as passing the query to the sgr() function.

There are two more sheets in the document:

• Construction permits by type: Showcases the use of "data validation" to present a dropdown menu of construction permit types.
• Selecting permit types: Demonstrates the use of checkboxes to plot lines for multiple permit types in a single chart.

Step by step instructions

1. Open the demo.
2. Make your own copy of the doc (File menu → Make a copy).
3. Install the Splitgraph extension.
4. Reload Google Sheets in the browser

If you get stuck, watch the screen recording:

Gotchas

The cells returned by the sgr() function don't always have the type needed for creating charts. The timestamps are converted to dates in column C, while column D contains the issued permit counts as numbers instead of strings (as originally returned).

Conclusion

Spreadsheets have eaten the world! They're one of the most convenient ways to share data analysis, but they're not great for storing lots of data. Splitgraph gives you the best of both worlds: store your data in a database, query it using standard SQL, and work with the results in Sheets! Using dropdown menus and checkboxes we were even able to build a simple dashboard without ever leaving our spreadsheet.