Newer Content
This blog post contains outdated information. Please check out posts with more current content.
- Another Mail Merge Script in Google Spreadsheet
- Google Sheets: Merge Data into One Column
- Google Forms: Sign In Sheet & Providing Weekly Feedback to Students
- Google Sheets: Create A Student Response Box
- Using Google Forms Instead of Worksheets
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.
Install Add-On
From the spreadsheet the Google Form is linked to, install the Add-On “Yet Another Mail Merge.” This only needs to be done once. You will find the Add-On already installed in future spreadsheets.
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>>,
<<column header>>
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.
New Sheet
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.”
Transpose
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.
On the new sheet you created type the formula =transpose( into one of the cells.
Highlight the column headers to capture the range of column headers and then press enter.
The column headers should now be displayed vertically on the other sheet.
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.
Ampersand (&)
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.
=”<<“&A1&”>>”
Fill Down
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.
Copy
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.
Paste
Tip, when pasting into the email hold down the shift key.
Sample Email
[expand title=”Click Here to view a sample email with merge tags.”]
Hello <<group>>
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
[/expand]
For another sample email
[expand title=”Click Here for another example of using merge tags.”]
<<first>>
First Tab
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.
Email Address
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.
Run 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.”
Draft
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.
Send Email
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
[/expand]
1 thought on “Using Mail Merge from a Google Form”
Thank you! I really needed this to email the results of students’ graded rubrics. I create a rubric in Google Forms and this way, students get their results back, individually.