Emailing Google Spreadsheet Grades

I am a teaching assistant for a friend this year, and the hassle of how to send out grades has been a small challenge for me. Last year the class was small enough that I could simply copy and paste from the Google spreadsheets we use to an email. This semester, however, we have more than 25 kids, so I wanted to find a better solution.

At RedSnake Philly one of the presenters talked about what he had done with Google Apps Scripts. I decided to take a look at it to see if I could automate emailing grades from a spreadsheet. It turns out that not only is it possible, but it’s pretty easy to do. The script to send an email to each student on our list is only about 10 lines long.

code.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 19;
  var numColumns = 25;
  var dataRange = sheet.getRange(2, 1, numRows, numColumns);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

  var headerRange = sheet.getRange(1, 1, 1, numColumns); //The first row tells us what each value means.
  var header = headerRange.getValues()[0]; //Treat it as a single dementional array
  for (i in data) {
    var row = data[i]; //Row of current users data
    var emailAddress = row[0] + "@seas.upenn.edu";  // add domain to pennkey
    var message = generateHtmlEmail(header, row);
    var subject = ScriptProperties.getProperty("EmailSubject");; //This should be changed for each assignment.
    MailApp.sendEmail(emailAddress, subject, message,
                      {htmlBody: message,
                       cc: "teacherAndOtherTAs" });
  }
}

The generate email function is a little more exciting. It uses a google doc as a template, and fills in the grade by replacing the header template in the doc.

code.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

function getHtmlEmail(header, row) {
  var templateDocId = ScriptProperties.getProperty("TemplateID"); //Look up template id.
  var docId = DocsList.getFileById(templateDocId).makeCopy().getId();
  var doc = DocumentApp.openById(docId);
  var body = doc.getActiveSection();
  var html = "";

  for ( var h in header ){
    body.replaceText("%" + header[h] + "%", row[h]); //Replace template code with the current students grades.
  }

  doc.saveAndClose();
  html = getDocAsHtml(docId);
  DocsList.getFileById(docId).setTrashed(true); //throw the doc away since we no longer need it.
  return html;
}

First we copy the document. We can then replace each variable that corresponds to a row header and return the html to send as an email.

This easily sends emails to everyone in the class. However, I still have a bit of work. Regrades require individual messages. I want to add some sort of flag that only resends messages to users when their grades changes.

Comments