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.