mail merge google forms

 

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.
Screen Shot 2015-04-24 at 5.46.24 PM

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.
2015-04-24_17-59-57

On the new sheet you created type the formula =transpose( into one of the cells.
Screen Shot 2015-04-24 at 6.02.16 PM

Highlight the column headers to capture the range of column headers and then press enter.
Screen Shot 2015-04-24 at 6.03.35 PM

The column headers should now be displayed vertically on the other sheet.
Screen Shot 2015-04-24 at 6.04.38 PM

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&”>>”

Screen Shot 2015-04-24 at 6.11.12 PM

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.
double click to fill down

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.

Fill Down

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.
Control Shift V

Sample Email

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

For another sample email

Click Here for another example of using merge tags.

<<first>>

Your comments: <<comments>>
Your link: <<link>>
My feedback <<feedback>>
Blog feedback <<blogfb>>
Portfolio Conferences coming up

Soon I will post a link on the agenda giving you a chance to sign up for a portfolio appointment. Prior to the appointment you will fill out a Google Form where you self evaluate against the rubric.

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.
2015-04-24_18-24-14

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.

2015-04-24_18-26-57

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

Click Here to view email sample

Hello John Smith, Nancy Drew, Mickey Mouse

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.
4 people gave you a score of 1.
1 people gave you a score of 2
9 people gave you a score of 3
3 people gave you a score of 4
5 people gave you a score of 5
22 total number of reviews
Your peers evaluated you on a scale of 1 to 5 on the overall quality of your assessment.
people gave you a score of 1
1 people gave you a score of 2
1 people gave you a score of  3
3 people gave you a score of 4
3 people gave you a score of  5
8 total number of reviews
Your peers evaluated you on a scale of 1 to 5 on the effectiveness of your annotations.
people gave you a score of 1
people gave you a score of 2
1 people gave you a score of 3
4 people gave you a score of 4
3 people gave you a score of 5
8 total number of reviews
Your peers evaluated how confident they felt about understanding the teaching style after watching your video.
0 said “am confused and could not understand the style of teaching presented”
4  said “Completely understand the teaching style”
0 said “Have a good introduction to the teaching style”
3 said “Have a pretty good feel for the teaching style”
0 said “Have a very good introduction to the teaching style and I will investigate it further”
0 said “have no idea what is going on”
1 said “Kind of understand the teaching style”
8 total number of reviews
Digiprove sealCopyright secured by Digiprove © 2015