Quickvisit/Walkthrough Form with Email Using Google Docs

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.

15 comments

  1. 0
    Chris Dennison says:

    Hi, many thanks for this template. I work as an IT technician in an english secondary school – ages 11-16 and I was asked to find something for the senior team to use for quick assessments.
    If I may make a contribution, if you have a spreadsheet with all of your teacher initials (or whatever preceeds the email domain) then this can be copied and pasted into the drop down list. I then added the domain to the email parameter “@domain.co.uk”.
    Saved a lot of typing.
    Thanks again

  2. 0
    Tom says:

    Colin,
    I have been working on this for a while. I followed your steps but the email still won’t work. I get this message a day later. The information goes to the form here is the message I get….

    sendEmails Please select an active sheet first. (line 4, file “Code”) time-based 9/7/15 8:21 PM

  3. 0
    eric says:

    I can not seem to create this on ipad as your directions stated. I do not see create from template

  4. 0
    Colin Matheson ( User Karma: 0 ) says:

    Hi Chris,
    Sorry I didn’t see your comment until months later. You will need to edit the script under Tools->Script Editor. You will then need to edit the information after line 21. The text is written in HTML and then it gets the data by adding an item from the array variable. For example:

    Predominant grouping of the students
    ” + array[0][5] +

    Will pull data from the 6th question column of the spreadsheet (computers start counting at 0). So change the text between the quotes to match what you asked in the form.

  5. 0
    Chris says:

    Thanks Colin! I have one more question. I took the Google document that you created and manipulated it to fit our needs. It changed the walkthrough form on the actual form and the spreadsheet as well, but when I e-mail it the teachers and myself it is the original document that you created. How do I change this? sorry, technology is on my growth plan yearly!

  6. 0

    Hi Chris,
    There are 9 steps under the Turn On Script Trigger step. Which one are you getting stuck on?

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

  7. 0
    Chris says:

    Thanks for the form! I have no problem with editing the form to fit my districts needs, reviewing the data on the spreadsheet, and etc. Of course, I think most us have problems sharing or e-mailing the individual walkthrough with teachers in a timely manner. In your steps above, how do I turn on the script trigger? I apologize for my technology incompetence?

  8. 0
    Laura says:

    Colin,
    Thank you for creating this walkthrough form. I have made some changes to the form but I am not sure how to make sure it saves to my google documents and erases all of the old data so we can begin collecting our own. This is only my second google document so if you could help me that would be great!

  9. 0

    Colin,
    Thanks for getting back with me so quickly. I was wondering if having one person make the modifications and then share with others who in turn would make a copy would work. (By the same token, I would think making a copy for each school, sharing it, and then transferring ownership to them would work as well.)

    I was not sure what would happen with the part about turning on the script trigger…whether the modified script would be what is turned on as opposed to the original script. From your answer, looks like that is not a problem.

  10. 0
    Colin ( User Karma: 0 ) says:

    Hi Frank,
    There are a couple of solutions for a district wanting to share a customized walkthrough form. One person should first customize the form and the script (and test it to make sure everything works). Then they can submit that form to the Google template gallery (either the public gallery or their internal Google Apps gallery), but right clicking on the document in the document list and choosing Submit to template gallery. Then other admins choose Create->From Template in Google docs and find the customized walkthrough form. Another way is to have the original editor just share it with the other admin staff and then they choose File->Make a copy and they would then have their own copy. After that they just need to customize the staff list and authorize the script to send emails. What I did for our district was set up each site as a copy of my original, did the customization/authorization and then when it was all ready, shared it with the admin. In fact at first I just sent them the link to the form and didn’t even get into the spreadsheet side of things until the end of the year when they wanted to do some analysis.

  11. 0

    Colin,
    This is great. I have been showing showing administrators how to use GoogleDocs to conduct classroom walkthroughs on a tablet for some time. The common question is how to provide feedback to teachers. You have provided that valuable missing link.

    I have one question. Suppose a district had its own particular questions, and all administrators in that district use that same set of questions.You have provided instructions on how to modify the script, and one option would be that each administrator would copy your public template, modify the questions, and modify the script. Is there a more efficient way. If one administrator modified the questions and script, can you tell me what he/she could then do to so that other administrators would be able to use the modified form and script?

  12. 0
    Colin ( User Karma: 0 ) says:

    Hi Erika. Here is a link that should get you to the template
    https://drive.google.com/templates?q=quickvisit&sort=hottest&view=public

    Here is the script as well-

    function sendEmails() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = ss.getSheets()[0];
    var last = ss.getLastRow();
    //var dataRange = dataSheet.getRange(last,2,1,21);
    //this pulls the most recently submitted row
    var dataRange = dataSheet.getRange(last,2,1,40);
    var templateSheet = ss.getSheets()[0];

    var emailTemplate;// = templateSheet.getRange(“A1”).getValue();

    // Create one JavaScript object per row of data.
    //objects = getRowsData(dataSheet, dataRange);

    // For every row object, create a personalized email from a template and send
    // it to the appropriate person.
    //for (var i = 0; i < objects.length; ++i) { // Get a row object //var rowData = objects[0]; var array = dataRange.getValues(); emailTemplate = "Hello, " + array[0][0] + " ” + array[0][1] + ” visited your class and observed the following:
    ” +

    Period: ” + array[0][2] +

    Grade: ” + array[0][3] +

    Subject: ” + array[0][4] +

    Activity taking place?
    ” + array[0][21] +

    Predominant grouping of the students
    ” + array[0][5] +

    Approximate % of students engaged:
    ” + array[0][6] +

    What is the cognitive complexity of the the work?
    ” +
    Work that requires the learner to:
    ” + array[0][7] +

    INSTRUCTIONAL NORMS” +

    Evidence of established learning objective: ” + array[0][8] +

    Evidence of a variety of instructional strategies to include critical thinking: ” + array[0][10] +


    Evidence of teaching academic vocabulary: ” + array[0][11] +

    Evidence of differentiated instruction: ” + array[0][12] +

    Evidence of checks for understanding: ” + array[0][13] +

    Evidence of closure: ” + array[0][20] +

    Is technology being used to support the educational activity?
    ” + array[0][14] +

    Students knew the objective:
    ” + array[0][16] +

    Students knew how their work would be assessed?
    ” + array[0][17] +

    Comments:
    ” + array[0][18];

    // Generate a personalized email.
    // Given a template string, replace markers (for instance ${“First Name”}) with
    // the corresponding value in a row object (for instance rowData.firstName).
    //var emailText = fillInTemplateFromObject(emailTemplate, rowData);
    var emailSubject = “Quick Visit by ” + array[0][1];

    //MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
    var advancedArgs = {name:array[0][1], replyTo:array[0][1], htmlBody:emailTemplate};
    //cal.createEvent(title, startDate, endDate, advancedArgs);
    if(array[0][22]==”Yes”){
    MailApp.sendEmail(array[0][0], emailSubject, emailTemplate, advancedArgs); //mail to teacher
    }
    emailTemplate += “

    Admin Notes (not in teacher email):
    ” + array[0][19]; //add private notes and email to admin
    if(array[0][22]==”Yes”){
    emailSubject = “Admin Copy of your Quick Visit of ” + array[0][0];
    emailTemplate = “This is a copy of the quick visit email sent to the teacher plus any private notes you made
    ” + emailTemplate;
    }
    else{
    emailSubject = “Your Quick Visit of ” + array[0][0];
    emailTemplate = “This is a summary of your quickvisit. The teacher was NOT emailed this information
    ” + emailTemplate;
    }
    advancedArgs = {name:array[0][1], replyTo:array[0][1], htmlBody:emailTemplate};
    if(array[0][1]==”Other”){
    MailApp.sendEmail(array[0][23], emailSubject, emailTemplate, advancedArgs);
    }
    else{
    MailApp.sendEmail(array[0][1], emailSubject, emailTemplate, advancedArgs); //mail to admin
    }
    }

    // Replaces markers in a template string with values define in a JavaScript data object.
    // Arguments:
    // – template: string containing markers, for instance ${“Column name”}
    // – data: JavaScript object with values to that will replace markers. For instance
    // data.columnName will replace marker ${“Column name”}
    // Returns a string without markers. If no data is found to replace a marker, it is
    // simply removed.
    function fillInTemplateFromObject(template, data) {
    var email = template;
    // Search for all the variables to be replaced, for instance ${“Column name”}
    var templateVars = template.match(/\$\{\”[^\”]+\”\}/g);

    // Replace variables from the template with the actual values from the data object.
    // If no value is available, replace with the empty string.
    for (var i = 0; i < templateVars.length; ++i) { // normalizeHeader ignores ${"} so we can call it directly here. var variableData = data[normalizeHeader(templateVars[i])]; email = email.replace(templateVars[i], variableData || ""); } return email; } ////////////////////////////////////////////////////////////////////////////////////////// // // The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects' // tutorial. // ////////////////////////////////////////////////////////////////////////////////////////// // getRowsData iterates row by row in the input range and returns an array of objects. // Each object contains all the data for a given row, indexed by its normalized column name. // Arguments: // - sheet: the sheet object that contains the data to be processed // - range: the exact range of cells where the data is stored // - columnHeadersRowIndex: specifies the row number where the column names are stored. // This argument is optional and it defaults to the row immediately above range; // Returns an Array of objects. function getRowsData(sheet, range, columnHeadersRowIndex) { columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1; var numColumns = range.getEndColumn() - range.getColumn() + 1; var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns); var headers = headersRange.getValues()[0]; return getObjects(range.getValues(), normalizeHeaders(headers)); } // For every row of data in data, generates an object that contains the data. Names of // object fields are defined in keys. // Arguments: // - data: JavaScript 2d array // - keys: Array of Strings that define the property names for the objects to create function getObjects(data, keys) { var objects = []; for (var i = 0; i < data.length; ++i) { var object = {}; var hasData = false; for (var j = 0; j < data[i].length; ++j) { var cellData = data[i][j]; if (isCellEmpty(cellData)) { continue; } object[keys[j]] = cellData; hasData = true; } if (hasData) { objects.push(object); } } return objects; } // Returns an Array of normalized Strings. // Arguments: // - headers: Array of Strings to normalize function normalizeHeaders(headers) { var keys = []; for (var i = 0; i < headers.length; ++i) { var key = normalizeHeader(headers[i]); if (key.length > 0) {
    keys.push(key);
    }
    }
    return keys;
    }

    // Normalizes a string, by removing all alphanumeric characters and using mixed case
    // to separate words. The output will always start with a lower case letter.
    // This function is designed to produce JavaScript object property names.
    // Arguments:
    // – header: string to normalize
    // Examples:
    // “First Name” -> “firstName”
    // “Market Cap (millions) -> “marketCapMillions
    // “1 number at the beginning is ignored” -> “numberAtTheBeginningIsIgnored”
    function normalizeHeader(header) {
    var key = “”;
    var upperCase = false;
    for (var i = 0; i < header.length; ++i) { var letter = header[i]; if (letter == " " && key.length > 0) {
    upperCase = true;
    continue;
    }
    if (!isAlnum(letter)) {
    continue;
    }
    if (key.length == 0 && isDigit(letter)) {
    continue; // first character must be a letter
    }
    if (upperCase) {
    upperCase = false;
    key += letter.toUpperCase();
    } else {
    key += letter.toLowerCase();
    }
    }
    return key;
    }

    // Returns true if the cell where cellData was read from is empty.
    // Arguments:
    // – cellData: string
    function isCellEmpty(cellData) {
    return typeof(cellData) == “string” && cellData == “”;
    }

    // Returns true if the character char is alphabetical, false otherwise.
    function isAlnum(char) {
    return char >= ‘A’ && char < = 'Z' || char >= ‘a’ && char < = 'z' || isDigit(char); } // Returns true if the character char is a digit, false otherwise. function isDigit(char) { return char >= ‘0’ && char <= ‘9’; }

  13. 0
    erika says:

    I need this but I can’t find your template in public templates? Help! I’ve been looking for the script. Even if you can provide me with the script that would be helpful

  14. 0
    pat says:

    Love the form but I need to use other information in the form – is there a way to edit the obsrvation information and still maintain the email feature?

Leave a Reply