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=10006060*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

}

}