Thursday, December 6, 2012

Using FormEmailer to send custom emails to students based on responses

I love Google Apps for education! My favorite workflow is to use Google Spreadsheets/Forms to collect information from students (mainly responding to my math questions) and then have the Spreadsheet automatically "grade" the response and send a customized email back to the student.

Recently, I took a lesson from Yummy Math (who is awesome!) about the record Powerball drawing.

Here is how I implemented the lesson using the Google Apps for Education resources from my school site.

Yummy Math has great questions about the Powerball, so I utilized these as my framework. I embed them in my class Google Site, here is an example from my Grade 7 class:
I like Google sites for a lot of reasons, but the main one is the enhanced document capabilities. I can embed videos and interactive activites into my "worksheets". In this unit, I embeded a youtube video of the latest Powerball drawing (the one before the record). It was also helpful since I teach abroad and some of my students had no idea what the lottery drawing looked like.

By having them watch the video of the drawing, it makes the explanation of the premise of the problem much easier, especially for my EAL learners. In addition, it gives me a reference point to help scaffold my questioning to derive theoretical probabilities.  I can pause and ask them how many balls are left and how many of them are numbers they picked, helping them get the probability of them winning on the next ball.

I use the Google site subpages capability to create a break in the questioning. I have a lot of students who like to "power through" the questions if they are all lined up consecutively. By making them click to the next page for more (Powerball part 2 in the picture above), it at least creates another step for them to perform before moving on. Sometimes, I limit the Sharing capability so they can't see that subpage until I want them to (but I can prepare it in advance without worrying if they will see it or not).

So, I broke the assignment up into 7 parts, culminating with the Powerball Simulator as given by Yummy Math: 
My students LOVED running this simulator for days to see if they could win the jackpot. I had a few students who hit the $1million, but no jackpot winners. It was great to have them simulate for so long to see how much money they lost in the long run. 

The powerball Part 8 was where I got the most student buy-in. I embedded a Google Form in the Google Sites page that allowed them to "buy" an entry. They submitted their numbers and their "ticket" was created. I even closed the entries as the same time as the official Powerball drawing. (Which happened to occur right before our lunch time, making for great lunch discussion on the powerball and whether they were going to win the $479million).


How did I embed this? First I created the Form associated with it. (You can check this out in detail on my blog post from last year: Creating a Google Form.)

Once the Form is created, go to the live form and copy the URL from the live form. Then on the page that you want to embed the Form, click Insert->SpreadSheet Form (it is in the 2nd column on the bottom):
From there in the popup window, pick Forms:
You should see the Form you just created in the list and you just need to select it. You can edit the properties of how it will look in the Google site page. I like to leave the Width blank and then make the Height enough to get a view of the form.
Now once you hit SAVE, you will go back to the Site page you are editing and your Form will be in a greyed out box. 
You will see the final formatting of the form embed once you hit SAVE from the editing controls on the upper right of the screen.
Now your form will be embedded in the Google Site. Student responses that are submitted will automatically populate the associated spreadsheet.

This is where the power of FormEmailer will come in!

FormEmailer is a JavaScript script that was created by Henry Abreau (Info on formEmailer)
You need to install this script in your spreadsheet. Click on Tools->Script gallery...


Search the Public gallery for formEmailer and Install the first result:

Since FormEmailer is going to do some stuff on your Spreadsheet you need to Authorize it to do so.
After clicking on Authorize, a window will pop open confirming your response. Upon closing that, FormEmailer will be installed in your Spreadsheet and evident in the Tabs at the top.

Before you run the script the first time on your spreadsheet, you need to Install it.

This will create a new column all the way on the left called Emailer Status and a new sheet called FormEmailer. 
Now you need to create the email that will be sent based upon the data in the sheet. I wanted to email the students the confirmation of their chosen Powerball ticket. I clicked on the FormEmailer tab and chose Settings to popup the creation window.
The popup window is where I construct the email message with the "placeholders" that will be replaced by data from each of the student responses, in this case the numbers they chose for their Powerball entry.

Once I have constructed the message, I need to process the emails. I put my own data in the first row of data (row 2) to test out the email message.
So to process an email from row 2, click on the FormEmailer tab and Process Manually.
Here is the result in my Inbox:

With the following message:


I find this to be a powerful way to communicate with students with customized data!

My next post will be to detail the usage of formulas in the spreadsheet to "grade" the students and perform more complex tasks.