Scripting Google

[this post was updated 11 May 2015 with an improved version of the script]

There has not been much demand for my Google Apps expertise lately, so I let my Google Apps for Education [GAFE] certification lapse. Nonetheless, I continue to discover new cool things the ambitious teacher can do with GAFE. Lately, I’ve been playing around with Scripts that tie together multiple GAFE products. It reminds me of working in the BASH shell where you could pipeline together effective tools in order to get some impressive stuff done.

The scripts are written in Javascript, with a bunch of different ways to invoke data and functionality from the various apps. You could, for example, import a “blob” from a spreadsheet, insert the data from particular cells into a document, export that document as a PDF, and then email the PDF as an attachment . Pretty powerful stuff.

This is exactly what I’ve been doing for my latest project, which is (like all good educational technology) a solution to a problem. Our middle school students need to assemble a document that contains reflections on their progress in each of their 10 courses. However, they’re forgetful, and would quickly lose any sort of paper product. They have varying levels of comfort with a word process, but most are capable of quite impressively messing up the formatting of a Google Doc. Thus, we needed a product where they students type their reflections into a box, from their cell phones or laptops, over the course of a week and then a reflection document magically appears a week later.

We’re using a Google Form to collect the data and stick it into a (large!) spreadsheet. The script first gets a list of the Google IDs for the students who have submitted and then, for each, makes a copy of the template document, inserts all the relevant text, and output/emails the PDF. There are a couple deprecated function calls, the approach is neither quick nor efficient, and I get frequent (seemingly random) runtime errors… but it works. Here is the code, please let me know if you find it useful.

// Performance notes:
// - spreadsheet cannot have blank rows
// - COURSES needs to exactly match the course options on the form/spreadsheet
// - lines in the SOURCE_TEMPLATE are carefully numbered, but the second line
//   is blank, and could be used for additional boilerplate

// Google doc id of the document template
var SOURCE_TEMPLATE = "1PF_qtfewX1IzViAK4LgAJxkKSs_2M5cRr5jCHfoXa74";

// Google doc ID of the form responses
var STUDENT_RESPONSES = "1EP-i-IKX1LSfghL1s2y4Pj3H0EBkemeN_-9Cvaq-5QE";

// Google drive ID for the output reflection documents
var TARGET_FOLDER = "0B5kgbRSu3Tiofk9BV0FfYjdQY1NZS3UyQUh2NFhfc05UOGx0TkFOMWpIdlhWZTlvNEdNMzQ";

// A list of the courses
var COURSES = ["Language and Literature - English", "Language Acquisition", "Humanities", "Science", "Mathematics", "Visual Arts",
 "Drama OR History of Music", "Band OR Music", "Physical and Health Education", "Design and Technology"];

// the line numbers that correspond to the COURSES array, as they appear in

var CL = [[5, 7, 9, 11, 13, 15],
 [18, 20, 22, 24, 26, 28],
 [31, 33, 35, 37, 39, 41],
 [44, 46, 48, 50, 52, 54],
 [57, 59, 61, 63, 65, 67],
 [70, 72, 74, 76, 78, 80],
 [83, 85, 87, 89, 91, 93],
 [96, 98, 100, 102, 104, 106],
 [109, 111, 113, 115, 117, 119],
 [122, 124, 126, 128, 130, 132]];

// This function returns an array of all the Google IDs of students who
// made at least one submission to the STUDENT_RESPONSES spreadsheet
// The array is sorted alphabetically, and duplicates are removed

function getStudentNames(sheet) {
 var lastRow = sheet.getLastRow();
 var dataRange = sheet.getRange(2,2,lastRow-1);
 var data = dataRange.getValues();
 var students = [];
 for (i in data) {
 var sortedStudents = [];
 for (i in students) {
   if (students[i] != students[i-1]) {
 return sortedStudents;

// This function makes a copy of the SOURCE_TEMPLATE document, and opens
// it for writing

function copyReflectionDoc(sourceId, name) {
 var source = DriveApp.getFileById(sourceId);
 var newFile = source.makeCopy("Reflection: " + name);

 var targetFolder = DriveApp.getFolderById(TARGET_FOLDER);
// newFile.addToFolder(targetFolder); 

 return DocumentApp.openById(newFile.getId());

// This function puts the reflections into the copy of the document

function insertReflections(name, sheet, refDoc) {

 var ps = refDoc.getParagraphs();
 var paragraph = ps[0];
 paragraph.setText("Reflection document completed by: " + name);

 for (k in COURSES) {
 var lastRow = sheet.getLastRow();
 var lastColumn = sheet.getLastColumn();
 var dataRange = sheet.getRange(2,2,lastRow-1,lastColumn-1);
 var data = dataRange.getValues();

// get all the entries from "name"
// push() the data onto the array r2, then pop() it off onto the document later

 var r2 = [" "," "," "," "," "," "];
 for (i in data) {
  for (j in data[i]) {
   if (data[j,i].indexOf(name) != -1 && data[j,i].indexOf(COURSES[k]) != -1) {
// Logger.log("Got this data: " + r2 + " and I'm going to put it at line " + CL[k][5]);
 var paragraph = ps[CL[k][5]];
 paragraph.setText(r2.pop() + " ");
 var paragraph = ps[CL[k][4]];
 paragraph.setText(r2.pop() + " ");
 var paragraph = ps[CL[k][3]];
 paragraph.setText(r2.pop() + " ");
 var paragraph = ps[CL[k][0]];
 paragraph.setText(r2.pop() + " ");
 var paragraph = ps[CL[k][2]];
 paragraph.setText(r2.pop() + " ");
 var paragraph = ps[CL[k][1]];
 paragraph.setText(r2.pop() + " ");


function generateReflections() {

 var data = SpreadsheetApp.openById(STUDENT_RESPONSES);

 // Fetch variable names
 // they are column names in the spreadsheet
 var sheet = data.getSheets()[0];
 var idArray = getStudentNames(sheet); 
 Logger.log("These students have been found: " + idArray);

// For each student, copy SOURCE_TEMPLATE, write in the reflections, save
// the PDF, and then email it to the student
// Performance note: maximum time for G-Script allows ~50 students to be
// processed. Maybe do batches of 40 by changing and final values from 0 and
// 40 in the next two lines?

 var i = 0;
 while ( i < 40) {

 Logger.log(" ->" + idArray[0,i] + "<- ");
 var refDoc = copyReflectionDoc(SOURCE_TEMPLATE, idArray[i]);
 insertReflections(idArray[i], sheet, refDoc);
 var targetFolder = DriveApp.getFolderById(TARGET_FOLDER);
 var pdf = DriveApp.createFile(refDoc.getAs('application/pdf'));
 var savepdf = targetFolder.addFile(pdf);

 var subject = "Reflections (draft)";
 var message = "Hello!\n\nPlease find attached the final draft of your reflections. If you have already received a printed copy, you do not need to print a new one. If you have questions, please contact Mr Doucette or Miss Smits.";

 // un-comment the next line to enable direct mailing of the reflection
 // documents
 // MailApp.sendEmail(idArray[0,i], subject, message, {attachments:[refDoc.getAs(MimeType.PDF)]});


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s