I recently posted about using TextExpander to semi-automate the process of sending grade updates to students. That post got me poking around for other ways to do a more thorough mail merge, and I found a tutorial for scripting Google Spreadsheet to send emails. With some minor modifications, I now have a spreadsheet set up as a grade book that can email each student with their current point total and class average at the push of a button. Below is a description of how I adapted the original spreadsheet to make it do what I wanted. You can open my spreadsheet and make your own copy to modify, too.
The original file is designed to collect user information with a form, save it to a spreadsheet, and email the user. Working from the copy of the tutorial spreadsheet, the first thing I did was to delete the form, as I do not need it in my application. Then I rearranged the columns and added some for my assignments and for totals. I left the original columns for first name, last name, and email address intact to minimize the need to edit the script. The script uses the first row of each column to identify which variable that column holds, so it’s important to respect those labels.
When I had the tutorial spreadsheet how I wanted it, I customized the text of the email template to suit my purposes. I added two new variables, based on two new columns, Total Points and Class Avg:
Then I ran the script with myself as the test recipient, and I was disappointed to find that the value for Current Avg did not get filled in. I returned to the script and began looking for the place where the data range is set, finding it in line 4. The original tutorial spreadsheet has 4 columns, so the range is set to 4. I have 5 columns I want the script to read from, so I changed the dataSheet.getMaxRows value to 5:
I ran the script again and it worked as expected.
The last step I took was to customize the subject line for the automated email. In the tutorial spreadsheet, this subject line is hard-coded in the script, which seemed a little too permanent or hidden or something. I changed it to set the subject line by reading it from a cell in the ‘Email Template’ spreadsheet.
Any time I want to update my students on their grades, I just run the script by clicking on the Tools menu, selecting Script Manager, and clicking ‘Run’. This solves one more of the problems I’ve had weaning myself from the tyranny of the LMS.