Go to Top


By Eric Eslinger
Program Officer, Educational Technology, Knowles Science Teaching Foundation

December 16, 2013

If you’ve been following along at home, you should have set up a Google site, and developed some simple forms to monitor student learning (i.e., formative assessment). Let’s take things to the next level, shall we?

Head into the spreadsheet you’re using to collect responses for one of your form questionnaires. There should be columns for name, responses, and so forth already. If there isn’t a column for the email address of the respondent, go ahead and make one, then populate it with your students’ email addresses. Next, make another column for feedback—each cell should contain a piece of feedback you would like to give to your students based on their performance on the formative assessment. Fill that column out too. Here’s a sample spreadsheet that I’ll be using for the purpose of this post.

Okay, now things get exciting. You have some feedback you would like to give to your students, but it’s in a spreadsheet. Let’s fix that. In the spreadsheet menu, go to Tools -> Script Editor. In the resulting page, select “blank project”. Welcome to the Wild West—Google Apps Scripts, where you can write programs that run on your spreadsheets (and word-processing documents and sites, and so on).

Okay, ready? Write this down in that area.

Wow. That’s a lot of text, and I’m not even giving it to you to copy-paste. What’s up with that? I’m inspired by Zed Shaw’s “Learn Python the Hard Way,” where you learn by doing. This is what programming is—you look at what other people are doing it, figure it out, and do it yourself.

Let’s take a look at this program carefully. First of all, it’s a JavaScript program—there’s lots and lots of references for that, and if you think you’re going to do a lot of Apps Script programming, it is probably worth learning a thing or three about JavaScript. Of course, you could just follow along here.

The first line defines a function. This is a bit of reusable code that I’ll run over and over. If you did this correctly, in the Run menu of your apps script, you’ll have a “sendEmails” entry. That’s how we tell Google to do stuff. We define functions and then run them.

At the start of the function, I set up a variable that is of use to us (and the function). Look at line 2: there’s a lot going on there, and it’s quite exciting. First, I am defining a variable to use later—var values = is how I say, “make a variable named values and set it equal to…”, and the right side of the equal sign is a bunch of woah-what’s-that, probably.

One bit at a time, whenever the program (and most programs work this way) encounter a long string of names with dots in them, it starts at the leftmost end and reads to the right. So, because we’re to the right of an equal sign, we have to get some value to put into that variable. We start with SpreadsheetApp, which is an object that Google gives us as part of the Apps Script environment. We can ask SpreadsheetApp about the current spreadsheet and stuff, which is what we’re doing.

Each of the names that end in a parentheses-pair like () are themselves functions. Instead of defining functions on line two, we’re calling them. These functions are ways of asking questions or telling commands to an object—just like the sendEmails function is a command we will eventually send to the spreadsheet.

So: SpreadsheetApp.getActive() asks the SpreadsheetApp to give us the active spreadsheet. We can then ask that spreadsheet what the current data range is (the cells that contain interesting values, as far as the spreadsheet can tell), and then ask that data range to give itself to us using getValues, which returns a nice two-dimensional array of cell values. Lots of work for one line of code.

Now that we have the spreadsheet in a variable called values, we can access any cell in that spreadsheet by treating values like an array. In JavaScript, you can get (0,0) in an array by using square-bracket notation. So values[0][0] would correspond to cell A1 of the sheet. Notice that while spreadsheets start numbering on row one, most computer things start counting at zero. Off-by-one errors are very common in programming, especially when using arrays. You’ll get used to it.

Row three sets up a way for us to iterate, which is to say, repeat a single command over and over, each time using a different row of the spreadsheet array. The name for this kind of loop is a for loop, and it says, “for the variable i starting at the value 1 and going as long as i is less than the number of rows in values (i < values.length), incrementing i by one each time (i = i +1, which is the easiest way to illustrate the difference between math and programming notation, I mean, when is i = i + 1 outside of this kind of thing?), do this stuff.” The stuff you do repeatedly is inside the curly-braces that terminate line 3 and start line 5, and it’s just the command on line 4.

Line 4 says, “hey, GmailApp, send an email with these parameters!” Because I read the instructions on using GmailApp, I know the first argument in the sendEmail(a,b,c) function is the email address you’re sending to. The second argument (“project feedback”) is the subject of the email, and the third argument, values[i][2] is the third column of the i’th row of the spreadsheet, which contains the comment I wanted to send in the first place.


  1. Why did I start the loop at 1 with “for (var i = 1” instead of 0, even though I told you that the first row of the spreadsheet was row 0 in the values array?

  2. What would I have to change if I added another project to my gradesheet, with a grade in column D and a comment in column E, and wanted to just send the feedback on the new project?

  3. What about if I wanted to send both feedback columns in the same email – columns C and E in this hypothetical spreadsheet?

Once it’s all typed in and customized, you can go to the Run menu and run your script. It will ask you for permission (since it needs to access your Gmail), but once run, it should send emails out.

If you have errors, look closely at them—they usually tell you what line they happen on and what kind of error they are. Post a reply with your error text or screenshot, or share your program (but not your students’ grades!) with me at eric.eslinger@kstf.org and I’ll check them out.

By now, you’re probably thinking: why should I do that instead of using the excellent FormEmailer? Or you may be thinking, “What are all those semicolons?” The answer is actually the same for both. Programming is (I contend, and more on this in a later post) a modern critical literacy. Like being able to read and write in the dark ages, being able to program in the internet age gives you vast powers, and I’m a fan of universal literacy of all forms.

So: FormEmailer is great. I use it, and you should definitely use it for your emailing. Programming is better, though, and if you learn how to write apps scripts for yourself, you can modify the FormEmailer code to do exactly what you want, or you can write your own stuff.

Let’s say you wanted to create Google Docs and share those docs with the students (so they could respond inline to your feedback) instead of using email. Could you write that program? Probably now you could (hint, there’s also a DocumentApp). Sure, there’s other apps scripts that will do that too, but that’s not the point. Learning to program here means you can say, “I want something that does X”, and you’d be certain that you could build it yourself if it doesn’t exist, and you can tweak it to be awesome if it does. Even better, you can contribute your changes back to the broader world.

If you’re interested in learning more about programming in JavaScript and don’t want to wait for me to post again, check out the Mozilla Developer Network’s introduction to Javascript resource page.

If you have good Apps Scripts resources, post them in the links below, and I’ll run a showcase of reader favorites in the near future.


Leave a Reply

Your email address will not be published. Required fields are marked *