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

Where do good ideas come from?

Tom Smith stumbles on a good idea...

---

Many people think ideas happen in a flash, a moment of inspiration, that eureka moment. They also believe that it's often "other people" that have ideas, either a boss or lone genius hunting down those elusive light bulbs.

The funny thing is, that the reality is, ideas can be slow things, taking time to come into being and most often they happen in discussion or collaboration. From what I've seen, the best ideas don't come from senior management, or from a sole genius but from people working together, people actually doing stuff in the real world. Many of these "good ideas" can be almost accidental.

My role at the university has been to both introduce people to the Google Apps suite, both evangelising them and working with people to help them realise their ideas.

Here's a story of an "accidental idea" that I think is good and is a great example of what I call "people actually doing stuff" and collaboration and discussion.

Someone in Facilities in the Library, let's call him Andrew (because that's his name), was talking to someone in the Communications Team, we can call her Jess because that's her name too. They came to me, Tom, and asked, "Can we have a spreadsheet to make the recording of seat availability in the Library easier?"

I didn't know that this information was collected. I showed them how to add drop downs and colours to a Google Spreadsheet. Job done.

I then discovered that someone actually regularly patrols the building and carries an Android tablet. I had the thought that instead of it being a spreadsheet, which was OK but not ideal, it could be an app, better designed for updating on the move. It took me about half an hour to make an app that did that for them, updating the same spreadsheet, using Apps Script. Apps Script is a coding language based on JavaScript built into Google Spreadsheets, Sites and Docs and it's a fantastic tool for people who want to quickly make their ideas happen.

The story doesn't end here. Previously, I'd shared an office with Aimee, who'd discovered that with a little HTML hacking, we could show some Google data on the York website. And so Jess added the seat availability page to our web site as a trial service. An accidental feature of the technology means that this chart is updated every 5 minutes, rather than every few hours like the web site.

And the story goes on. By now, only days after launching a trial service, the Communications Team were already receiving positive feedback from students.

Next, Jess and Steve, seeing that the data could be displayed on the web site, asked if it could also be displayed on the big screens around Harry Fairhust. We found that the original spreadsheet looked a "bit lost" on the big screens, so wrote some code to "push" the data from the main spreadsheet to another sheet formatted with bigger fonts etc (shown below).

function copy_sheet(){
  //Simply makes a copy of the main sheet
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  
  var sheet = ss.getSheetByName("Seating availability")
  var range = sheet.getDataRange()
  var a1 = range.getA1Notation()
  var values = range.getValues()
  
  var desination_spreadsheet = SpreadsheetApp.openById('nu7JgRtB5CdHJUVlNaRzUyd_pmUWc')
  var destination_sheet = desination_spreadsheet.getSheetByName("Big Screen")
  var destination_range = destination_sheet.getRange(a1)
  destination_range.setValues( values )
  
  }

The trial Seating Availability service came into being after chance questions and discussions. It took little to no time to put together. It uses Google Spreadsheets, an app, an android tablet, a widget embedded in a web page and our big screens. It involved at least four or five people. We will soon be adding extra areas and improving it based on feedback.

This is already hugely popular for all sorts of reasons - not just the ones you expect; I heard from someone I was working with in Disability Services that students with problems with anxiety love being able to avoid "nearly full" study spaces.

Still wondering where good ideas come from?

---

Tom Smith leads our Collaborative Tools Project. He also has his own Everythingability blog

If you're interested in finding out more about what you can do with Google Apps, follow the Google Apps European User Group on Twitter when they meet at York on 23 & 24 July at https://twitter.com/GEUG14 or search the hashtag #GEUG14.