Home > Enterprise >  How to set specific value in sheet to trigger an email to send?
How to set specific value in sheet to trigger an email to send?

Time:08-09

I've been working on a script for google sheets that will trigger 3 different emails depending on 3 different columns. In the 1st column I want the email to send whenever any edits have been made in it. For the 2nd and 3rd, I only want the email to be sent when a specific text has been written. However, the 2nd and 3rd columns are sending emails no matter what has been written in them.

Here is the code so far, thanks in advance!

function sendMailEdit(e){
   var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("REVIEW");
   const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,24).getValues();

   let useremail = rData [0][22]

   ...

   if (e.range.columnStart == 8){
   MailApp.sendEmail({
     to: "[email protected]",
     subject: "Global - New Account Access Request",
     htmlBody: msg,
     noReply: true
   });
   }
     
   else if (e.range.columnStart == 15 || e.value == "BIZ APPROVED"){
   MailApp.sendEmail({
     to: "[email protected]",
     subject: "Global - New Account Access Request",
     htmlBody: msg2,
     noReply: true
   });
   }
  
   else if (e.range.columnStart == 19 || e.value == "RISK APPROVED"){
   MailApp.sendEmail({
     to: useremail,
     subject: "Global - New Account Access Request",
     htmlBody: msg3,
     noReply: true
   });
   }

} 

CodePudding user response:

In your script and situation, how about modifying || to && for the if statement as follows?

From:

else if (e.range.columnStart == 15 || e.value == "BIZ APPROVED"){

and

else if (e.range.columnStart == 19 || e.value == "RISK APPROVED"){

To:

else if (e.range.columnStart == 15 && e.value == "BIZ APPROVED"){

and

else if (e.range.columnStart == 19 && e.value == "RISK APPROVED"){
  • By this modification, when the values of BIZ APPROVED and RISK APPROVED are put to the column "O" and "S", respectively, the script of if statement is run.
  • Related