Notes to the Future with Google Scripts & PearDeck

I wrote previously about my use of PearDeck in an end of semester activity. One of the slides in this deck was one in which I asked students to write themselves a note containing the things they would want to remember three weeks later at the beginning of semester two. With vacation now over, that day is now. I wrote a Google script that automatically sends the notes they wrote to each student. This allowed me to generally send these out without inadvertently reading the notes in detail. I saw some of them, but made an effort not to see who was writing what.

The PearDeck output spreadsheet for this deck looks like this:

Column 3 of the spreadsheet contains columns with the student’s email addresses, so that made it easy to get the address corresponding with a given note to the future, which is column 4. By selecting ‘Script Editor’ from the tools menu, you can create a script that has the ability to process this data.

You can delete the code that is there, and then paste in the code below to create the email script.

You’ll need to save the script, and from the run menu, select ‘sendEmails’. You’ll need to give permission for this script to read the spreadsheet for this to proceed. The emails will all be sent from your Google email.

Code:

function sendEmails() {

var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 12; // Number of rows to process

var dataRange = sheet.getRange(startRow, 1, numRows, 5) //get all the data in the spreadsheet from the range between (startRow,1) and (numRows,5).
//This gets the first 12 students that are in this class, and the five columns of data I want to use for the email.

var data = dataRange.getValues(); //Store the spreadsheet data in an array
for (i in data) { //for each row in the spreadsheet
var row = data[i];

var studentEmail = row[2]; //the student email is in element 2, which is the third column

var subject = "Note To The Future (AKA Now): " + studentEmail; //email subject

//This next line is text formatted using HTML tags that appears before each students' note.

var greeting = "Happy New Year!

Before break, I asked you to write an email to yourself with things you would want to remember at the beginning of the semester. Whatever you wrote in that text box in Pear Deck is below for you to enjoy.

I'm looking forward to seeing you Tuesday or Wednesday in class.

Be well,
EMW

";
var message = greeting + row[3] //Combines the greeting and the student's individual note

MailApp.sendEmail({to:studentEmail, subject:subject, htmlBody:message});
//Sends the email to the student, with the subject defined in line 14, and the message from lines 20 and 21.
//htmlBody means the email will be formatted as HTML, not just text.
}

}