Using Google to Manage your SBG Workflow - Google Forms Script

This is from a session at TMC13 to help you use Google to manage all the tasks that may overwhelm you. Instead of creating my own resource which may quickly go out of date as Google updates their products, you will notice I mainly link you to Google created resources/tutorials.

We will look at using Google Scripts to automate some functions, specifically a way to have submissions to a Google Form emailed directly to you instead of you having to go to the spreadsheet to view them. Yep, no more going cross eyed trying to read the submitted responses on a spread sheet.  Instead, receive an email with the submitted response that looks like any normal email. You can even reply to the student using that email. See an overview of the process below.

Before I begin, there are a variety of scripts that you really need to check out. The top ones I would recommend you play with are:

• gClassFolders - Easily and quickly create class folders for you and your students
• Doctopus - Great for organizing/managing student work and projects your students are doing in Google Apps.

One of the things that can be most time consuming with Standards Based Grading is reassessments. With my extra duties and the usual items that pop up out of nowhere, it is difficult to find a consistent time I was available for students. At the same time I also did not want reassessments to become somethings students just took at random to "see what happens this time". I needed a way to do the cliche kill two birds with one stone, stay organized and make reassessments meaningful for students. 

My first attempt was to use an email template created by Sam that students would use to request a reassessment. For some reason many students had trouble figuring out what they needed to copy and paste from the template into the email and what they were supposed to write themselves. 

My next attempt was to use a Google form just like Bowman's. Perfect, now students don't have to do any copying and pasting for an email and just fill out the form. Then I realized I needed to go to the spreadsheet to view the submissions.  I did some searching and came across a script that would do an email merge of Google form submissions. Brilliant, no more having to look at a spreadsheet of form submissions!

Google Form Script How to Guide

Step 1: Create a Google Form

Create your Google form. You can use Bowman's as a guide or mine. After creating the form, create the spreadsheet to hold the responses. 

Our school district is a GAFE (Google Apps for Education) district. In my form I had an option to force the student to first sign in to their school Google account so that the form would automatically collect their email address. This is nice in that it helps prevent the silly students who have a hard time typing their own email address. If your school is not a GAFE school, make sure you have a field in your form that asks for their email address.

Step 2: Share the form and response spreadsheet with a second Gmail account

The best part about the script is that I can reply to the email and it will be sent to the student who submitted the form. However, the reply does not work if you are getting the script emails from the same Gmail account used to set up the script. In other words, I set up the Google form and script using my someone@gmail.com account. I told the script to send the emails to my someone@gmail.com account. When I try to send a reply to the student, the email address that Gmail wants to send it to is incorrect (if interested, you can read why). The work around is to share the form and spreadsheet you created with a second Google account and use that account when setting up the script.

I created the form using my school Google account. I also have a personal Google account but I did not want to use that account. Whatever second Google account you use, that email address will be in the email sent to you by the script. To keep my personal Google account private, I created another generic Google account to use to set up the script.

Step 3: Install the script

The script being used is called FormEmailer. Using your second Google account, open the response spreadsheet. Follow the instructions given by the FormEmailer creator to install the script.

Step 4: Set up the script template

You can choose what data from the form submission to be included in the email sent to you. You can also create a template in the script so that the data is sent to you in a pleasant to read kind of why. I set up my template to make it look like the student was actually sending me an email. If you know some basic html, your template can include bolded text, etc.

Until I make my own directions to show you my settings, the video below does a decent job of showing you an overview of the process. The first 3 minutes shows you how to install the script. From about the 3 minute mark to about the 5 minute mark, it shows you how to set up the template.

Step 5: Set up the script trigger
You have two options to make the script run:

1. Manually run the script - aka you have to open the spreadsheet and tell the script to send out the emails (pffftt)
2. Have the script automatically run - Ding ding ding, we have a winner!

You can set the script to check for any submissions and send you an email for each of the submissions automatically. You have the option to set the trigger to occur as soon as someone submits a form or be time driven (set it for once a day, once an hour or even once a minute).

Let's pretend you have it set to once a day. Say you had three students submit a reassessment on a particular day. The script will check the response spreadsheet, see that you have three submissions and then send you each submission in a separate email based on the settings in your template.

To view how to set up a trigger, see the video below from about 4:40 to 6:05.

Step 6: Test
Go pretend to be a student and fill out your form. Look at the response spreadsheet to see your pretend responses and to see the script to mark your submission as "Email sent". Note: You will want to change the trigger to something like every minute, otherwise you will be waiting a while. Then go to your email to view it. If you don't like the formatting, go back to the script template in the settings to modify it.

Once you have it tweaked to your liking, you are ready to go live.


Optional:
You can send an email to the student as well. You have the email sent to you as you set up using the above instructions, in the script you would set up a second email template that would go to the student (person who submitted the form). Even though a Google form has that confirmation page that appears after the student submits the form, for some reason I have found students are still worried that I saw their submission and will ask in class the next day. When they get a confirmation email, that seems to calm their fears.

For directions on how to set up a second email, see the video in step 5. Skip to about 3:28 until 4:40.