Editor’s Note: Guest author Martin Hawksey is an advisor at the Jisc Centre for Educational Technology and Interoperability Standards. — Dan Lazin
When I started looking at Google Apps Script in 2010, one of the things that attracted me was the ease with which a non-developer like me could start customising Google Apps with only a few lines of code. Since then, the rich community of users and examples has continued to grow, and I’ve built event booking systems, entire student feedback solutions, and even integrated with Mozilla Open Badges.
Recently, Justin Marckel, the assistant principal at Cornatzer Elementary School in North Carolina, asked for help in modifying one of my existing Apps Script examples. Justin was recording teachers’ classroom activities using a Google Form, then manually copying and pasting data into separate spreadsheets for each teacher to review. Justin wanted to know whether there was a way for a Google Form to store the results in a master spreadsheet, then filter results to each teacher’s spreadsheet.
The basic pseudocode would be:
on form submit if teacher’s spreadsheet doesn’t exist, then create spreadsheet add teacher as viewer store id else get id open teacher’s spreadsheet copy values to teacher’s spreadsheet
Here’s a closer look at each of the steps.
Apps Script offers three triggers specific to Google Sheets: “on open,” “on edit,” and “on form submit.” Looking at the Understanding Events documentation, we can see that a form submit trigger gives us a few options for how to pull the submitted values out of the event parameter (usually called e). We can get the data as an array via e.values, a Range object via e.range, or a JavaScript object that pairs the form questions with the respondent’s answers via e.namedValues. In this project, the e.values array is most convenient, and it will look something like this:
['2010/03/12 15:00', 'bob@example.com', 'Bob', '27', 'Susan', '25']
First, though, we have to add the form-submission trigger. The user could add it manually from the script editor’s Resources menu, but in this case, let’s manage triggers programmatically:
function setup(){ if (ScriptApp.getScriptTriggers().length === 0) { ScriptApp.newTrigger('doOnFormSumbit') .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()) .onFormSubmit() .create(); } }
One of the big advantages Apps Script is that you’re automatically working in a Google-authenticated environment. The result is that you can programmatically create a new spreadsheet with one line of code, then add a teacher as a viewer in just one more line:
var newSS = SpreadsheetApp.create('Spreadsheet Name'); newSS.addViewer('email-address-of-teacher');
Writing data to a sheet requires more than a one-liner just because we need to specify which cells to write to. The Range.setValues() method expects a 2D array; because we’ve already retrieved the response to the form as an array, it’s easy to throw those values into a row of cells:
Range.setValues()
var destSS = SpreadsheetApp.openById(id); // open teacher spreadsheet var destSheet = destSS.getSheets()[0]; // grab first sheet var insertRow = destSheet.getLastRow() + 1; // next row to enter data destSheet.getRange(insertRow, 1, 1, e.values.length) .setValues([e.values]);
The completed project is here. The bulk of the form-submission handling (including error logging) happens in around 50 lines of code, and I was able to complete the project within an hour. Now Justin no longer needs to copy, paste, and set up separate spreadsheets, potentially saving him hours of work. Justin recently contacted me to say:
“We have successfully used our program over the past couple of months to provide teachers with meaningful and efficient feedback. It has been successful at several other schools as well, and I got word today that our school district is looking at adopting it as a district-wide tool.”
This is just one of a growing number of examples of how Google Apps Script is directly benefitting educators by allowing custom solutions with the security, convenience, and power of Google Apps.
