Automatic GMail Grading (Google Script)

user_15695111 0
Viewed 44

The following code below is a sendEmail function that I use to send GMail messages using Google Script so students get feedback on their math tests. I create a test and the first list of values at the top are entered by me (see example of grading here). I want to mark the questions ("Incorrect") that are not equal to the row at the top.

Particularly the line of code that is giving me trouble is the following: if (Answer(j)!=row[j]) {row[j]=row[j]+" (Incorrect)";}

If I put Answer(5), it recognizes it as the first row at the top and fifth column over which is doing what it is supposed to. However, when I use the variable j to call Answer(j), it marks all of the questions as incorrect. Now, Answer(j) corresponds to row[j] which is what the student enters. I do not know what I am doing wrong, and I have been trying to mess around with the code for awhile but nothing seems to work. Any help would be super appreciated!

function sendEmail() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var startRow = 2; // First row of data to process
 var numRows = 5; // Number of rows to process
 var numQuestions = 17; //Number of Questions Asked on Assessment
 // Fetch the range of cells A2:B3
 var dataRange = sheet.getRange(startRow, 1, numRows, 25);
 // Fetch values for each row in the Range.
 var data = dataRange.getValues();
 for (var i = 0; i < data.length; ++i) {
   var row = data[i];
   var emailAddress = row[1]; // First column
   var message = row[0]+","+"\n"+"\n"+"Below is feedback on your assessment. Your score is a "+row[4]+".\n";
   for(var j = 4; j < numQuestions+4; ++j) {
     k=j-3;
     function Answer(n){
     return SpreadsheetApp.getActiveSheet().getRange(1, n).getValue();
     }
     if (Answer(j)!=row[j]) {row[j]=row[j]+" (Incorrect)";
     }
     message = message+"\n"+"Question #" +k+": "+row[j];
   }
   var emailSent = row[2]; // Third column
   if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
     var subject = 'Math Feedback';
     MailApp.sendEmail(emailAddress, subject, message);
     sheet.getRange(startRow + i, 2+1).setValue(EMAIL_SENT);
     // Make sure the cell is updated right away in case the script is interrupted
     SpreadsheetApp.flush();
   }
 }
}
google-apps-script google-sheets gmail
1 Answers

I thought that the reason of your issue might be due to the differences between the index of array (the 1st number is 0.) and the column number (the 1st number is 1.). In your script, j of for(var j = 4; j < numQuestions+4; ++j) { is used as the index of row. But at function Answer(n){return SpreadsheetApp.getActiveSheet().getRange(1, n).getValue();}, j is used as the column number. In this case, how about the following modification?

From:

return SpreadsheetApp.getActiveSheet().getRange(1, n).getValue();

To:

return SpreadsheetApp.getActiveSheet().getRange(1, n + 1).getValue();

Note:

  • In your script, when the header row and data rows are retrieved with one request by separating, the process cost might be able to be reduced. When this is reflected to your script, it becomes as follows.

      function sendEmail() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 1; // First row of data to process <--- Modified
    var numRows = 5; // Number of rows to process
    var numQuestions = 17; //Number of Questions Asked on Assessment
    // Fetch the range of cells A2:B3
    var dataRange = sheet.getRange(startRow, 1, numRows, 25);
    // Fetch values for each row in the Range.
    var [header, ...data] = dataRange.getValues(); <--- Modified
    for (var i = 0; i < data.length; ++i) {
      var row = data[i];
      var emailAddress = row[1]; // First column
      var message = row[0] + "," + "\n" + "\n" + "Below is feedback on your assessment. Your score is a " + row[4] + ".\n";
      for (var j = 4; j < numQuestions + 4; ++j) {
        k = j - 3;
        if (header[j] != row[j]) { <--- Modified
          row[j] = row[j] + " (Incorrect)";
        }
        message = message + "\n" + "Question #" + k + ": " + row[j];
      }
      var emailSent = row[2]; // Third column
      if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
        var subject = 'Math Feedback';
        MailApp.sendEmail(emailAddress, subject, message);
        sheet.getRange(startRow + i, 2 + 1).setValue(EMAIL_SENT);
        // Make sure the cell is updated right away in case the script is interrupted
        SpreadsheetApp.flush();
      }
    }
  }

Reference:

user_71086530
Related