How do I add link in gmail notifications & have unspecific trigger values in sheets using Script?

Question

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!

Answer 1

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