-1

My code below worKs fine when i break it seperately to copy records and then send mail.

i need to have the send mail function run after the onedit copies the record. the code only copies the record but does not send the mail. the mail code works when executed manually.

function onEdit(event){ var ss = SpreadsheetApp.getActiveSpreadsheet();var s = event.source.getActiveSheet(); var r = event.source.getActiveRange();

if(s.getName() == "UK STOCK ALERTS" && r.getColumn() == 5 && r.getValue() == "Back Orders Accepted") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("AlertMailOut");  
var target = targetSheet.getRange(1,16)    
s.getRange(row, 1, 1, numColumns).copyTo(target);        

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AlertMailOut");  var startRow = 2;  var numRows = 40;  var dataRange = sheet.getRange(startRow, 1, numRows, 100); var data = dataRange.getValues(); for (var i = 0; i < data.length; ++i) {var row = data[i]; var emailAddress = row[0];var message = row[1]; // Second column
var emailSent = row[4]; // Third column
if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
  var subject = 'Brick Fanatics Stock Update';
  MailApp.sendEmail(emailAddress, subject, message);
   SpreadsheetApp.flush();
} } } }
Jignesh Joshi
  • 1
  • 1
    If you are using `onEdit` as the simple trigger, please use the installable trigger of OnEdit. Because I thought that the reason of your issue is due to that `MailApp.sendEmail` is used with the simple trigger. [Related question](https://stackoverflow.com/q/28163274) – Tanaike Jan 25 '21 at 08:44
  • By the way, when you use the installable OnEdit trigger, I would like to recommend to rename the function name from `onEdit` to others. Because the function name of `onEdit` is used for the simple trigger. So when the installable OnEdit trigger is installed to the function of `onEdit`, this function is run 2 times with the asynchronous processing. [Ref](https://gist.github.com/tanaikech/88f7fd5ed14da5e9afde18310da61cb5) So please be careful this. – Tanaike Jan 25 '21 at 08:44

1 Answers1

1

You will need to execute this function as an installable trigger, rather than a simple trigger.

To do:

  • Rename your function to something else
  • Install an onEdit trigger manually from the Triggers tab by selecting Add trigger
Andras Sztrokay
  • 83
  • 8