I help with a weekly rota in our local community, where we run two parallel classes. It’s taken a year to move (pull) all the people who help out on the rota to stop sending a myriad of emails to each other, and migrate them to Google Documents.

Google Docs is such a great product. It’s centralised, and has excellent sharing and version control.

As soon as I had set up the weekly spreadsheet, I was asked whether we could send out email alert reminders to the people on the rota for the coming weekend.

I looked around at a number of tools. At one point I considered a background app which would regularly download the latest spreadsheet to my PC, parse the file in VB (I’m at my most comfortable in .net although I’ll try any language including a quick jaunt with Ruby last year) and send emails. I also considered (and spent some effort) trying to convert the spreadsheet into a series of Google Calendar appointments. However the amount of education I needed to do for our group to teach them about Google Docs put me off the idea.

I knew Google Docs has a powerful scripting language, I just needed to see if there was a way of regularly running a script. I tried searching for everything, and then came across ‘triggers’. I found that triggers, to run scripts, can be time based, and that evening I wrote the rota-notification scripts below.

The first thing, was to implement a clear rota. See below for the format. You need to call this sheet ‘Rota’.

The next task was to set up an ‘address book’ so that I could easily change people’s email addresses. I chose to put this on a second ‘tab’ (sheet) to keep it separate from the main rota. To use the code below you need to call this sheet ‘EmailContactList’.

The email lookups ended up being one of the trickier tasks, so just copy the code below.

Now for the code. Go into the Tools menu, then Script Editor, then New, and paste this lot in:

 

function sendEmails() {

  var ss1 = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss1.getSheetByName(“Rota”)

  ss1.setActiveSheet(sh1);

 

var sheet = SpreadsheetApp.getActiveSheet();

 

  // Fetch the range

  var dataRange = sheet.getRange(“A4:G50”)

 

  // Fetch values for each row in the Range.

  var data = dataRange.getValues();

  for (i in data)

  {

    var row = data[i];

    var today=new Date();

    var timecell = new Date(row[0]);

    var timediff = new Date();

    var one_day=1000*60*60*24;

       

    var daystogo = Math.ceil((timecell.getTime()-today.getTime())/(one_day));

       

    if (daystogo==2)

    {

        var subject = “Rota reminder!”;

        var emailAddress;

        var message;

       

        message = “Reminder for ” + row[1] + “!   nn” +

                  “=========================================nn” +       

                  “Hello ” + row[2] + ” and ” + row[3] + “,nn” +

                  “You are organising the Young Ones team this week. Have fun!n” +

                  “———————————————————-nn” +       

                  “Hello ” + row[5] + ” and ” + row[6] + “,nn” +

                  “You are organising the Intermediate team this week. Have fun!n” +

                  “———————————————————-nn” +       

                  “*** Remember, you can check the rota anytime by clicking this link:n” +

                  “http://google.com/url n”

       

        // Send an email to the first person

        emailAddress=getEmailFromName(row[2]) + “,” +

                      getEmailFromName(row[3]) + “,” +

                      getEmailFromName(row[5]) + “,” +

                      getEmailFromName(row[6]);

        MailApp.sendEmail(emailAddress, subject, message, {bcc:”myemailaddress@gmail.com“});

 

    }

  }

}

 

function getEmailFromName(sKey) {

 

  // to use this function, don’t put anything in the first column (A) or row (1).

  // Put the name (i.e. the key, or what we’re looking for) in column B.

  // Put what we want to return in column C. 

 

  var columnToSearch = 1; //column B

 

  // Set the active sheet to our email lookup

  var ss1 = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss1.getSheetByName(“EmailContactList”)

  ss1.setActiveSheet(sh1);

 

  var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

 

  var line = -1;

  for( var i = 0; i < data.length; i++ ) {

    if( data[i][columnToSearch] == sKey ) {

      line = i;

      break;

    }

  }

   

  if( line != -1 ) {

    //do what you want with the data on “line”

    return data[line][2]; //value on column C of the matched line

  } else {

  return ‘myemailaddress@gmail.com‘;

  // if criteria is not found

  }

}

 

There are two functions here. The second one, getEmailFromName, looks at the address book to find the correct email address for the name.

The first function goes through the cells on the first sheet and looks to see if it is currently 2 days before the date on column A of the rota. If it is, it sends an email to the people on that row, organising by the two teams.

When I send the email I bcc myself for peace of mind.

The magic happens when you select the Resources menu, followed by ‘Current project’s tiggers’. Select the SendEmails function (NOT the getEmailFromName function), select ‘Time Driven’ from the next drop down, and I set the timer to daily at midday.

Google Apps is such a powerful tool that I’ve been using it more and more for various tasks. My biggest issue is that when I get stuck, searching for the correct term is difficult – you start searching for ‘Google App email’ and it returns results describing Gmail for Business, so you have to be very specific!