Here is some text I prepared for a possible Google Apps Developer blog guest post. It doesn’t look like it’s going to get published so rather than letting it go to waste I thought I’d publish here:

Martin Hawksey is a Learning Technology Advisor for the JISC funded Centre for Educational Technology and Interoperability Standards (JISC CETIS) based in the UK. Prior to joining JISC CETIS, and in his spare time, Martin has been exploring the use of Google Apps and Apps Script for education. In this post Martin highlights some features of a Google Apps Script solution which combines Google Spreadsheet and Google Documents to speed up and standardise personal feedback returned to students at Loughborough College.

One of things that drew me to Apps Script over two years ago was the ease in which you could interact with other Google services. I also found that both using Google Spreadsheets and a coding syntax I recognised ideal as a ‘hobbyist’ programmer.

Late last year when I was approached by Loughborough College to take part in their ‘Fast Tracking Feedback’ project, I saw it as an ideal opportunity to get staff using Apps Script  and showcase the possibilities of Apps Script to the Google Apps for Education community.

The goal of the project was to produce a mechanism that allows tutors to input assignment grades using a custom UI that mirrors the final feedback sheet or enter details directly into a Google Spreadsheet.  These details are then pushed out as individually personalised Google Documents shared with the student. This sounds relatively simple, but the complication is that each assignment needs to map to a predefined set of rubrics which vary between units. For example in one course alone there are over 40 units and every unit can be assessed using multiple assignments with any combination of predefined criteria ranging from pass, merit and distinction.

Below is an example student feedback form highlighting the regions that are different for each assignment.

Example student feedback form highlighting the regions that are different for each assignment

The video below shows a demonstration of the current version of the of the ‘Fast Tracking Feedback’ system is set-up and used:

Solution highlights

A number of Apps Script Services have been used as part of this project. Lets look at how some of these have been implemented.

DocList Service – The self-filing Google Spreadsheet

The eventual plan is to rollout the Fast Tracking Feedback system to teaching teams across the College. To make the life of support staff easier it was decided to use a common filing structure. Using a standardised structure will help tutors stay organised and aid creation of support documentation.

When a tutor runs the setup function on a new feedback spreadsheet it checks that the correct folder structure exists (if not making it) and moves the current spreadsheet into the pre-defined collection.

Self-generating folder structure and organization

The code that does this is:

// code to generate folder structure and move spreadsheet into right location 
// + ROOT_FOLDER 
// |- SPREADSHEET_FOLDER 
// |- DRAFT_FOLDER 
// |- RELEASED_FOLDER 
var rootFolder = folderMakeReturn(ROOT_FOLDER); // get a the system route folder (if it deosn't existing make it 
// create/get draft and release folders 
var draftFolder = folderMakeReturn(DRAFT_FOLDER,rootFolder, ROOT_FOLDER+"/"+DRAFT_FOLDER); 
var releaseFolder = folderMakeReturn(RELEASED_FOLDER,rootFolder, ROOT_FOLDER+"/"+RELEASED_FOLDER); 
var spreadsheetFolder = folderMakeReturn(SPREADSHEET_FOLDER, rootFolder, ROOT_FOLDER+"/"+SPREADSHEET_FOLDER); 

// move spreadsheet to spreadhsheet folder 
var file = DocsList.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()); 
file.addToFolder(spreadsheetFolder); 

// function to see if folder exists in DocList and returns it 
// (optional - if it doesn't exist then makes it) 
function folderMakeReturn(folderName,optFolder,optFolderPath){ 
try { 
   if (optFolderPath != undefined){ 
     var folder = DocsList.getFolder(optFolderPath); 
   } else { 
     var folder = DocsList.getFolder(folderName); 
   } 
   return folder; 
} catch(e) { 
   if (optFolder == undefined) { 
     var folder = DocsList.createFolder(folderName); 
   } else { 
     var folder = optFolder.createFolder(folderName); 
   } 
   return folder; 
} 
}

UI Service – Hybrid approach

A central design consideration was to make the Fast Tracking Feedback system easy for College staff to support and change. Consequently wherever possible the Apps Script GUI Builder was used to create as much of the user interface as possible. Because of the dynamic nature of the assessment rubrics part of the form is added by selecting an element holder and adding labels, select lists and textareas. Other parts of the form like the student information at the top can be added and populated with data by using the GUI Builder to insert textfields which are named using normalized names matching the spreadsheet column headers. The snippet of code that does this is:

app.getElementById(NORMHEADER[i]).setText(row[NORMHEADER[i]]);

Where NORMHEADER is an array of the normalized spreadsheet column names and row is a JavaScript Object of the row data generated based on the Reading Spreadsheet data Apps Script Tutorial.

Hybrid UI construction using GUI Builder and coding

Document Services – Master and custom templates

The process for filling in personalized feedback forms has three main steps. First a duplicate of the Master Template is made giving it a temporary name (DocList Services). Next the required assessment criteria are added to the form using the Document Services mainly using the TableCell Class. Parts of the document that are going to be filled with data from the spreadsheet are identified using a similar technique to the Apps Script Simple Mail Merge Tutorial. Finally for each student the assignment specific template is duplicated and filled with their personalised feedback.

if (email && (row.feedbackLink =="" || row.feedbackLink == undefined)){
  // Get document template, copy it as a new temp doc, and save the Doc's id
  var copyId   = DocsList.getFileById(newTemplateId)
                         .makeCopy(file_prefix+" - "+email)
                         .getId();
  var copyDoc  = DocumentApp.openById(copyId);
  // move doc to tutors folder
  var file = DocsList.getFileById(copyId);
  var folder = DocsList.getFolder(ROOT_FOLDER+"/"+DRAFT_FOLDER);
  file.addToFolder(folder);

  // select the document body
  var copyBody = copyDoc.getActiveSection();

  // find edittable parts of the document
  var keys = createKeys(copyDoc);

  // loop through elements replacing text with values from spreadsheet
  for (var j in keys) {
    var text = keys[j].text;
    var replacementText = ""; // set the default replacement text to blank
    if (row[keys[j].id] != undefined){ // if column value is defined get text
      replacementText = row[keys[j].id];
    }
    copyBody.replaceText('{%'+keys[j].text+'%}', replacementText); // replace text
  }
  copyDoc.saveAndClose();

  // create a link to the document in the spreadsheet
  FEEDSHEET.getRange("B"+(parseInt(i)+startRow)).setFormula('=HYPERLINK("'+file.getUrl()+'", "'+copyId+'")');
  FEEDSHEET.getRange("C"+(parseInt(i)+startRow)).setValue("Draft");
  // you can do other things here like email a link to the document to the student
}

Currently the system is configured to place generated feedback forms into a draft folder. Once the tutor is happy for the feedback to be released either individual or class feedback forms are distributed to students from a menu option in the feedback spreadsheet for the assignment, a record being kept of the status and location of the document.

Easy record keeping

Next steps/Get the code

The Fast Tracking Feedback System is currently being piloted with a small group of staff at Loughborough College. Comments from staff will be used to refine the system over the next couple of months. The current source code and associated template files are available from here.

The Fast Tracking Feedback project is funded by the UK’s LSIS Leadership in Technology (LIT) grant scheme.