I’m always looking for opportunities to not code. On a new project, we’ve been using Zapier to extract data from a source and store it in google sheets. Then we manually categorize the data. One of our challenges on this project is presenting the right amount of actionable data to the customer.

After hacking our way to to accumulate the data using zaps, I had planned to build (code) a dashboard. But the dashboard will change many times as we iterate. So, I went looking for alternatives. I discovered that google sheets has an amazing range of functions that when combined can be useful as an MVP (minimum viable product).

Here’s an example of a thin MVP built using Google Sheets.

The example is classroom demographics tool, using this dataset. The example contains a dashboard with charts and tab for displaying all data. The charts were generated within google sheets on a single sheet. Both this sheet and an  additional sheet were published to the web using google’s Publish A Document feature.

Contained on the dashboard is the capability to “drill down” into datasets. In this demo, there is a chart displaying the ‘home states’ of the students. I link this chart to relevant datasets using google sheet’s query language.

Google allows you to query a spreadsheet as you would any database and return the result as html. For example, in ‘Student Home State’ chart, I create a link for each row, using the hyperlink function and a query as the link.

For example, to drill down and see the students whose home state is ‘NY’, I hyperlink the text ‘NY’ and link it to “https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/gviz/tq?tqx=out:html&tq=select+A,+B,+C,+D,+E,+F+where+D=%27NY%27&gid=0”

This link is generated using the link to your spreadsheet as a base. My link is https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/edit#gid=0. I remove the “/edit#gid=0” and replace it with “/gviz/tq?tgx=out:html”. Which gives becomes “https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/gviz/tq?tqx=out:html”

I then add a SELECT query to the url. If you are unfamiliar with SELECT queries go here for a basic overview. Be mindful that google allows a limited set of clauses and all clauses should be encoded. (google has a tool to help you encode the query) In this case, I want to show all columns to for when the column home state is equal to ‘NY’. My query looks like: SELECT A, B, C, D, E, F WHERE D=’NY’

After encoding, my query I add it onto my url. I also add back on gid which tells what sheer to use. My resulting url is ‘https://docs.google.com/a/playpenlabs.com/spreadsheets/d/1PeK8vnm42-FM8fvhJWlZF2N8Y-XEUyL90timA_WG2J0/gviz/tq?tqx=out:html&tq=select+A,+B,+C,+D,+E,+F+where+D=%27NY%27&gid=0‘

Another cool thing about this google sheet mvp is that authentication is already baked in. The published html is limited to the permissions set on the original spreadsheet.

This is a pretty rough hack and can be used in cases where visual design faux pas are forgivable because the value provided is greater.