Tuesday, April 30, 2013

[Gd] How Apps Script Makes Classroom Observation Quicker and Easier

| More

Google Apps Developer Blog: How Apps Script Makes Classroom Observation Quicker and Easier

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
get id
open teacher’s spreadsheet
copy values to teacher’s spreadsheet

Here’s a closer look at each of the steps.

Handling a form submission event

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) {

Creating and managing permissions on a spreadsheet

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

Writing data to a spreadsheet

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:

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)

Simple, effective and efficient

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.

Martin Hawksey   profile | twitter | blog

Martin is an advisor at the Jisc Centre for Educational Technology and Interoperability Standards (CETIS), a national advisory and innovation centre that works on on educational technology and standards for the UK Higher Education and Post-16 Education sectors. Martin is an active contributor to the Apps Script community and regularly shares projects on his blog, MASHe.

URL: http://googleappsdeveloper.blogspot.com/2013/04/how-apps-script-makes-classroom.html

No comments: