Walkthrough Mail Merge for Multiple Users and One Form

As I sit in my office writing the blog post for this week, which is actually making up two weeks worth of posts, I am thankful for a wonderful PLN that gives me ideas on what to write about.  The topic for this blog came from the Nebraska Ed Chat (#nebedchat) on Twitter last night.  It also came from a question and response on types of scripts used in form that we like to use.  I was talking about some email scripts that were in principal walkthrough forms that ESU7 (#esu7) principals use and how we made it so one form can be used by a district and have the bcc on the email go to the evaluator.  Ann Feldmann (@techiefeldie) from Bellevue asked about if I had a blog post on it.  I told her I didn’t, but thanks for the idea and I would do one.  So here we go. (Sorry for the shameless Twitter plugs)

Mitch Kubicek created the script that most of ESU7 principals use for their walkthroughs.  So I cannot claim, nor will I, that the entire script is mine.  All I did was change one row for the bcc.

The original script for the mail merge was the following:

MailApp.sendEmail(rowData.teacher, emailSubject, emailText, {bcc: “yourname@domain.org”  } )

To make the change so that all principals in a district or building use the same form and collect the data on the same spreadsheet you make a sport on your form called observer.  Make sure you format the data in the observer list the same as you did for the teacher list. The format needs to be: (First Last) firstlast@domain.org.  If you are adding this to your form, make sure it is to the left of the email sent column, or the script won’t run correctly.  You will then need to change the previous script line to the following:

MailApp.sendEmail(rowData.teacher, emailSubject, emailText, {bcc: rowData.observer } )

One word of caution: make sure what ever is after the rowData is worded the same as you did for your form.  Otherwise this will not work.

Those are the only changes you will need to make to the form and the script.  Just be ready for the email to be sent out by the creator of the form, not from the observer.  So maybe in the email template add in the following line:

Observer:${“Observer”}

And make sure that it is spelled exactly or it won’t fill it in.  It will also be case sensitive.

I know this helped some of you, but others it may not have.  Once again thanks to Ann for the idea.  I enjoy learning from all my PLN in person and on Twitter.