Mail Merge from a Google Spreadsheet


I know you’re thinking why in the world would I want to do this…

Have you ever wanted to email each student in your class individual feedback? But then realized how overwhelming that task is. You’re 20 emails into it and you don’t have the strength to message the other 180…

Here is what you want to do. Create a Google Form so students can submit their assignment, this is extra awesome because then all of your grading is in one spreadsheet. No loose papers to grade, nothing to collect.

Now that you have all the student answers in a spreadsheet (go ahead, use flubaroo to grade it if you want) you can make a column for score and a column for feedback. Go down the rows and while you’re watching TV on your couch make comments for each student and give them a score.

Not required but I suggest you duplicate the sheet with the data and rename all the column headings to one word. (Change “what is your email?” to “email”). Rename the sheets to one word also.

Then in the spreadsheet insert->script->ValMerge (search for mail merge, it comes up). Say yes to the red dire warnings about installing a script.

I like to rename my sheet 1 to something short like “grade”

You will need to add a tab to your spreadsheet, I call mine “mail” but you can call it whatever you want.

You will need to copy and paste the field key from the ValMerge help page

Change the values in that sheet to match what you want.

Anything that refers to a cell in your spreadsheet of data you want to put the title of the column header in brackets.

Once you have all your data and you have your mail merge letter set up, just click on the ValMerge button that came up in the toolbar after you installed the script.  Choose Mail Merge.

Type the name of the sheet that has the ValMerge key (remember I called mine “mail”)

If you did everything right you should get a confirmation screen asking if you really want to send the email…

Then each kid gets an email addressed to them personally (assuming you had a name field in your mail merge) with personalized data about their score on the assignment and the specific individual feedback.

Click here to link to my sample form