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!
Hello Bradley,
What you have done would be of huge benefit to me. But I’m falling down at the first hurdle. I have my spreadsheet with the correct sheet names.
When I select ‘Tools’ and ‘Script Editor’ I get a code.gs page with “function myfunction() { }”
Where should the code go? Below this code, instead of it or between the {}? Wherever I try and put it I get “Illegal character . (line 7, file “Code”)” – which occurs at the var sh1 = ss1.getSheetByName(“Rota”) line. I’m sure it’s me, but a little help would be much appreciated.
Thanks,
Matthew
Hi Matthew. You can remove the template for myfunction – in fact, delete all the code and then paste in the code from this blog post. Try that and let me know how you get on.
Thanks again Bradley, believe it or not, it was the wrong kind of quotation marks! I retyped the single and double quotation marks and it worked.
Many thanks,
Matthew
Excellent. Glad to hear it all worked in the end.
Massive thanks for this – I got it working for a staff duty rota in a school. A real revelation!
You’re very welcome Tim! Let me know if you get stuck at all, and I’ll see if I can help.
Hi Bradley
This looks great! I’ve got no experience with this kind of thing and I have 2 questions:
1) I’m using a drop down list of names in the rota. Will that be ok or does the text need to be typed in manually?
2) Can I make the email go to everyone (so everyone knows who else is scheduled to work)?
Thanks!
Hi Rebecca, yes you can do both of this in Google Spreadsheets.
1. Go to Data, Validation and select what list you want in the dropdown (it’s similar to Excel). See here for more help (https://support.google.com/docs/answer/186103?hl=en)
2. Yes you can – and it’s much easier, just remove the lookups that I’ve written in the email function.
Glad you found it useful.
Hi Bradley,
This looks fantastic, and I am going to have a play with it today, but I am wondering if it will work for my roster that is split over two rows? For example my date is in in column A and one weeks roster is split over two rows, so the date cell is actually merged with the row below as well and some cells have content in both rows. Does that make sense?
I am hoping to use your script to send 1 email out to a group email address with all of that’s weeks roster information from row 1&2 or 3&4 etc….
As I said I will look at it in more detail but if you have a quick answer please let me know.
Marcus
Hi Marcus, my recommendation is to create a cell which aggregates all your information together. Look at using the & and + operators to create the cell contents. You now have my email address, so feel free to share your sheet with me and I’ll see if I can help.
Hi Bradley, I’ve been using this for 16 months now and it works brilliantly. I was wondering if there is any way of requesting a ‘read receipt’? I’ve had a look myself and can’t find anything, do you have any thoughts? Thanks again for making such a useful tool available to the world.
Hi Bradley, Fantastic work. Really helpful ! So glad you posted this.
I’m getting stuck when I try to read a date from the spreadsheet in to the email text.
When I code; blah blah, next meet is on + row[0] + ‘at’ + row[1] + ‘see you all then’ ,
the email reads; blah blah next meet is on Sun May 17 2015 00:00:00 GMT+0100 (BST) at Sat Dec 30 1899 15:00:00 GMT-0000 (GMT) see you all then.
Any ideas on how to change to date formatting ? Many thanks Ali
Ali, you can do this one of two ways:
1. The easiest is to format the cell in the original spreadsheet to however you’d like it to look. (This is what I’ve done).
2. You can use the formatDate function to change the format to what you’d like to use.
Hi Bradley,
Thanks for this. It works like a treat. One thing, how do you start a new line in the message body?
I assume the “nn” is for a new line, but the code doesn’t seem to pick it up and print nn at the end of the sentence…
Ben, use \n to enter a new line. I find that you need 2 of them to make paragraphs look pronounced, so you’ll need to use \n\n in the message body. Let me know how you get on.