Failed to send email: no recipient (line 19, file “Email”)

0
votes

I got notification like this

Exception: Failed to send email: no recipient (line 19, file "Email")

I got no idea what happened

/**
 * Sends emails with data from the current spreadsheet.
 */
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3; // First row of data to process
  var getLastRow = 900; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, getLastRow, 6);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var trigger = row[0]; // kolom ke 1 TRIGGER
    var emailAddress = row[2]; // Email column
    var message = row[4]; // Content column
    var subject = row[3]; // Subject Column
    var status_email = row[5];
    if ((trigger != 'FALSE') && (status_email != 'EMAIL_SENT')) { // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 6).setValue('EMAIL_SENT');
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}
1
google-apps-scriptgoogle-sheets
Os emails estão estão em qual coluna? - Sidney Cardoso dos Santos
@SidneyCardosodosSantos Please write in English. - Rubén
Welcome to Stack Overflow Please add more details like sample data input and the corresponding expected result. Note: Is very likely that the Email column has some blank cells. - Rubén
hi ruben, thanks for your response...alright this is my sample data link : [link] (docs.google.com/spreadsheets/d/…) - IndraAzenx

1 Answers

0
votes

You define getLastRow = 900 and emailAddress = row[2]

  • This means that from row 3 to row 900 you expect an email address to be present in each cell of column C
  • If you try to send an email from a row that does not contain an email address wou will get the error you metnioned

How to avoid the error?

Catch it with an if statement that checks either the cell is empty before sending the email

Sample




function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3; // First row of data to process
  var getLastRow = 900; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, getLastRow, 6);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var trigger = row[0]; // kolom ke 1 TRIGGER
    var emailAddress = row[2]; // Email column
    if(emailAddress != "" && emailAddress != " "){ 
      var message = row[4]; // Content column
      var subject = row[3]; // Subject Column
      var status_email = row[5];
      if ((trigger != 'FALSE') && (status_email != 'EMAIL_SENT')) { // Prevents sending duplicates
        MailApp.sendEmail(emailAddress, subject, message);
        sheet.getRange(startRow + i, 6).setValue('EMAIL_SENT');
        // Make sure the cell is updated right away in case the script is interrupted
        SpreadsheetApp.flush();
      }
    }
  }
}