Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Student Sign in Sheet with Mail Merge

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: http://goo.gl/iMEHR 
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 http://drive.google.com 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 @gmail.com 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.

5 Easy Steps for Teaching with Digital Tools

Introducing digital tools into your teaching can seem challenging at first. There are many tools out there, and it might feel like there’s a lot to learn. But with easy steps for teaching with digital tools, you can start simply and gradually. There’s no need to dive in all at once – just take it one step at a time. Taking easy steps for teaching with digital tools starts with trying something!

Read More »
Students Prepare to Present

Help Your Students Prepare to Present

Maximize student success in presentations with ‘Speaker Notes by AliceKeeler,’ the ideal Google Slides add-on. Enhance how students prepare to present with easy transfer of speaker notes to Google Docs, promoting effective communication skills. Dive into the world of engaging, technology-aided presentations and empower students to shine in their academic endeavors. Discover the key to transforming student presentations into interactive, skill-building experiences.

Read More »

Infographic 7 Basic Steps for a Google Form

New to using Google Forms? This tool is essential for teachers to not only save time but to be adaptive to student needs. Use Forms to survey students, play games, personalize instruction, and assessment. This infographic on the 7 basic steps for a Google Form will help you get started.

Read More »

Online Workshop: Get Your Add-on Published

How can you make Google Workspace (Docs/Sheets/Slides) even better? You can create custom Add-ons with Google Apps Script. Wondering How to Get Your Google Workspace Add-on Published? Join Alice Keeler, Google Developer Expert, to learn the steps to get your Add-on officially published.

Read More »

Leave a Reply

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