Reads all Form Result and check if it matches the values in the sheet called Answers

check_sheet_exam.gs

/**
 * Reads all Form Result and check if it matches the values in the sheet called Answers
 * After a successful evaluation, results sheet is created
 */


// SETTINGS
var QUESTION_COLUMN_START = 2;
var QUESTION_COUNT = 16;
// NO NEED TO EDIT BEYON THIS POINT, OR SO I THOUGHT...




function readRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var resultSheetName = "Results";
  var answerSheetName = "Answers";
  
  // setup result sheet
  var resultSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(resultSheetName)
  if(resultSheet==null)
    resultSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(resultSheetName);
  else
    resultSheet.clear();
    
  resultSheet.getRange(1,1).setValue("Name");
  resultSheet.getRange(1,2).setValue("Score");
  resultSheet.getRange(1,3).setValue("Remarks");
  resultSheet.getRange(1,4).setValue("Mistakes");
  
  var sheetAns = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(answerSheetName);

  
  // fill 'em up
  for (var i = 1; i <= numRows - 1; i++) {
    var row = values[i];
    var correct = 0;
    var totalPoints = 0;
    var mistakes = "";
    var start = QUESTION_COLUMN_START;
    var qCount = QUESTION_COUNT;
    if(sheetAns!=null){
        for(var r=start;r<start+qCount;r++){
          Logger.log(row[r]+' == '+sheetAns.getRange(r-start+1,1).getValue());
          totalPoints+=sheetAns.getRange(r-start+1,2).getValue();
          if(row[r]==sheetAns.getRange(r-start+1,1).getValue()){
            correct+=sheetAns.getRange(r-start+1,2).getValue();
          }else{
            mistakes+=(r-1)+",";
          }
        }
        
     
      
    }else{
      SpreadsheetApp.getActiveSpreadsheet().toast("No answer key sheet!");
    }
    


    
    
  
    // set name
    resultSheet.getRange(i+1,1).setValue(row[1]);
    // set Score
    resultSheet.getRange(i+1,2).setValue((correct/totalPoints*100)+"%");
    resultSheet.getRange(i+1,3).setValue(correct/totalPoints>=.75?"PASSED":"FAILED");
    resultSheet.getRange(i+1,4).setValue(mistakes);
    mistakes

  }
};

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Calculate Scores",
    functionName : "readRows"
  }];
  spreadsheet.addMenu("Exam Result Checker", entries);
};