GoogleScript function calling ALL values vs the row that was just changed

Question

The function below calls and sends an email for ALL rows that are marked "Closed"; but I want it to only email the one that gets marked "Closed"; not older, previously closed rows and I don't know how to correct this, please help.

        function sendEmails() {
           var sheet = SpreadsheetApp.getActiveSheet();
           var startRow = 3;
           var numRows = 5000;
           var dataRange = sheet.getRange(startRow, 1, numRows, 5000);
           var data = dataRange.getValues();
           var FinalMessage;
              for (var i in data) {
              var row = data[i];
              if (row.includes("Closed")){
              // Logger.log("CLOSED" + row);
  
           var emailAddress = ""
           var TSRNumber = row[19];
           var IssueType = row[4];
           var Customer = row[5];
           var TankCode = row[13];
           var City = row[9];
           var State = row[10];
           var Region = row[0];
           var Terminal = row[1];

       switch (Terminal) {
        case "Riga MI":
        emailAddress = "[email protected]";
            break;
        case "Other":
        default:
        emailAddress = "[email protected]";
        break;
        }

           var subject = "CLOSED - TSR #" + TSRNumber + " for " + Customer + " in " + City + " " + State                                  
           + " ( " + Region + " )";  
           var message = "TSR # " + TSRNumber + " for " + Customer + " in " + City + " " + State + " ( " 
           + Region + " ) " + "is now Closed" +'\n' +'\n' + "Link to TSR Database: " + 
           "https://xxxxx.com"

          MailApp.sendEmail(emailAddress, "[email protected]", subject, message); 

      }
  }
}

Answer 1

You would need an installable trigger for this scenario:

function createTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('sendEmail').forSpreadsheet(ss).onEdit().create();
}

This function, when executed manually, will create a trigger for the spreadsheet that will fire when a cell is edited.

Then the sendEmail function itself would then read the edited cell, validate, and fill out the fields as usual:

function sendEmail(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  if ((e.range.getRow() >= 3) && (e.range.getValue() === "Closed")) {
       var row = sheet.getRange(e.range.getRow(),1,1,20).getValues();
       var emailAddress = ""
       var TSRNumber = row[19];
       var IssueType = row[4];
       var Customer = row[5];
       var TankCode = row[13];
       var City = row[9];
       var State = row[10];
       var Region = row[0];
       var Terminal = row[1];

       switch (Terminal) {
         case "Riga MI":
           emailAddress = "[email protected]";
           break;
         case "Other":
         default:
           emailAddress = "[email protected]";
           break;
       }

       var subject = "CLOSED - TSR #" + TSRNumber + " for " + Customer + " in " + City + " " + State                                  
       + " ( " + Region + " )";  
       var message = "TSR # " + TSRNumber + " for " + Customer + " in " + City + " " + State + " ( " 
       + Region + " ) " + "is now Closed" +'\n' +'\n' + "Link to TSR Database: " + 
       "https://xxxxx.com"

       MailApp.sendEmail(emailAddress, "[email protected]", subject, message); 
  }
}

References:

Installable Triggers