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 = "xxxxxx@xxxx.com";
            break;
        case "Other":
        default:
        emailAddress = "xxxx@xxxx.com";
        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, "NO-REPLY@xxxxx.com", subject, message); 

      }
  }
}

CMB · Accepted Answer · 2020-12-17T18:11:24.483

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 = "xxxxxx@xxxx.com";
           break;
         case "Other":
         default:
           emailAddress = "xxxx@xxxx.com";
           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, "NO-REPLY@xxxxx.com", subject, message); 
  }
}

References:

Installable Triggers

Thanks Carlos. I created a Trigger so, on edit it will send the email but it's grabbing every row that says "Closed" and emailing them 1 by 1. I need it to only email the one row that was just changed to "Closed" — JasonG, Dec 17 '20 at 18:04
The ````sendEmail(e)```` function I sent should only check and email once for the cell that was edited, which is in the variable ````e````. Maybe you have pointed the trigger to your old ````sendEmails()```` function? — CMB, Dec 17 '20 at 18:13
when I pasted your script, I get the Error "Cannot read property 'Range" of undefined" sendEmail @ ClosedTsr.gs:7... the If statement — JasonG, Dec 17 '20 at 21:20
You do not need to run ````sendEmail()```` manually. It will run when you edit the Google Sheet because of the trigger. — CMB, Dec 17 '20 at 21:46

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

1 Answers1