I am currently trying to write a script that will send an email to different people when different columns are edited.

For example, if column A is edited, an email should be sent to [email protected]. If column B is edited, an email should be sent to [email protected]

I've written a script that works when sending an email to one person, but when I try add another trigger event, the script stops working.

Here is what I have so far:

function sendMailEdit(e){
   var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("REVIEW");
   if (e.range.columnStart != 8) return;
   const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,20).getValues();
   let sub = rData[0][2];
   let sta = rData[0][3];
   let pos = rData[0][4];
   let dep = rData [0][5];
   let jur = rData [0][6];
   let acc = rData [0][7];
   let rem = rData [0][14];
   let msg = 'A new account access request has been added to ';
       msg ='<a href="' SpreadsheetApp.getActiveSpreadsheet().getUrl() '#gid=' e.source.getActiveSheet().getSheetId() '">TEST GLOBAL - Account Access</a>';
       msg ='<br>●Submitter: '   sub;
       msg ='<br>●Email: '   sta;
       msg ='<br>●Staff Position: '   pos;
       msg ='<br>●Department: '   dep;
       msg ='<br>●Jurisdiction: '   jur;
       msg ='<br>●Account Access: '   acc;
   MailApp.sendEmail({
     to: "[email protected]",
     subject: "Global - New Account Access Request",
     htmlBody: msg,
     noReply: true
   });

   if (e.range.columnStart != 13 || e.value != "APPROVED") return;
   let msg2 = 'A new account access request has been added to ';
       msg2 ='<a href="' SpreadsheetApp.getActiveSpreadsheet().getUrl() '#gid=' e.source.getActiveSheet().getSheetId() '">TEST GLOBAL - Account Access</a>';
       msg2 = ' which has been aproved by the Business Operations Team.';
       msg2 ='<br>●Submitter: '   sub;
       msg2 ='<br>●Email: '   sta;
       msg2 ='<br>●Staff Position: '   pos;
       msg2 ='<br>●Department: '   dep;
       msg2 ='<br>●Jurisdiction: '   jur;
       msg2 ='<br>●Account Access: '   acc;
       msg2 ='<br>●Remarks: '   rem;
   MailApp.sendEmail({
     to: "[email protected]",
     subject: "Global - New Account Access Request",
     htmlBody: msg2,
     noReply: true
   });
}

CodePudding user response：

The return statement will exit your function and not execute any of the code in the lines below it

What you probably want is something like

 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 != 13 || e.value != "APPROVED"){
  ...
  MailApp.sendEmail({
     to: "[email protected]",
     subject: "Global - New Account Access Request",
     htmlBody: msg2,
     noReply: true
   });
  }
}

• The combination of if and else if means that the script checks first either the first if condition is fullfilled.
• If the first condition is fullfilled, the script executes the respective code block (sending en email to AAA)
• If the first condition is not fullfilled, the script jumps to the second conditional statement (else if)
• If the second condition is fullfilled, the script executes the respective code block (sending en email to BBB)
• If none of the two conditions is fullfilled, the script does not execute any of the respective code blocks
• If both conditions are fullfilled - the script executes only the first code block, since the if else implies the the second code block is only executed ith the first condition is not fullfilled