Student Sign in Sheet with Mail Merge




One thing I like to do to start class is to have students fill out a Google Form.  I use this to take attendance, ask survey questions to see where students are at on a project or how they feel about class policies, warm up questions and I always ask for “comments, questions, concerns” to help me get a pulse for where everyone is at.

At the beginning of class I have a roster of who is there (I can use this to randomly call on students). If I asked survey or quiz questions I can go to the spreadsheet and go to the Form menu to show summary of responses.  This gives me instant graphs of how students answered so I can use this information to instantly drive my instruction.

I install the valmerge script on the spreadsheet so that I can send individualized feedback to students based on their warm up responses and in particular the comments feedback they gave me.  I think this is a valuable tool to make sure that I am not letting any students fall through the crack and that the directions and lessons I am giving are actually making sense to the students. I take student feedback very seriously and I let that help me make classroom management decisions along with adjusting my instruction to student needs based on what they tell me versus what I feel needs to change.

With the effort of one and a half emails I am able to send a personalized email to every student in the class.

Step 1: Make a copy of this spreadsheet: 
File->Make a copy. Rename it with a title meaningful to your class along with the date you are going to use it.

Step 2: Go to and create a Google Form. Make sure you ask for email address.
Suggestion for other fields:

  • First Name
  • Last Name
  • Student ID Number (SID)
  • Comments/Questions/Concerns
  • Any quiz or survey questions

Step 3: Choose a response destination
In the new Google Forms the responses to a Google Form are stored in the form.  You want to choose a spreadsheet to connect the responses to.  Along the top of the edit form screen is a button to “choose response destination.”

Step 4: The 2nd option is “new sheet in an existing spreadsheet”
Choose the spreadsheet you copied in step 1.

Step 5: On the mail tab you will want to craft your mail merge response.  This is essentially a newsletter you will send to the students.  I generally tell students what to expect for upcoming projects, reminders of when test or other important dates are, links to YouTube videos to support what we are learning, etc… I also will add to this letter as I read through student comments.  As I see patterns such as several students asking when the project is due and I realize I was not as clear as I thought I was, I will clarify this in the letter.

Note that your cells you bold, italicize or color will appear this way on the email.

The merge comes in with [merge tags].  The column headers on the “Form Responses 1” tab are your merge tags. Suggestion to rename all of your column headers to a single word. Make sure your email address column is named only “email.”

Hint: I make a copy of the Form Responses tab and call it “data.” This allows me to manipulate the spreadsheet more easily and not tamper with the original data that came into the form.  If you choose to do this you will want to change cell B2 on the mail tab to be the name of duplicated tab.

Step 6: On the Form Responses 1 tab (or on your duplicated “data” tab) add a column at the end and call it feedback.  Go down the spreadsheet and give individualized feedback to each student.

Step 7: At the top it should say ValMerge next to the help menu (if it does not go to insert->script and search ValMerge and install). You will choose Mail Merge from the drop down list.  You will be asked to authorize the script.  You may need to go back to the ValMerge menu and choose Mail Merge again.

Step 8: When prompted for the sheet type in “mail.” You are telling the script which tab has the ValMerge template on it.

Step 9: Assuming all of the student email addresses are valid email addresses (script will not run if someone forgot on their email) then you will get a sample email to preview. This sample contains HTML tags so don’t worry about the code you see in there.  Indicate yes and your emails will be sent.