Putting the magic in the machine since 1980.

Tuesday, December 28, 2010

Sending Emails from a Google Spreadsheet

I have been using Google Spreadsheet to keep my class grades for a couple of years. It has several advantages over the PC version:

  1. I can share the spreadsheet with my TAs so they can enter grades for their labs. We are all always on the same page.
  2. Google spreadsheet keeps track of all old revisions, so if any one of us makes a mistake we can always go back to see what the original grade was.
  3. It is trivially easy to create a graph of the grades and post a link to it on the class blog. The graph gets updated automatically as the grades change.

However, one remaining problem was how to give each student his grades without revealing all the grades, which our university frowns upon, even if the names are not posted. The clear answer is to email each student his own grade.

Luckily, Google has a cool feature called Google Apps Script which lets one write JavaScript programs that can access the data on a Google doc and do things like send emails. So, I wrote a short program to automatically send each student his grades.

Installation

To use it you must first set up your grades spreadsheet to look like this one. Note that

  1. the first row has a 'name', 'email' and other columns for each item,
  2. some columns have the same name as other columns, but with ' Comment' appended, for example: 'Test 1' and 'Test 1 Comment', the comment columns contain any comments you want to give the student
  3. the sheets are named 'grades' and 'template'
  4. the 'template' sheet contains the template information for building the emails (your message).

Once you have the spreadsheet built, go to 'Tools -> Scripts -> Scripts Editor and cut-and-paste the emailgrades.js script in there. Save it, then close and re-open the spreadsheet (or, run the onOpen method if you know how to do that). You should now see a new menu item on you spreadsheet called 'Class', as shown in the screenshot.

Usage

The 'Send Emails' item will send each student (row) his grades if you choose it when you have no cells selected. If you first select (light blue background box) some cells in the spreadsheet then it will only email the students in the selected rows. You can verify that the emails have been sent by going into your gmail account and checking the 'Sent Mail' folder. It can take several minutes for the emails to start appearing so be patient.

The 'Preview Emails' item is mostly for debugging. It will show you a preview of two of the emails that send emails would send. The 'Show Email Quota' item tells you how many more emails you can send today. Google limits scripts to 500 emails per day to keep spammers from abusing the system. Since my classes have at most 60 students this has not been a problem for me.

If you have any comments, bug reports, or feature requests please post them below.

5 comments:

GBankS said...

how to verify that the emails have been sent by going into gmail account?

Jose Vidal said...

If you look at the code (emailgrades.js), there is a commented-out line that shows how to bcc: an email.

In my classes I put this line back in so I get a bcc: on every email sent, so my gmail gets a copy of every email sent.

GBankS said...

thank's jose... this article is very useful. I was greatly helped by your article. Bless you

Eman said...

hey this is really useful,
i just have a question how can I change the column names ? or use more columns ?
Thanks

Eman said...

Figured it out, I was getting error because I did not define the other column. Thanks a lot this is just perfect and saved me a lot of time. 180 4 Assignments worth of time :)