Trigger fails when executing sendEmails function

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I created a script to send emails with calculated information from a sheet. The script works beautifully when run manually. I would like this to trigger every minute, but when I try to create any kind of time-based trigger, I get an error where it says there is no recipient. I have made sure there are new entries that should get emails, but it won't work until I manually run the script. I don't know what to post code-wise, because the code works fine. It just won't run when a trigger starts it.

EDIT: Scripts below:

function minuteTrigger() {
  ScriptApp.newTrigger('sendEmails')
      .timeBased()
      .everyMinutes(1)
      .create();
}

This is the email script:

var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails() {
  SpreadsheetApp.flush();
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; 
  var numRows = 1000; 
  var dataRange = sheet.getRange(startRow, 1, numRows, 9);
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[1]; 
    var message = row[7]; 
    var emailSent = row[8]; 
    if (emailSent !== EMAIL_SENT) { 
      var subject = 'Your Tacoma Lap Results';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 9).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

Best Answer

Ok, I got it. There were sporadic blank emails being sent, and it seems that the email script was switching between active sheets, and basically looking in the wrong place. The second suggested link held the solution, with a little modification, as it was not a onFormSubmit trigger or a for loop in the code.

Thanks, @Tedinoz!

function sendEmails() {
  var ss=SpreadsheetApp.openById('1A3cVqUqaBqEIPdcp0NObL-wy2vqgt5gV2DCHshr9lQ0');
  var sheet=ss.getSheetByName('Query&Reply');
Related Topic