Google Visualization – Pick and Choose…

It seems much easier to use the query method to pull in data for visualizations, can be run straight from any old html page (can also be run from a google apps script web app), number formats remain intact (e.g. dates), and using the query language allows to select just the data you need. However, at present, there is one flaw, even for G-Suite users, this method requires that the underlying spreadsheet is shared as “Anyone with the Link”. Now I know it would take 4 x the life of the Earth to crack a google Sheet ID, but in these days of data protection (GDPR) in the workplace one cannot accept this reduced level of security. Fortunately, there is another way, ones spreadsheet can remain private, and data can be visualized and presented in a secure way. This uses a standalone Google Apps Script web app (can be bound), and can be restricted to a G-Suite domain. Downsides are the extra work required to manage dates (these all come through as numbers, which need turning back into dates!), and it has to be done in a web app.

Here is an example of each one, starting with the query method, I will use a simple table visualization.

Open in Browser

And here is the web app method

The spreadsheet being called has an array formula in it so there is additional code to strip out the empty rows, as covered here


Open in Browser
(on first run you will need to “TRUST APP”) – if you don’t want to be assured it looks much the same as the previous one :)

Because we can’t query the columns we need, we have to create a DataView of the data, in order to select the columns we want to show.

So, there we are two ways to do the same thing, but one allows you to keep your data/spreadsheet private (albeit exposing your google account address in the process!)

Leave a Reply

Your email address will not be published. Required fields are marked *