Fun with Google Apps scripts. (Yes, you read that right)

One of our non-technical staff drank the Google Apps Kool-Aid. In this blog post, Tom Grady shares what he learned when he decided to get automated.

---

I have come to the conclusion that I must be fundamentally lazy. Why?

Because I recently learned about Google Apps Scripts and have become a little bit obsessed with using them to automate everything in my life. Without openly admitting it to myself or my boss, I think I'm aiming to get to a point where I come to work, set all my scripts going and then wander off to drink cups of tea.

Ground Control to Major Tom by Chris Devers. Reproduced under a Creative Commons licence

So what exactly have I done? Well, we're hardly talking SkyNet taking over just yet, but I have managed to write some scripts that do the following:

• Collect the most recent stats for my team from several Google Spreadsheets, showing how hard we've worked and what's on the horizon for this week
• Put those figures into a pretty table so you can compare last week with this week at a glance
• Automatically email my boss with them every Monday morning at 10am*

*(I suspect she has an inbox rule that sends them straight to Deleted Items, thereby neatly closing a circular loop of automation, but anyway...)

I thought I'd share with you how I did it, in case anyone else out there is as lazy as me.

First of all, let me be clear: as an English Lit graduate I knew I wasn't going to be able to merrily start writing code from scratch. So I did what everyone does: I went to see The Oracles. They're not on a distant mountain top, they're in the Teaching and Learning team in IT Services in the Fairhurst Building. After an hour's overview with Mike Dunn and Tom 'Major Tom' Smith they let me loose, and over the course of a week I managed to cobble together a script that I thought might do the job.

It didn't work.

And then I learned the most important lesson of all: if you don't know what to do, ask StackOverflow and a kind soul somewhere in the world will help. If you follow their no-nonsense rules for asking questions ("This site is all about getting answers. It's not a discussion forum. There's no chit-chat.") you actually get some great ideas and, if you're lucky, they'll even throw you some code to take away. For free. And they don't even like it when you say thanks.

My code was copying stats from the spreadsheets OK but it wasn't copying the most recent cells; I could only get it to find fixed rows and columns and this is no good if you update your stats every day. Here's a snippet of what someone suggested.

if (origin.getName() == "Sheet1") {
    var r = origin.getActiveCell();
    var row = r.getRow();
    var numberOfCol = 3;
    if( row > 1) {
      var numberOfRows = row >= 7 ? 6 : row - 1;
      var startRow = row >= 7 ? row - numberOfRows + 1 : 2;
      range = origin.getRange(startRow, 3, numberOfRows, numberOfCol);
      range.copyValuesToRange(destination, 1, numberOfCol, 1, numberOfRows);
    };

On every edit event in Sheet1 this copies the bottom six cells from Sheet1 to Sheet2. I jiggled this into my script and hey presto! It worked. The final piece of the puzzle was to have an old-fashioned formula in another Google Sheet grab the stats from Sheet2 and format them nicely. Then I wrote another script that is triggered at 10am every Monday morning and uses the MailApp.sendEmail function to send the stats.

Apart from being absurdly and unreasonably proud of learning this from scratch, I think this is worth sharing because it's a good example of something that not long ago would have required input from a real developer. I would have had to persuade someone important that it was worth spending time and money on doing, and I'd have have failed because it really wasn't a pressing problem that needed solving. Fiddling with Google Apps scripting liberated me from so much red tape and allowed me to just go off and do it, without much fuss and without much training.

Next I'm working on a script that will churn out blog posts ...