How can I send an email notification if specified keyword is listed in a range?

Question

Overview:

• I'm attempting to Send an email notification message, each time the keyword Complete is listed in a column (CJ).
• Not sure if I have the right approach, but the validate_complete function checks to see if the value is listed in column CJ, if exists, it populates column CK (in the same row) with the text 'Send Email'.
• The 'Send Email' text initiates the Send_notification function and the email is delivered to the specified email address.
• I have also created a Trigger, which is supposed to initiate the validate_complete function, each time the 'Send Email' is listed in Column (CK).

Current issue:

When the Trigger is executed, it's runs for several minutes and either times out or send multiple email messages at a time. Need to only send One email message when text 'Send Email' is listed for a specified row in column (CK), then clear the 'Send Email' text so multiple emails are not sent out to if another row also contains the 'Send Email' text.

Further guidance is much appreciated. Thanks

function validate_complete(){

  const ss = SpreadsheetApp.getActive();

  const sh_01 = ss.getSheetByName('Index');   
  const data = sh_01.getRange('CI2:CK'+sh_01.getLastRow()).getValues();

  var columnNumberToWatch = 88; // column A = 1, B = 2, etc.
  var valueToWatch = 'Complete';

 data.forEach(r=>{
  var range = sh_01.getActiveCell();
  if (range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    range.offset(0, 1).setValue('Send Email');

  }

  });
    sendnotificationEmail();
}



function sendnotificationEmail() {
  const ss = SpreadsheetApp.getActive();

  const sh_02 = ss.getSheetByName('Index'); 
  const date_range = sh_02.getRange('BW2').getValues();
  const data_02 = sh_02.getRange('CI2:CK'+sh_02.getLastRow()).getValues();

  var recipient = "[email protected],[email protected]";
  data_02.forEach(r=>{
 
     let overdueValue = r[2];  
     if (overdueValue === "Send Email"){
         let name = r[0];
         let message = ' Test: ' + name ;
         let subject = 'Test: '+ name + ', ' + date_range + ' Inspections are available' ;
         MailApp.sendEmail(recipient, subject, message); 
     }
       var range_02 = sh_02.getRange('CK2:CK100');
       range_02.clear();

  });  
}

Answer 1

The goal is to Send an email whenever Complete is in column (CJ). From what I see, the code's logic is to look for the value and set the cell on its right to 'Send Email' before sending out the email. It's likely that the script times out or send multiple email messages at once due to the spreadsheet values being called out several times.

Here's a modified version of your code that should achieve what you'd like:

function validate_complete() {
  const ss = SpreadsheetApp.getActive();
  const sh_01 = ss.getSheetByName('Index');
  const date_range = sh_01.getRange('BW2').getValue();
  var columnNumberToWatch = 88;
  var valueToWatch = 'Complete';
  var recipient = "[email protected], [email protected]";
  var range = sh_01.getActiveCell();
  if (range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    if (range.offset(0, 1).getValue() != "Send Email") {
      range.offset(0, 1).setValue('Send Email');
      let row = range.getRow();
      let name = sh_01.getRange(row, columnNumberToWatch - 1).getValue();
      let message = ' Test: ' + name;
      let subject = 'Test: ' + name + ', ' + date_range + ' Inspections are available';
      MailApp.sendEmail(recipient, subject, message);
    }
  }
}

Note: To avoid timing out, I've combined both the functions, removed the loop, and changed the script's logic to work on the row being edited. It doesn't clear the 'Send Email' text as well since I've added a conditional statement to only send an email if there's no Send Email value in Column CK.