Tag Archives: fast-track

1 Comment

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 
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()); 

// 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:


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)
  var copyDoc  = DocumentApp.openById(copyId);
  // move doc to tutors folder
  var file = DocsList.getFileById(copyId);
  var folder = DocsList.getFolder(ROOT_FOLDER+"/"+DRAFT_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

  // create a link to the document in the spreadsheet
  FEEDSHEET.getRange("B"+(parseInt(i)+startRow)).setFormula('=HYPERLINK("'+file.getUrl()+'", "'+copyId+'")');
  // 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.


Another post related to my ‘Hacking stuff together with Google Spreadsheets’ session at  Dev8eD (A free event for building, sharing and learning cool stuff in educational technology for learning and teaching!!) next week. In this example Google Apps Script is used to create a custom user interface that can be used in Google Spreadsheets, allowing tutors to enter feedback and grades based on individual assessment criteria pulled from a central data source (another Google Spreadsheet). The system then generates personalised feedback forms (Google Documents) based on the data and distributes them to students.

As part of my work outwith JISC CETIS I have been helping staff at Loughborough College with their LSIS funded Fast Tracking Feedback project. As part of this project I’ve helping staff create a system that standardises and speeds up the return of assignment feedback to students. This project has generated a number of outputs for the wider community including training material and some code snippets (sending free SMS | generating Google Documents from Spreadsheets).

As my official involvement in the project comes to the close there is another chunk of code and resources to push out in the wild. It’s complete copy of the beta system currently being piloted with staff at Loughborough College. If you want to get an idea of how it works here’s a short video demonstrating the system I did as a lightning talk at GEUG12.

The code

If you want to pick over the code for this I’ve dumped a copy in github. This is more for reference as the code makes use of the Apps Script GUI Builder for parts of the interface, which can’t be extracted from the Spreadsheet. For a functional version you’ll need to make a copy of the four documents linked to below (this is followed by some instructions on setup and usage). I should also point out that this system has been build around the British BTEC qualifications. An example of the assessment and grading criteria is on page 3 of this document. Hopefully there is enough reusable code for other qualification systems.

Files you’ll need

  • Master Spreadsheet – this is the main document with all the Apps Script in it.
  • Master Template (you’ll need to File > Make a copy) – this is a Google Document used as a template for the form
  • Criteria Sheet – spreadsheet of units and courses with the related assessment criteria/rubric
  • Student Lookup – spreadsheet of student fullnames and related Google Apps ids (used because the App Script Group Services can only return ids and email addresses)

The basic setup

  1. Place all four copied files into a folder in Google Docs (you can name the folder anything you like).
  2. Change the share setting on the folder so that either ‘Anyone with the link’ or ‘People at your Google Apps domain with the link’ can view.
  3. Open your copy of the Master Spreadsheet and open Tools > Script Editor…
  4. On lines 17-19 copy and paste the document id/keys for Master Template, Criteria Sheet and Student Lookup. You can get these by opening the documents and looking at the browser url for the highlighted bits.
    Where to find spreadsheet/document keys
  5. From the Script Editor you can also open File > Build a user interface… and then open the importStudentList and click on the ‘Enter the group …’ label to edit the text in the property pane on the right-hand-side and then similarly for the textfield beneath it. There’s also the option to customise/add logos to the entryForm GUI
  6. In the Criteria Sheet create your list of units/courses and associated assessment criteria
  7. In the Student Lookup sheet import a list of Google Apps Ids and names

There are additional options in the code to change folder names, pass, merit, distinction colouring.
That’s it. Enjoy and any of your thoughts are welcomed in the comments (I can’t make any guarantees to respond to all)

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


Note: This is a personal post made outwith my current employment at JISC CETIS

Back in May 2011 Tony Hirst looked at the Visual UI Editor For Google Apps Script and commented that he thought before long I would have posted something about it. Well almost a year later here’s what I’ve got for you.  As part of the Fast-tracking feedback project (funded by the LSIS Leadership in Technology grant) with Loughborough College I ran a training session at the beginning of the month to help staff learn about user interface construction in Google Apps Script. The session follows on one of my earlier blog posts releasing some code to batch fill in Google Docs from a spreadsheet of feedback comments. As part of the session I produced a step-by-step guide for creating a Google Sites based form/gadget that could read and write data to a spreadsheet. As part of the project this guide is available for re-use using the link below. Before you download/use a couple of things worth bearing in mind:

  • the guide has been tweaked slightly for publication and as a consequence I may have inadvertently broken it. If you find something is wrong leave a comment in the document
  • in the guide an image is used to help you layout a form and is not intended to be part of the final navigation

Introduction to Google Apps Script: Custom Interfaces Guide


Finding free ways to send and receive SMS messages is something I’ve covered before (Twitter Ye! Twitter Ye! Keep your students informed with free SMS text message broadcasts! and Free SMS voting using intelliSoftware SMS Gateway service). Browsing the Google Apps Script site I saw there is a new tutorial by Romain Vialard which lets you Link a Gmail Filter to Google Calendar SMS Notifications in which it “shows how to create a specific filter in Gmail and be notified by SMS when you receive an important email”. This got me thinking if there was a way of using this feature to send free SMS updates to students, say for example, when they they received some Fast-tracking feedback.

This solution needs the student to configure their calendar for SMS notifications and not all mobile operators are supported, but I will show how to make this work and you can decide if its worth the effort.

Student side setup

To enable SMS updates all the students need a Google Calendar and to have enabled SMS notifications for event invites (you may want to tell students about this anyway just so they can get SMS notifications for event reminders). This is done by:

  1. Google Calendar Settingsopening Google Calendar going into ‘Calendar settings’ from the cog icon top-right
  2. on the Mobile Setup tab the student needs to verify their mobile number (the supported UK providers are: 3; O2; Orange; T-Mobile; Virgin Mobile; and Vodafone – full international list here), then click ‘Send Verification Code
  3. once a code has been received enter it in the ‘Verification code’ field and click ‘Finish setup
  4. this then takes you to the ‘Notifications’ settings for your default calendar. In the New events row tick the SMS checkbox (you might want enable other SMS services), then click ‘Save
    Google Calendar Notification Settings

Sending SMS notifications manually

To send a message to students open your Google Calendar and create an event (you might want to create a new calendar so you don’t mess up your own schedules – creating a new calendar and creating events using your current date/time also means you have a record of when a message was sent - SMS notifications appeared to only be sent for events in the future). The fields included in the SMS are the event title, location and date/times. Here’s an example message recieved:

 Example SMS notification

In the ‘Add guest’ field paste in a comma separated list of student email addresses (these addresses need to be associated with the student’s Google Calendar so if you are not using Google Apps for Education some prep is required to get this list) and click ‘Add’. I would also recommend making sure the guest options to modify, invite and see a guest list are unticked.

When you save the event shortly afterwards students should receive the SMS message and an email similar to the one shown below:  

Example email notification

You might want to agree a system with your students where they have to confirm they have seen it by clicking ‘Yes’ in the ‘Going’ option. This will give you a summary of who has seen it in the event details. You may want to utilise other Calendar features like notes.

 guest list

Using Google Apps Script to automatically send SMS notifications

This whole idea came from a Google Apps Script tutorial so it only seems fitting to show how this could modified to automate the guest list creation, which in turn would trigger the SMS notification. I’m not going to go into any detail about what Apps Script is but basically as one of its main uses in to automate tasks it’s fairly easy to write a script that will take a list of email addresses from a Google Spreadsheet, create a new event in your calendar adding those addresses as guests. And here it is:

For this to work all you need is a column of email addresses associated with personal Google Calendars and within seconds your sending  free SMS messages to people.

So what do you think, a viable solution or is student side setup/concerns over network coverage going to prevent you using it? 


In October last year the Sport Learning Technologists at Loughborough College successfully won funding from the LSIS Leadership in Technology (LIT) grant scheme for the Fast-tracking feedback using Google Scripts project. Here’s an extract from the project summary:

This project will effectively combine Google Apps for Education and Google Apps Script in order to create a tool which allows tutors to enter grades and feedback in a single spreadsheet which then automatically populates individual feedback proforma, simultaneously sharing these results with students, progress tutors, and administrators as appropriate.

The benefit will be an increase in the efficiency with which assessment feedback can be shared, improving the speed and quality of paper-less student feedback. A successful conclusion to this project will be demonstrated by reduced submission turnaround times and a reduction in the errors brought about by inconsistencies in data entry.

Project funding is not just for deploying technology but also increases the capacity within the organisation at the operational level. With this in mind I have been working with Loughborough, helping them in the technical aspects of developing the Fast-Tracking Feedback System and also learn about Google Apps Script via a series of workshops. Friday was the first of these and I thought I’d share the story so far.  

The Loughborough group had already got of to a flying start successfully modifying My #eas11 world premier: Creating personalised Google Documents from Form submissions. 5 months is a long time in Google Apps Script and since then not only is there some new functionality in Apps Script, but I’ve also picked up some new tips. My own understanding has come on along way thanks to receiving a preview copy of Google Script: Enterprise Application Essentials by James Ferreira [due out 27th January]. I’ve been a regular visitor to James simpleappssolutions.com site and tutorials so wasn’t sure if his book would teach me much more, but how I was wrong. Part of the reason I believe for this is the book is geared towards ‘enterprise applications’ so concentrates on documents and workflows, just as assessment in education (for better or worse) is concentrated on documents and workflows.   

So below are two links of the current version of the Google Apps Script Spreadsheet and example Document template followed by a quick video to show how it is used. Obviously these are still work in progress as there is still 6 months to run on the project but there’s already enough there for others to benefit from and perhaps feedback on design.

Stay tuned for more developments