Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Mail Merge Script in Google Spreadsheet

Mail Merge Script in Google Spreadsheet

This post has been UPDATED

Please check out my blog post on Mergo. The Mergo Add-on is a high quality mail merge that is created by a personal friend of mine (in other words, it is trustworthy!)

YouTube video

I enjoy using mail merge to communicate with students.  One popular tool is flubaroo.  When students use a Google form if the teacher installs the flubaroo script it will auto grade the results and email out responses to students.

Flubaroo is great for sending out student responses to the quiz but is a pre-formatted mail merge and I want one that is more customized.

Usually I use the Google Spreadsheet script valmerge, has worked great.  I ask my students to sign into class each day using a Google Form.  This tells me who was in class and then I can also ask warm up questions to get a feel for where the students are at.  Most importantly I make a paragraph text style question that asks for “Questions/Comments/Concerns.”

I duplicate the sheet so I can edit the headers.  Editing the headers of the spreadsheet behind a Google form changes the form itself.  I edit the headers so that each header is only one word.  Thus I change “What is your email?” to simply “email”

I then create a column for feedback to each students individual question, as well respond to their answers to the warm up.  I can then formulate a newsletter that responds to patterns and questions that come up.  With mail merge I can insert the individual students comments and my responses in amongst the newsletter.

Recently I decided to try another mail merge script.  There are many to choose from.

From the Google Spreadsheet insert menu choose script.

In the search box type in Mail Merge

Scroll down and choose the script called “Edited another Mail Merger”

Install the script.

After installing an additional menu option of “Mail Merge” will appear next to the Help menu.

**Note: the Mail Merge menu sometimes can take a bit to appear, wait a couple of minutes for it to appear. You may need to close out of the spreadsheet and open it back up to see the “Mail Merge” option along the top next to the Form menu.  

In your Gmail create the email that you want to send all students.  For any field in your spreadsheet that you want to insert into your email you flank it with $%field%.
For example you may want your email to say Dear John Doe.  So your email you draft will start Dear $%first% $%last%.  This assumes your column header is “first” and “last” for the name.

Save your email as a draft.

Back on your spreadsheet make sure that the tab that has the feedback and fields you want to send out is the FIRST sheet.  The default first sheet is the sheet that the forms empties into.  If you right clicked on this tab and duplicated it (which I always do) you will want to drag the tab to the first spot.

Click on the Mail Merge menu that now shows along with the other menu options and choose start.  If the script asks you which column contains the participants, it is referring to the column that contains the emails.
Suggestion: Name the email column “Email Address” if the script does not seem to be running.

If an email is entered incorrectly so that it is not recognized as an email it will stop the script. Fix the email and start the Mail Merge again.

A column to the right of your columns will show that the emails have been sent.

Want More Help with This? Become a Premium Member

6 thoughts on “Mail Merge Script in Google Spreadsheet

  1. Thank you for your tutorials on ValMerge and “Edited another Mail Merger.” After much trial and error, research and tinkering, I could not get ValMerge to work (script errors) so I tried this one and followed your advice exactly. It does not work for me either. After I hit start and select my email draft, it creates a column called “Email sent” and then nothing else happens. I wonder if this is because I am using my university Google Apps account. Any advice would be appreciated. I am excited about this use of Google spreadsheets.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.