Posts Tagged ‘google docs’

Quickvisit/Walkthrough Form with Email Using Google Docs

Friday, August 3rd, 2012

Google docs is a great way to great forms for doing classroom observations. However, the information in the form just goes to a spreadsheet which isn’t very useful for providing teacher feedback. By copying a template in Google Docs you can have a quickvisit form which emails the teacher and observer an email with the information from the form in a nice human readable format.

Step 1: Copy the template

  1. In google docs/drive choose Create->From Template
  2. Choose Public templates
  3. Search for quickvisit
  4. You should see CUSD Quickvisit Walkthrough with Email by Colin Matheson click Use this template

Step 2: Add your staff emails

  1. Hover over the teacher dropdown and click the pencil icon that appears in the far right
  2. Enter the email addresses of your teachers (you have to do this one at a time)
  3. If that seems like too much work for now you could change the Question type to Text and then type in the email address at the time of the walkthrough, however by doing a little up front work enter emails in the form you will save time and prevent typing errors later
  4. You should also enter the emails of the main people doing the observations in the Observer question

Step 3: Turn on the script trigger

  1. Close the editing form window/tab and go back to your document list
  2. Click on the spreadsheet which is attached to the form (by default it is called “Copy of CUSD Quickvisit Walkthrough with Email”
  3. Go to Tools->Script editor
  4. A new window/tab will open up, in that window choose Resources->Current script triggers
  5. A popup dialogue will appear which says “No triggers set up. Click here to add one now.” Click on that link
  6. Change the third menu from “On spreadsheet open” to “On form submit” and choose Savescript trigger
  7. A big box saying Authorization Required will appear, click Authorize
  8. You will then have to click Save again to actually have the trigger save
  9. Close the Script editor window

Step 4: Try it out

  1. Go to your form web address by choosing Form->Go to live form
  2. Enter in some practice data and see if the emails are sent

Advanced Customization

So let’s say you like the concept of this form, but would like to customize the questions, the email being sent, or use the email feature on an entirely different form. Some elements you can alter by just editing the form, others will require you to also edit the script that sends the emails. In order to get emails to work with another form that you have already written you will need to copy and edit the scripts. You can get to the scripts from the Tools->Script Editor menu. The script is written in javascript so if you have someone with a bit of web/programming experience they can help edit it for you, but you don’t need programming experience to customize the script. I have italicized all pieces of code in the text below to help them stand out.

Simple Edits: The dropdown menu answer options can be changed and the new answer options will show up in the email.

Medium Edits: If you want to change the text of a question,first you need to edit the form to change the question and answers. Then you need to go to Tools->Script editor to change the text in the email to reflect the changes you made. The email text code starts on line 21, but first we should understand line 7:

var dataRange = dataSheet.getRange(last,2,1,25);

This gets columns 2 through 25 from the last line of the spreadsheet. So if you you have more or fewer questions you can alter those numbers. We don’t bother with column 1 because it is a time stamp. Later I load that info into a variable called array.

So starting on line 21 is the email
emailTemplate =
“Hello, ” + array[0][0]

Anything in quotes is actual text of the email. The array[0][0] means it takes the first bit of data from the spreadsheet (which is actually the second column). array[0][1] takes the info from the 0second question, array[0][13] takes info from the 13th question, etc. There is always a [0] before the actual number we are using, and the number is always in square brackets. If you add new questions to the google form, they will be added to the last column of the spreadsheet, so first you need to change the number 25 on line 7 var dataRange = dataSheet.getRange(last,2,1,25); and increase it to reflect the number of questions you added (if you added two questions then you should change 25 to 27. Then you need to add some text to the email and pull the data from the spreadsheet with array[0][25] to pull the first question you added and array[0][26] to pull the answer from the second question you added. If you delete questions from the form, google docs keeps them in the spreadsheet so you don’t have to alter any numbers in the array. You will want to remove the reference to the deleted question from the email text.

Also of note is that on line 60 the script checks to see if a box “Email to teacher” was checked. This happens to be the 22nd column on my spreadsheet. If it is Yes, then an email is sent to the teacher. If not, then just an admin email is sent.

if(array[0][22]==”Yes”){
Advanced Edits: To use these scripts on an entirely new form, you can select the entire text of the script and copy the text. Then in your form, go to Tools->Script editor, create a new blank script, and copy the text in. You will then have to adjust all of the text in the email and the numbers in the array[0][change this number to refer to the question you want]

Let me know if you have other questions about the script or need help. I have responded to several emails and calls with people looking for specific help already, so feel free to contact me directly.