Skip to main content

Building a Dashboard with Google Apps

The other day I went for a chat with reluctant blogger Paul Bushnell in Estates ( pictured right) to find out what they've been doing with Google Apps. The answer is lots, almost too much to fit into one blog post, but I'll have a go.

Estates have created a system for working with the numerous fix requests they get, from broken toilets to fire alarm faults to potholes and people stuck in lifts.

1. Data Logging with Android Tablets

The team in Estates all have tablets with which they can get access to all the recent reports and log when problems have been dealt with and add any notes. For this they mainly just use simple spreadsheet data entry, rather than Forms or fancy user interfaces.

2. Workflow System 

Once a problem has been dealt with, it gets moved from one sheet, onto the next. From here, the job might get logged as "we need to make sure this never happens again" and moved into other sheets. 

3. Working With External Suppliers

In each of the spreadsheets, when a job was logged as needing parts from an external supplier, the parts were ordered. Interestingly, rather than slow down the fix time, external suppliers were given access to this part of the spreadsheet and could add notes along the lines of "we can get two of these to you by Friday". An interesting part of this is that the urgency for supplying the part is handed to the supplier, first come, first sale rather than having to order it from their system... genius.

4. Automatic Email Reports

Throughout this whole process/system, automatic email reports were sent, with real data - sometimes even chasing up people who needed a reminder. Often lots of people get these personalised mails. 

This sort of mail merging is something we think lots of people want to do at the University ( with Google Apps ) and it is quite simple. We both thought it'd be a good idea to share this Spreadsheet/code as a starting point for other people to make their own mail merge systems. 

4. KPIs For Every Spreadsheet

Of the many spreadsheets I saw, each had an "Intelligence" sheet. This was sometimes an aggregation of the data, sometimes just one number, for example, "How many toilets were fixed this month" or a trend item. The bit I found interesting was that for every spreadsheet, or clump of data there was a KPI sheet... the measurable had been thought about.

5. The Dashboard Itself

There was more than one dashboard actually, but they tended to look like this... And in it, Paul could see that Estates' fault fixing effectiveness was definitely improving over the last six months.

And the really amazing thing about this, is that, at its heart, it is just a collection of Google Spreadsheets with some timed triggers that move data around.

I've also been helping them to import and integrate automatically generated CSV data from their Fire Alarm system. I also find this interesting, not only because as you start being able to mix automated data with human-data ( if you know what I mean ) new things start being possible.

And on top of all this, this work by Estates isn't happening in some technologically arcane corner somewhere, BECAUSE it's in the cloud, using a codebase and hosting solution all of us have access to, potentially, some of the ideas or solutions in here could wind up being used by other departments. 

The only hard part is working out how and where to share this expertise well

There is, I think, a sweet spot in sharing code ( or spreadsheets or AppsScript stuff ) where it is complex enough to do the job properly (enough), but not so developed that the end solution is fixed and it can still be hacked. 

Often I find beautiful code, that works so well I have no idea how it is doing what it is doing, it is like magic, whereas what I want is something that I can look at, understand the components, take apart and put them back together in a new shape. 

So, I propose that, working with Paul, I will "take parts of his code" and share it here, breaking it down into smaller pieces that you may find useful. I also have a number of other projects I'm currently working on I could do the same with. I will try to resist the temptation to overwork them and share my work in progress here. 

I'll also keep badgering Paul ( and others ) to give this blogging thing a whirl. 


  1. Looks fantastic... a demo would be great.. perhaps at the next dev meeting.

  2. Amazing. Would love to see few examples as you mentioned. Never knew something like this was possible.

  3. Your post is awesome. You have explained it very well. I am willing to read posts like this one. Thanks for posting...
    Top home alarm systems

  4. Many thanks quite a lot for giving this specific operating all of us you probably know what you might be preaching about! Cool Work
    dynamic dashboard


Post a Comment

Popular posts from this blog

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems. A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it. The QR app looks like this. The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this. The Web Application The web application, which only checks items in or out and should be used on a phone in conjunctio

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

This article looks at using Apps Script to add new features to a Google Spreadsheet. At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form. Spreadsheets can be better than Forms for data collection because: The spreadsheet data saves as you are editing. If you want to fill in half the data and come back later, your data will still be there. The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary The commenting features are brilliant - especially the "Resolve" button in comments. One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often

A Working Booking System In Google Sheets

Working with Andras Sztrokay we had another go at a booking system. This time it was to enable staff to book out a number of iPads over a number of days. You select the days you want, then select the Booking menu. Andras did an amazing job. It even creates a daily bookings sheet so you can see who has which iPads. To see this in action, go  here  and  File > Make a Copy (I won't be able to support you this is just provided to maybe give someone else a leg up, good luck!)