I am trying to write a script for Google Sheets which will send an email notification once the sheet is updated with a new row of data (the data comes through an IMPORTRANGE function) but I can't seem to get it to work.

Here is what I have written so far:

function sendMailEdit(e){
   var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Withdraw");
   if (e.range.columnStart != 6 || e.value != "LARGE") return;
   const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,9).getValues();
   let id = rData[0][8];
   let account = rData[0][4];
   let withdraw = rData[0][5];
   let amount = rData[0][6];
   let date = rData[0][1]

   let msg = 'A new Withdrawl request has been made ';
       msg ='<a href="' SpreadsheetApp.getActiveSpreadsheet().getUrl() '#gid=' e.source.getActiveSheet().getSheetId() '">US - Sales spreadsheet</a>';
       msg ='<br>●ID: '   id;
       msg ='<br>●Account Type: '   account;
       msg ='<br>●Withdrawal Type: '   withdraw;
       msg ='<br>●Amount: $'   amount;
       msg ='<br>●Request Date: '   date;
   MailApp.sendEmail({
     to: "[email protected]",
     subject: "Withdrawal Request",
     htmlBody: msg,
     noReply: true
   });
}

CodePudding user response：

It looks that you expect to use a trigger that will be activated when IMPORTRANGE be automatically recalculated. This is not possible as the Google Apps Script triggers aren't triggered by an automatic recalculation.

Apparently the basic workarounds for you are to use on open installable trigger, time-driven trigger or to run the function manually instead of using a trigger. It might be possible to explore other options but there aren't enough details.

CodePudding user response：

This will work for an importRange

function onMyChange(e) {
  //e.source.toast('Entry');
  const sh = e.source.getActiveSheet();
  //Logger.log(JSON.stringify(e));
  //Logger.log(e.source.getActiveSheet().getName())
  if (sh.getName() == "Your Sheet Name" && e.changeType == "OTHER") {
    //Send your email
  }
}