This blog post contains outdated information. Please check out posts with more current content.
Google Forms and Merging
If you use Google Forms with students you can use mail merge to provide students with feedback. The Flubaroo Add-On allows you to add personalized comments to each students responses before emailing back quiz scores. When you are not using Flubaroo you can use the Add-On “Yet Another Mail Merge” to email information from a Google Form. This allows me to respond to student free response answers in a quiz and give them email notification of my comments. I use mail merge to send student peer review comments. I use a Google Form with a rubric and comment fields to evaluate student projects. Mail merge allows me to send the student the rubric scores and my comments.
Compose a Draft Email
Yet Another Mail Merge works with Gmail. In Gmail compose a new email. Anywhere in the email where you want to insert information from the spreadsheet you put a placeholder merge tag.
For example, I always collect the students name. I want the email to address the student personally so I start the draft email with: Hello <<student name>>,
The merge tag for the mail merge is to place the column header in between double less than and double greater than symbols.
Spelling counts so you need to make sure that your merge tag is exactly the same as the column header.
Note that what the column header says will NOT appear in the emails you send out. You will want to provide context in your email text for your mail merge.
For example, if I asked “How many hours do you study?” and this is my column header in the spreadsheet then my text of my email might be: You indicated that you study <<How many hours do you study?>> hours.
To avoid accidental misspellings you can use concatenation to create your merge tags for you. Create a new sheet in the spreadsheet by clicking on the plus icon in the bottom left hand corner. This will allow you to have some space to “play.”
The column headers in your spreadsheet span horizontally across the top. You probably want the list vertically in your email. To transpose is to switch directions. Using the transpose formula will display the column headers vertically.
Add Merge Tags
You need to append the symbols << to the front of each column header and >> to the end. To do this you will concatenate the text strings with the column header values.
The ampersand symbol means AND. You are going to tell the spreadsheet that you want a text string AND you want the column header AND you want another text string.
In the adjacent column type this formula. Remember that all text strings need to be in quotations. Adjust the cell reference for the cell location that your data is located.
This should automatically append the merge tags to the left and right side of the column header text. Click one time on the cell containing the formula and you should observe a small blue square in the bottom right hand corner of the cell. Double click on the square to automatically fill down the formula for all of the column header text.
Alternatively you can click the mouse down on the blue square and pull down for the range of cells you wish to copy the formula to.
Now that you have a list of the column headers in the format of a merge tag you can copy and paste these into an email.
[expand title=”Click Here to view a sample email with merge tags.”]
This is summary information about your video project from your peers.
Your peers evaluated you on a scale of 1 to 5 on the overall quality of your video.
<<ov1>> people gave you a score of 1.
<<ov2>> people gave you a score of 2
<<ov3>> people gave you a score of 3
<<ov4>> people gave you a score of 4
<<ov5>> people gave you a score of 5
<<ovtotal>> total number of reviews
Your peers evaluated you on a scale of 1 to 5 on the overall quality of your assessment.
<<asst1>> people gave you a score of 1
<<asst2>> people gave you a score of 2
<<asst3>> people gave you a score of 3
<<asst4>> people gave you a score of 4
<<asst5>> people gave you a score of 5
<<assttotal>> total number of reviews
Your peers evaluated you on a scale of 1 to 5 on the effectiveness of your annotations.
<<ann1>> people gave you a score of 1
<<ann2>> people gave you a score of 2
<<ann3>> people gave you a score of 3
<<ann4>> people gave you a score of 4
<<ann5>> people gave you a score of 5
<<anntotal>> total number of reviews
Your peers evaluated how confident they felt about understanding the teaching style after watching your video.
<<am confused and could not understand the style of teaching presented>> said “am confused and could not understand the style of teaching presented”
<<Completely understand the teaching style>> said “Completely understand the teaching style”
<<Have a good introduction to the teaching style>> said “Have a good introduction to the teaching style”
<<Have a pretty good feel for the teaching style>> said “Have a pretty good feel for the teaching style”
<<Have a very good introduction to the teaching style and I will investigate it further>> said “Have a very good introduction to the teaching style and I will investigate it further”
<<have no idea what is going on>> said “have no idea what is going on”
<<Kind of understand the teaching style>> said “Kind of understand the teaching style”
<<Grand Total>> total number of reviews
For another sample email
[expand title=”Click Here for another example of using merge tags.”]
The sheet with the Google Form data needs to be the first tab in your spreadsheet. Most likely it is. If not drag the sheet to the first position. The Yet Another Mail Merger performs the mail merge from the information on the first tab.
I recommend you change the column header that contains the recipients email address to be “Email Address.” The script will do this automatically for you; however, it is easier and more reliable to identify the email column prior to running the Add-On.
In the Add-On menu in Google Sheets choose the “Yet Another Mail Merger.” From the pop out menu choose to “Start mail merge.”
You will be prompted to choose the draft email that the spreadsheet will merge with. A draft email is created automatically in Gmail when you start an email and do not send it. Simply leave it open in your Gmail or close out (x) of the email without sending.
Click the blue “Send Emails” button to conduct the mail merge.
Sample Merged Email
To view a sample of what an email looks like to the student
[expand title=”Click Here to view email sample”]
Hello John Smith, Nancy Drew, Mickey Mouse