I wrote a script recently which had a specific trigger value and is able to send email notifications. Now I am trying to make it more complex but have been struggling. How can I: (1) make the trigger value go from ​"SALES" ​to any unspecific value, as long as the column has been edited (2) add the link of the spreadsheet which was edited in the email notification (3) had a new row of text in the email notification (as it is currently all in one sentence)

​function sendMailEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RO");
if (e.range.columnStart != 2 || e.value != "SALES") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,8).getValues();
let id = rData[0][4];
let pro = rData[0][5];
let tra = rData[0][6];
let amo = rData [0][7];

let msg = "A new rollover request has been added to the US - Sales spreadsheet = Account ID: "   id   ", Provider account: "   pro   ", Transfer from: "   tra   ", Transfer Amount: $"   amo;
GmailApp.sendEmail("[email protected]", "New Rollover Request", msg)
}

​Thank you!

CodePudding user response：

Solution

1. Delete || e.value != "SALES"
2. Use html tag <a href="{link}">{title}</a>
3. Use html tag <br>

function sendMailEdit(e){
   var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RO");
   if (e.range.columnStart != 2) return;
   const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,8).getValues();
   let id = rData[0][4];
   let pro = rData[0][5];
   let tra = rData[0][6];
   let amo = rData [0][7];
   let msg = 'A new rollover request has been added to the ';
       msg ='<a href="' SpreadsheetApp.getActiveSpreadsheet().getUrl() '#gid=' e.source.getActiveSheet().getSheetId() '">US - Sales spreadsheet</a>';
       msg ='<br>●Account ID: '   id;
       msg ='<br>●Provider account: '   pro;
       msg ='<br>●Transfer from: '   tra;
       msg ='<br>●Transfer Amount: $'   amo;
   MailApp.sendEmail({
     to: "[email protected]",
     subject: "New Rollover Request",
     htmlBody: msg,
     noReply: true
   });
}

Return on email