Using Google Forms for assessment

In today's post I am going to explain how to save a little time using the Flubaroo script to autograde close-ended questions a teacher poses to students using Google Forms as the assessment tool.

If you would like to learn more about the Flubaroo script, click here.

PART I

This video explains how to create a quiz using Google Forms.





PART II

This video explains how to install the Flubaroo script.





PART III

This video explains how to use the installed Flubaroo script to autograde selected questions from your Google form.






PART IV

This is an advanced tutorial on how to add script to create an email menu so that you can email comments directly to each student. Note: this script does not send out an email to all students, only the student whose row is active at the time when you click on the email menu.


Below is the javascript code for adding a script to be able to email students information / assessment comments directly from the spreadsheet. I have added a picture below, as well, to help depict the elements of the code I am explaining hereafter.

Before I launch any further into how to use the code, we need to consider why this code might be useful to a teacher.  This code could be useful if ever a teacher has different items that need to be emailed to multiple students; instead of having to compose multiple different emails, the teacher could compose the text in the spreadsheet and email each individual student out of the spreadsheet. Second (and closer to our purposes for quiz assessment) a teacher may want to consider using the code if that teacher uses Google form quizzes to ask students to fill in short, text answers to questions. Pedagogically, we know that we are not necessarily getting the truest assessment picture of a student if we only ask multiple choice and true/false questions, so one may consider adding the occasional short answer question.

We see from the above videos that the Flubaroo script will automatically grade the multiple choice, fill-in-the-blank, and/or true/false type questions, but for questions where students have wider liberty in their response, the questions will most likely be ungraded.

This is where the email script can help. Create an answer column for each short answer question, then edit the script below to email each student the comment you have written in the corresponding answer cell. If, for example, I had two short answer questions I wanted to provide feedback for, I could add this code to the script below -- body += "\n\nText you want to appear in email" + sheet.getRange(row, getColIndexByName("Exact text of column you want emailed")).getValue(); -- (Note -- are not part of the script).

So there you have it, a shortcut for being able to provide short answer feedback via email.

If you have further questions, you may find me in the Tech Office or walking the halls. Thanks for your time,

Matt

function getColIndexByName(colName) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numColumns = sheet.getLastColumn();
  var row = sheet.getRange(1, 1, 1, numColumns).getValues();
  for (i in row[0]) {
    var name = row[0][i];
    if (name == colName) {
      return parseInt(i) + 1;
    }
  }
  return -1;
}
function emailStatusUpdates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRowIndex();
  var userEmail = sheet.getRange(row, getColIndexByName("Username")).getValue();
  var subject = "Quiz Comments";
  var body = "Comments from your latest quiz \n\nComments: " + sheet.getRange(row, getColIndexByName("Comments")).getValue();
  body += "\n\nDescribe why the above color is your favorite.(Teacher Corrections): " + sheet.getRange(row, getColIndexByName("Assessment for question 2")).getValue();
  

  MailApp.sendEmail(userEmail, subject, body, {name:"Color Quiz Text Answer Results"});
}

function onOpen() {
  var subMenus = [{name:"Send Status Email", functionName: "emailStatusUpdates"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Email Menu", subMenus);
}