Quizzes, Google Forms and Short Answer Questions

Using Google Forms to give multiple choice quizzes, grade them, and send results is relatively easy using floobaroo or the built in quiz function of forms. Doing the same with short answer questions, however, isn’t quite as straightforward. Here’s how I’ve started doing it. This is pretty nerdy, but if you follow the instructions, I think it will work for you. When I learn how I’ll publish the script as an add-on to take out the part many will find unfamiliar.

1. Create your quiz in Google Forms. If you’re going to use my grading script later on, there are a few things you have to do when you first create the form.
   – The first three questions in order must be first name, last name, and class period (ask what hour they have your class).
   – Click the gear at the top of the screen, and check “collect email addresses.”
2. After students have taken the quiz, open up the form, and click on responses
3. Click the Sheets logo.
4. It may ask you to create a new sheet if you haven’t done so for this form.
5. Change the background color of any cells with incorrect responses (any color will work, I choose red). It’s not necessary, but I like to sort the sheet by each question. To do this, first go to the View Menu, choose freeze, then one row. Click on any cell in the column for your first question, go to the data menu, and select “Sort sheet by column _ (a-z). This will group most of the correct responses together, making it easy to select incorrect answers as a group and change their background at once. It also lets me notice if a lot of students gave the same incorrect response meaning there is some confusion I might want to go over with the class. I then repeat the same process for each question column.
6. Click the top left corner of the spreadsheet (left of A and above row 1) to select all cells. Go to the data menu, choose “sort range…”, check “data has header row.” Sort by period, then last name, then first name (or however you want them sorted. This sort makes it easiest for me to enter scores our gradebook).
7. Here’s where some magic happens if you trust me. Go to the tools menu and select “script editor.” Delete the part where it has

   function myFunction() {
   
   }
   

   and in it’s place, copy and paste everything in the block below

   /**
    * @OnlyCurrentDoc
    */
   
   
   function onOpen(e) {
    SpreadsheetApp.getUi().createMenu('Red Pen Grader')
        .addItem('Initialize Settings', 'initializeSettingsSheet')
        .addItem('Initialize Grades Sheet', 'createGradeSheet')
        .addItem('Recalculate Grades', 'recalculateGrades')
        .addItem('Email Grades', 'sendGrades').addToUi();
   }
   
   function onInstall(e) {
     onOpen(e);
     initializeSettingsSheet();
   }
   
   
   function createGradeSheet() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var gradeSheet = ss.getSheetByName('Grades');
     if (gradeSheet != null) {
       ss.deleteSheet(gradeSheet);
     }
     var gradeSheet = ss.insertSheet('Grades',2);
     
     var responseSheetName = 'Form Responses 1';
     var indirectColumnOne = 'B';
     var indirectLastColumn = 'E';
     var indirectReferenceString = "\'" + responseSheetName + "\'!" + indirectColumnOne + ":" + indirectLastColumn;
     gradeSheet.getRange(1,1).setFormula("=INDIRECT(\"" + indirectReferenceString +"\")");
     
     gradeSheet.getRange(1,5).setFormula("=grades(Settings!$A$7)");
   
     gradeSheet.getRange(1,8).setValue('Sent');
     
     gradeSheet.getRange("G:G").setNumberFormat('##0.##%');
     
   }
   
   function initializeSettingsSheet() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var settingsSheet = ss.getSheetByName('Settings');
     if (settingsSheet != null) {
       ss.deleteSheet(settingsSheet);
     }
     var settingsSheet = ss.insertSheet('Settings');
     
     settingsSheet.setColumnWidth(1, 180);
     settingsSheet.setColumnWidth(2, 500);
     
     var maxColumns = settingsSheet.getMaxColumns();
     settingsSheet.deleteColumns(3,maxColumns -2);
     var maxRows = settingsSheet.getMaxRows();
     settingsSheet.deleteRows(8,maxRows -7);
     
     settingsSheet.hideRows(7);
     settingsSheet.hideRows(6);
     settingsSheet.hideRows(5);
     
     settingsSheet.getRange(1,1).setValue('Assignment Name');
     settingsSheet.getRange(2,1).setValue('Points Per Question');
     settingsSheet.getRange(3,1).setValue('First Question Column');
     settingsSheet.getRange(4,1).setValue('Last Question Column');
     settingsSheet.getRange(5,1).setValue('Number of Questions');
     settingsSheet.getRange(6,1).setValue('Incorrect Response Color');
     settingsSheet.getRange(6,2).setBackground('#ff0000');
   }
   
   function recalculateGrades() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var settingsSheet = ss.getSheetByName('Settings');
     if (settingsSheet == null) {
       return;
     }
     settingsSheet.getRange(7,1).setValue(Date.now());
   }
   
   
   
   
   function sendGrades() {
     recalculateGrades();
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var gradeSheet = ss.getSheetByName('Grades');
     if (gradeSheet == null) return "No grade sheet exists";
     var settingsSheet = ss.getSheetByName('Settings');
     if (settingsSheet == null) return "No settings sheet exists";
     var responsesSheet = ss.getSheetByName('Form Responses 1');
     if (settingsSheet == null) return "No response sheet exists";
     var firstQuestionColumn = settingsSheet.getRange(3,2).getValue();
     if (firstQuestionColumn.length < 1) return "first question column not defined";
     var lastQuestionColumn = settingsSheet.getRange(4,2).getValue();
     if (lastQuestionColumn.length < 1) return "last question column not defined";
     var pointsPerQuestion = settingsSheet.getRange(2,2).getValue();
     if (pointsPerQuestion.isNaN) return "points per question not defined";
     var last = responsesSheet.getLastRow();
     var assignmentName = settingsSheet.getRange(1,2).getValue();
     var subject = "Grade for " + assignmentName;
     var numQuestions = responsesSheet.getRange(firstQuestionColumn + 1 + ":" + lastQuestionColumn).getWidth();
     var possiblePoints = numQuestions * pointsPerQuestion;
     
     for (var i=2; i<last+1; i++) {
       if (gradeSheet.getRange(i,8).getValue() == 'x') {
         continue;
       }
       if (gradeSheet.getRange(i,1).isBlank()) {
         continue;
       }
   
       // If you did not ask for first name in a question, comment out the line below with //
       var fName = gradeSheet.getRange(i,2).getValue();
       var studentAddress = gradeSheet.getRange(i,1).getValue();
       var pointsEarned = gradeSheet.getRange(i,6).getValue();
       
       // If you did not ask for first name in a question, comment out the first line below, and uncomment the second
       var message = 'Dear ' + fName + ', \n';
       //var message = 'Dear Student, \n';
   
   
       var message = message + 'On the assignment ' + assignmentName;
       var message = message + ' you earned ' + pointsEarned + ' out of ' + possiblePoints + ' possible points.';
       GmailApp.sendEmail(studentAddress, subject, message);
       gradeSheet.getRange(i,8).setValue('x');
     }
   }
   
   function grades() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var gradeSheet = ss.getSheetByName('Grades');
     if (gradeSheet == null) return "No grade sheet exists";
     var settingsSheet = ss.getSheetByName('Settings');
     if (settingsSheet == null) return "No settings sheet exists";
     var responsesSheet = ss.getSheetByName('Form Responses 1');
     if (settingsSheet == null) return "No response sheet exists";
     var firstQuestionColumn = settingsSheet.getRange(3,2).getValue();
     if (firstQuestionColumn.length < 1) return "first question column not defined";
     var lastQuestionColumn = settingsSheet.getRange(4,2).getValue();
     if (lastQuestionColumn.length < 1) return "last question column not defined";
     var pointsPerQuestion = settingsSheet.getRange(2,2).getValue();
     if (pointsPerQuestion.isNaN) return "points per question not defined";
     
     var lastSubmission = responsesSheet.getLastRow();
     
     var returnArray = [["Num Correct", "Points Earned", "Percentage"]];
     for (i=2; i <= lastSubmission; i++) {
       var answerRangeString = firstQuestionColumn + i + ":" + lastQuestionColumn + i;
       var gradeRange = responsesSheet.getRange(answerRangeString);
       var bgColors = gradeRange.getBackgrounds();
       var numRight = 0;
       for (j in bgColors[0]) {
         if (bgColors[0][j] == "#ffffff") {
           numRight++;
         }
       }
       var pointsEarned = numRight * pointsPerQuestion;
       var percentage = numRight / bgColors[0].length;
       returnArray.push([numRight, pointsEarned, percentage]);
     }
     return returnArray;
   }
   

8. In the toolbar of the script window, click the disk to save it. Where it says "Choose function" select "On install" then click the play button just to the left. It may ask for permission, if so, tell it allow. Don't worry, you only have to mess with the code window once. You can now close the code window.
9. Back on the sheet with your student answers, you'll notice a new tab for a sheet at the bottom of the window that says "Settings." Click on that tab. Enter the name of the assignment and the number of points you want each question to be worth. You may need to refer back to the sheet "Form Responses 1" and find the column for the first actual graded question and the column for the last graded question. You're looking for the letter above each column. Enter those letters on the settings sheet.
10. Go to the Add-Ons menu, choose "Red Pen Grader" then choose "Initialize Grades Sheet."
11. Open the newly made "Grades" sheet and you'll be able to see the scores for each student based on the grading you did back on step 5. It counts any cell with a background color that isn't white as wrong.
12. To email scores to your students, click on the Red Pen Grader menu then click Email Grades.