Frankly I am a data nerd, using a spreadsheet or database gets me abnormally excited. I create queries in an Access database frequently to filter my data so I can create my custom reports. So I was pretty excited to see that the valmerge script in Google Spreadsheet gives me a query option.
The query option allows me to tell the mail merge which rows to include in the mailmerge.
In the sample sheet the query says to look at the column that says send and see if there is a y in there. Now, if you are using your own Google Form probably you did not include a field called send and then your valmerge script will not run.
Option 2: Create a column called send and put a y in each cell that you want to send the mail merge to.
Option 3: Define your own criteria. If you created a grade column and you only want to send your mail merge to students who scored poorly delete the send=y and replace it with grade<70. (note this assumes the column with the students grade is titled “grade”)
In using the query function you are able to send emails only to students who have submitted recently. I usually start by making the send column, typing a y and dragging that down the column. I send the mail merge and then delete all they y’s and either let them all be blank or replace them with anything that is not a y such as “sent.” Then if you want to send a follow up email you can just change your query to send=sent. Any new entries I just type a y in the send column before I run my mail merge.
Remember when dealing with data you have to type things exactly the way you have them or the scripts will not run properly. If in your mail merge directions you tell it to send the email to Hello [name] but there is no column with the title of name, you have [first] and [last] then the script will not ignore the errant description, but will instead give you an error.
When I create forms I tend to ask a full question “What is your first name” which is great for a form, but not so great for my mail merge. If I go in and change the column titles to “first” unfortunately that changes my form. So always make a copy of the sheet before you start changing the column titles. I go through and change each column title to a single word all lower case just to keep things simple for my mail merge.
Other reasons you might get an error code when using valmerge is you have blank column titles. Make sure each column has a title. Sometimes for making my data visually manageable I will have an empty column between columns. valmerge does not like this. At least name it blank.
As a bonus if you put formatting in your mail merge form letter that will show up in the students email. So if you change the font color, increase the font size, fill the cell with a color, etc… all of that will show up to the student.