Google Docs scripting funPosted on: May 15, 2012

So at work, we recently had a google doc shared around that everyone was supposed to collaborate on to put down your name, and what you’re the 'head' of. One of my colleagues (let’s call him John) was unfortunate enough to fall victim to me playing a little prank with some Google Docs scripting. You see, he has an annoying tendency to play really crappy music in the office (Rolf Harris hour anyone?) so here’s what I did and how I pulled it off...

The Doc basically had three columns: 'Name', 'Title', and 'Description', so what I wanted to do was find the row with his name in it, and then add ' & crap music' to his title (so his title would read 'Head of foo & crap music', for example). Below is the code I eventually cobbled together.

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Loop through the rows
  for (var r = 1; r <= sheet.getLastRow(); r++) {
    var name = sheet.getRange(r, 1, 1, 1);
    var title = sheet.getRange(r, 2, 1, 1);
    // Does the `name` cell contain his name? If so, does the title field have a value and does it already contain the desired text?
    if (
      name.getValue().match(/John/i) &&
      title.getValue() !== "" &&
      title.getValue().indexOf("crap music") === -1
    ) {
      // Seemingly, he isn't head of crap music yet. Make it so!
      title.setValue(title.getValue() + " & crap music");
    }
  }
}

Now in your Google Doc, go to: Tools > Script Manager, and click on the 'New' button, paste the code in and hit 'save' then give it a relatively inconspicuous name. Return to the Google Doc and open the Script Manager again, then select your new script and hit 'Run' (I'm not 100% sure this last step is strictly necessary). There you have it... now every time he tries to fix his title, it will tack ' & crap music' to the end.

Always remember... prank responsibly!