The Jolly Postbot: Using Apps Script to notify teachers of letters sent home

Allan and Janet Ahlberg’s ‘The Jolly Postman’ is one of the most captivating books I can remember from my first years in primary school. It tells the tale of a bicycling postman who goes about a village delivering letters to fairy tale characters. With each turn of the page there is an actual envelope containing and actual letter! I felt like I was intercepting the mail of witches and giants!

I aspired to be a postman, convinced it was the most glamorous job in the world, but I did not think of the book again until I needed a title for my Google Apps Script powered file mailer.

The Jolly Postman
The Jolly Postman

Keeping teachers informed of letters sent home

The Jolly Postbot serves to peak at letters sent not to witches and giants but to parents and carers.

Being informed of information sent home is useful for school staff, and though there are many ways of making this happen, I saw an opportunity to learn about Google’s MailApp service and time-based triggers.

At my school, letters home are sent via a mailing system, then stored in a Google Drive folder for staff to access. The following code keeps track of the files uploaded to that Drive location, then mails a copy to staff.

Screenshot of Google Drive files
Screenshot of Google Drive files
One folder, one sheet

The Google Sheet keeps a record of files in a ‘Letters home' folder.

Record of files kept in folder
Record of files kept in folder
Record of files kept in the folder

Attached to the Google Sheet is a script which is wrapped inside a function.

function listFiles(){ 
//Stuff happens
...
}

First up, some variables are defined. The folder and spreadsheet keys can be extracted from the url of each.

//Set variables
var folderID = "KEY OF FOLDER";
var spreadID = "KEY OF SPREADSHEET";
var ss = SpreadsheetApp.openById(spreadID);
var sheet = ss.getSheetByName("Drive Index");
var folder = DriveApp.getFolderById(folderID);

The script then loops through the Drive folder and adds the file ID, upload date, file name and web link to the ‘list’ array. The ‘existing’ array keeps a list of the file IDs which have already been sent by the Jolly Postbot.

//Get files
var list = [];
list.push(['ID','Date Uploaded','File Name','Link']);
var files = folder.getFiles();
while (files.hasNext()){
file = files.next();
var row = [];
row.push(
file.getId(),
file.getDateCreated(),
file.getName(),
file.getUrl()
);

list.push(row);
}

var existing = [];
var startCount = sheet.getLastRow();
for(i=1;i<=startCount;i++){
existing
.push(sheet.getRange(i,1)
.getValue());
}

Ready for attaching to the HTML email, a Jolly Postbot image is created.

// Image URLs
var botURL = "http://reveleigh.com/content/images/2020/12/jpb.png";
// Fetch images as blobs, set names for attachments
var postBotBlob = UrlFetchApp
.fetch(botURL)
.getBlob()
.setName("The Jolly Postbot");
var botBody = "<br><br><img src='cid:postBot' style='width:250px; height:250px;'/>"
The Jolly Postbot
The Jolly Postbot
The Jolly Postbot

Finally, a for loop iterates through the list of files and checks to see if each letter is already included on the spreadsheet. If it isn’t present, MailApp.send() sends an email to a mailing list of staff and appendRow() adds a row to the spreadsheet.

for(x=0;x<list.length;x++){
if(existing.indexOf(list[x][0]) == -1){
sheet.appendRow([list[x][0],list[x][1],list[x][2],list[x][3]]);

var date = list[x][1];
var doc = list[x][2];
var link = list[x][3];
var subject = "Letter Home | " + doc;
var email = "staff@school-name.com";
var message =
"<p style='font-size:18px;font-
family: Helvetica, Arial,
sans-serif'>

<i>This is an automated email
from your friendly School
Communications Robot. Bleep.
Blob. Bleep.</i>
<br>---------- <br><br>"+

"The following file has recently
been sent home to parents:<br>
<br>" +

"<b>File Name:
<span style='color:red'>" + doc
+ "</span></b><br>"+
"<b>Link: </b><a href='"+ link
+"'><b>Click here</b></a> to see
the file.<br>" + botBody +

"<br>----------<br><i>All
previous letters home can be
found in the Jolly Postbot's
pigeon hole, <a href='https://drive.google.com/drive/folders/"+folderID+"'>here</a></i>";

//Compose email
MailApp.sendEmail({
name: "The Jolly Postbot",
to: email,
subject: subject,
htmlBody:
message,inlineImages:
{
postBot:
postBotBlob
}
});


// Make sure the cell is
updated right away in case the
script is interrupted
SpreadsheetApp.flush();


}//End If

startCount++;
}//End loop

The HTML above is not particularly beautiful, but it does successfully create this whimsical email to staff:

Example of an email
Example of an email to staff

In order to send the file automatically, I explored the time driven triggers. Every 5 minutes the listFiles() function runs.

Time based trigger
Time based trigger
Time based trigger

A primary school teacher and family man in the UK who likes tinkering with code and who sometimes tries to write good.