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 – 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 Sheets – ArrayFormula with Header/s

The array formula is a wonderful thing in Google Sheets, in most cases providing the opportunity to enter just the one formula and have it automatically fill down as new entries appear from elsewhere.

A clever trick though is to create an array within an array, which allows for header rows to be included.

Continue reading

Google Sheets – Google Apps Script – One to One Meeting Tracker

I was presented with a word document last week with a table full of names of staff and dates of their one to one meetings (who uses spreadsheets for managing this sort of thing ;)), ….”Can you make it so that an email gets sent to the supervisors when it is time for them to hold another one to one meeting with their staff?”

“Yes, of course.”

First job was to grab the data and convert it into a manageable list. Ended up with 8 columns: Name, Supervisor, Job Role, Department, Date of Meeting, Completed, Notes/Comments, date for Next Meeting. The notes were to allow for entries of reasons why the meeting could not take place, e.g. maternity leave or long term sick. There was all sorts of rubbish written in the table that wasn’t a date, people who had left and started during the year, which all had to be cleared out. Eventually got a neat set of data to play with. Continue reading

Google Sheets – Using Query to Find a Value in any Column

The saga continues on from this and this to find a workable solution to find a value from any column using query. I happened upon another route (see accepted answer), which was to use a spreadsheet function to generate a csv list of columns which could then be applied to the select query. Credits as always to the original poster! I then needed to modify the substitutions to create more of the select query, and found I could build the whole query syntax in two cells then apply these to the query function. I was so chuffed with myself i made up a proper spreadsheet layout with comments and made a video of it for demonstration purposes. Continue reading