Scripting Google Spreadsheet to do email merge

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.download film The Boss Baby 2017 now

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:

template text to send email

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:

screen shot of script text

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.

 

 

1 thought on “Scripting Google Spreadsheet to do email merge”

  1. Hi. Like you I am trying to get away from our clunky LMS. I have a really powerful grade book set up in Google Sheets already and I’ve built a mail merge into it. I am trying to use personalised subject lines in the emails sent to students and I think that your method of hooking into a spreadsheet cell for the subject may a step toward helping me achieve that goal. Would you mind sharing that script or part of the script with me?

    Incidentally we’ve been sharing grades with students via a published dashboard. That way students can get a live view of their grades without us having to do anything. I want to set up a mail merge to email students who have not completed a weekly homework or met their target grade (and the subject line will reflect which scenario applies).
    Here’s an example of the dashboard we’re using: https://docs.google.com/spreadsheets/d/1dTkiTijTAt4wnOQWNhsA5QT-UPDHBykTorDXCxj5Pr8/pubhtml?gid=1851892907&single=true&range=$A$13:$L$24

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s