Google Visualization Table with Modal on Selection

Couldn’t find anything on this, other than a post about angular.js, so had a go at displaying a modal popup after making a row selection on a google visualization table. I had already got to grips with creating modals and used the code provided by W3-Modal.css, so I simply had to put the pieces together. I used the spreadsheet query method for generating data, if done using a google apps script, it is possible (see other posts) to keep the spreadsheet private. I also used the W3.css file for formatting and layout.

The html page created to do all of this is well commented and you can see the results using the demo after the code. Continue reading

Google Visualization – Gauges

This is fun, a simple couple of gauges linked together, one showing revolutions per minute (rpm) the other showing speed, based upon 5th gear in a L322 Range Rover! I guess with more time than I have got it would be possible to code for the entire gearbox 😉

gauges

Continue reading

Google Visualization – Basic Slideshow from Google Sheet

Here is the code for a simple automatic slideshow using google visualizations

Example Slideshow

Google Visualization – HTML Table Layouts

In order to get a decent layout for charts and dashboards I found it helps to use html tables to create the base layout. Below are two, one with controls across the top, and the second with controls down the side. The tables are reasonably responsive to screen/window sizing….

Top Layout

html

Example Top Filter Table

Side Layout

html

Example Side Filter Table

For these to work with your visualizations, simply insert divs with ids to each of the cells where you need them

Google Visualization – Dependent Controls / Filters

When building a chart with controls (using a dashboard), you very often want to filter, then filter again. Lets say we have a people directory, with names, locations, and countries. You create a CategoryFilter for each of these. But it would make sense to filter by country, then be left with only those in that country, before filtering by location. This is how you do it.

Set up your filters:

Set up the table

Now we need to do the binding of the controls and the table:

You can see that we bind the country to the location, then the location to the people, then the people to the table. In this way, the filters become dependent on each other. In the simple demo below, you should see how – when a country is selected only the locations for that country show in the Location drop down.

Here is a full demo of this in operation:

Demo Open in Browser

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.
Continue reading

Google Visualization – CategoryFilter with csvFilter

As mentioned in a previous post, I have been working on generating a replica of the csvFilter option as found in a well known online visualizer for google sheets. In this post I demonstrated how to create a unique list of items that could be used in the control, and now can show the working example.
Continue reading

GAS – getLastRow() when sheet has Array Formula

The benefits of using array formulas in your spreadsheets are manifold, but when you come to grabbing the data held on those sheets for scripting you are presented with an issue: using the getDataRange().getValues function provided in GAS will return the entire sheet, in terms of rows, and therefore a whole load of unnecessary rows shown in your google visualization table or arrays. Continue reading

GAS – Create a Unique list from Cell based csv type Values – two ways

In my endeavours to move away from what might be a costly exercise in providing tables and charts for my users at work, I am delving heavily into google visualisations as a replacement. One feature I have yet to replicate is the “csvFilter”, whereby a column of cells in a table contains more than one value, separated by commas:

csvlist Continue reading