How I automated the staff duty rota

Traditionally the staff duty rota has been a long list published in the Common Room noticeboard. It quickly became covered with the scrawl of people swapping and yet still people forgot! 

I thought there must be a way to tidy the rota up and to automate the sending of a reminder and, sure enough, there is.

I'm no uber-coder, but I do pride myself on my skills as a magpie - picking through other people's code and adapting it to my own needs. I set up a two-columned Google sheet like the one below:

Then by adapting Bradley Howard's rota script I created the script below. It picks up the duty staff member's e-mail from a separate sheet (called EmailContactList) and sends them a reminder the day before their duty's due. Any changes to the rota have to come through me. It works a treat - nice!

function sendEmails() {

  var ss1 = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss1.getSheetByName('Rota')

  ss1.setActiveSheet(sh1);

var sheet = SpreadsheetApp.getActiveSheet();

  // Fetch the range (needs changing when the range changes) this script adapted from here: http://www.bradbox.com/blog/using-google-apps-to-organise-a-rota/

  var dataRange = sheet.getRange('A2:E100')

  // 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==1)

    {

        var subject = 'Duty reminder for ' + row[1] + ' ' + row[0];

        var emailAddress;

        var message;

        message = 'Automated duty reminder for ' + row[1] + '.' +   

                  '\n\n Hello ' + row[2] +

                  '\n\n You are on POWIS duty tomorrow.' +      

                  '\n\n Hello ' + row[3] +

                  '\n\n You are on QUARRY duty tomorrow.' +      

                  '\n\n Remember, you can check the rota at any time by clicking this link:' +

                  '\n\n http://goo.gl/pckc5I' +

                    

                  '\n\n A full explanation of what duty involves is posted here:' +

                    

                  '\n\n http://goo.gl/xXMYp'

       

        // Send an email to the first person

        emailAddress=getEmailFromName(row[1]) + ',' +

                      getEmailFromName(row[2]);

        MailApp.sendEmail(row[2]+'@oswestryschool.org.uk,' +row[3]+'@oswestryschool.org.uk', subject, message, {bcc:'xx@oswestryschool.org.uk'});

    }

  }

}

 

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 'xx@oswestryschool.org.uk';

  // if criteria is not found

  }

}