Friday, February 17, 2012

Using Google Forms FormEmailer Script

Over the past 5-6 months, I've been looking for an ideal way to have submitted Google Form responses go to my students in the form of an email and I think I finally have my solution!  This has been around for a while and I remember looking at the FormEmailer script a while back but for some reason I didn't go as in to detail with it and didn't think it would fit my needs.  Double backed to it today and I'm super excited to use this going forward!  Here are some directions and my use case to share in case others want to try using this.

First, I created my rubric Google Form.  An example of this is here.  This Google Form would be only for me to fill out as the teacher.

I made sure the first question was "Email Address."   This part would probably take me the longest going forward. I entered all my student's Google Apps for Education email addresses as a drop down menu choice.  So my kids could see the form linked up on our class website, I would make a COPY of the spreadsheet/form and take out the first question so no one could submit this form or see other's email addresses.  (I wouldn't want a curious student filling out the original rubric and have the FormEmailer script running to send a copy to whoever, would I?)

FormEmailer SCRIPT
After the form was ready to go, I had to install the FormEmailer Script.  Honestly, I don't know that much about scripts in Google Spreadsheets other than they are little bits of code that can make things happen.  Thanks to Henrique Abreu who created this FormEmailer script and his website has great directions for showing how this works.  Again, I know nothing about code and I could get this to work so give it a try! :)

I followed Henrique's video and the steps below the video to install the script in my Google Spreadsheet.

I went to TOOLS > SCRIPT GALLERY and did a search for "FormEmailer."  Two scripts came up.  The screenshot below shows the one you should install to your spreadsheet.

After installing the script, I went to FormEmailer > SETTINGS and I created the email that my students would receive. I followed the advice given in Henrique's video to use the "placeholders."  If you don't know any HTML coding, you can uncheck the HTML box on the left and just craft the email the way you want.  If you do know HTML coding, you can leave this checked and add little codes where needed to do things like bold or do a linebreak

and so on.  My Email to my students is below:

After crafting this email, I went and filled out the form, making sure to choose my own email address as the "student" for now just to test things out.  I saw the data from the form go into the spreadsheet.  I then went to FormEMailer > PROCESS MANUALLY and was asked what row to send, so I chose row "2" which is where my student submission was.  The email went through and looked good but I tweaked a few things back in the FormEmailer > Settings area and ran again until things looked the way I wanted.

After testing a few times, I decided I want to turn on the ability for the form to automatically go to the recipient when the form is "SUBMIT"ed.  The details on Henrique's site didn't say how to do this in the newest version of the script so I threw out a call on Twitter.  Thankfully, new tech friend Michelle Russell shared with me what a "trigger" was and how to turn it on.

I went back in to the spreadsheet and went to TOOLS > SCRIPT EDITOR.  I clicked on "Copy of FormEmailer" as shown below.

The Script window opened where all the code for this script was located.  Scary.  I didn't touch any of it!  Go to TRIGGERS > ALL YOUR TRIGGERS.  

In the "All your Triggers" window, I clicked ADD A NEW TRIGGER and made sure the windows showed what is shown below.  
Run = timeDriven
Events = From Spreadsheet & On Form Submit

I checked out the notifications link and you can have the spreadsheet email you if there's a problem. I set that to email me immediately if there's a problem.  Screenshot below of my choices.

I clicked SAVE in the Triggers window and then went to FILE > SAVE in the script window just to be sure everything saved.  

I then tested filling out the form and seeing what happened.  Totally worked!  Any new submission sends the email address in that row the email I created with the details.   Screenshot below of the email the students will receive.  

So in the end, I now have a digital rubric I can use with my students to be able to quickly and efficiently email them feedback on a project.  Entering student email addresses into the first question of the form is what will take me the longest, but for that little bit of time for the bigger payoff of authentic feedback (and no paper!) it's totally worth it.  I suggest you try it out and see what you think.  This is a also a great resource for administrators looking to do walk-throughs or staff feedback forms when visiting a class.

P.S.  Check the "help/troubleshooting" section of Henrique's site if you run into any problems.


  1. Wow- fantastic! Thank you Chad! A related question to walk-throughs- is there a way for admin to have a running google form for each teacher, but then also merge the form data from all teachers in a building, and then again across the district? My superintendent really wants this functionality. Any advice? Thank you! Claudia Felske

  2. Hey Claudia. Thanks for the compliment! Glad it could help. I don't know off hand how to do what you're asking. However, I would check out Hank Thiele's blog post here where he shares a way to do the "form collection" like I posted about but have it go an extra step where it also puts that individual submission in a Google Doc Folder as a "Google Document." He has shared using this as an admin to keep a file on that Walkthrough per teacher. Not identical to what you're talking about, but I could see you setting this up to save to a specific GDoc Collection for the "building" and then just share that "building" collection to the Supt. Everything in one place by Collection. I haven't attempted Hank's steps to set this up yet so I won't be any help there. Here's his blog post:

  3. Nice post Chad. I'm Henrique, the FormEmailer developer.
    The trigger setup instructions didn't showed up in the spreadsheet because of a new issue on Google Apps Script API.

    You can break up the cells merge manually and the instructions will appear.
    The trigger used with this version of the FormEmailer script should be of the 'time-driven' type, not "on Form Submit" (set to run every minute or 5 minutes). As the "on Form Submit" doest not work perfectly (you may get duplicated emails). This is another issue (128) with Google API. I'll implement the "on Form Submit" trigger option when it's fixed.

    But again, nice blog post.

  4. Henrique - thanks for the comments and the update on the "trigger" stuff. I'll have to edit my post to incorporate your new information. Really appreciate what you did to make the FormEmailer script. Very excited to be using it with students.

  5. Chad and Henrique -
    Awesome post. Thank you so much. I went into the Script Gallery and can no longer find the FormEmailer script to choose. Are we just waiting for Google to approve the script?

    Thanks, Jared

  6. In response to Fel's question, a quick and easy way to accomplish would be to use the importrange function. It can pull pre-formatted data from one spreadsheet to another 'master' sheet.

  7. This comment has been removed by the author.

  8. Is there a way to assign point values like one would with a traditional rubric and have the points appear in the email and on the spreadsheet?

    1. couldn't you use a scale type question with 1-4 and get a point that way?

  9. Not to my knowledge without some crafty scripting work. I just keep the points in a range that I can quickly add them in my head.

  10. I am having a hard time including a hyperlink in my email like you have in the last line of your example. I'm using this as the HTML code but it's not including a hyperlink

    Here's my code. Do you know why it's not linking the text?
    this form.

  11. If anyone can shed some light....
    Some advice please.

    I have a form that I'm using for registration. The form is setup to send a copy of submission to two people (person filling in registration form as well as attendee). However, if someone (lets say Admin Assistant) is registering more than one person I would like for the Admin Assistant to still continue to get a copy of the conformation. As of now, once the Admin Assistant receives one email conformation that is the most the person will get even if they register more than one person. I hope I haven't made this confusing.

    Any suggestions?