0

I am running a script that should send a message to an email that is input into a specific sheet cell. I get the error Failed to send email: no recipient

function emai() {
  var EMAIL_SENT = 'EMAIL_SENT';
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet5')
  var startRow = 2; // First row of data to process
  var numRows = 1000; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 2, numRows, 1000);
  // 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 emailAddress = row[1]; // First column
    var message = "b"
    var emailSent = row[26]; // Third column
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Sending emails from a Spreadsheet';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 1000).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}
Community
  • 1
  • 1
retter
  • 23
  • 4
  • 1
    Don't know much about that,but are you able to debug my looking at the values for emailAddress, etc. ? – SScotti Jul 01 '19 at 02:14
  • 1
    If you put `console.log(emailAddress)` after `var emailAddress = row[1];`, what does it log? – Santi Jul 01 '19 at 02:15
  • is `row` a zero-based index? if so, you need `row[0]`. – David Zemens Jul 01 '19 at 02:15
  • 1
    @DavidZemens The comments seem to be misleading...(I guess?) For example `row[26]; // Third column`. – Santi Jul 01 '19 at 02:25
  • 1
    my point is that the code comments seem to be at odds with the data. 26 is not the "third" column, and "1" is probably not the "first" column. Seems like this is a typo or other error OP needs to debug. – David Zemens Jul 01 '19 at 02:26
  • yes. the comments say one thing but the code is clearly doing something else. if OP is novice (or lazy or tired after a long day, etc) is easy to overlook minutiae like these. so if he *thinks* the comment accurately describes the row, and he's mistaken in that assumption, he won't find the otherwise obvious solution. clearly, the email is not assigned a To address. why? well, without seeing the data we can only guess, but seems like a bad index. – David Zemens Jul 01 '19 at 02:29
  • Could you share your spreadsheet to make sure we get what you are referring to with these comments? the indexing seems to be a little off in the way the code is written. – AMolina Jul 01 '19 at 10:25
  • Please share a copy of your spreadsheet with us. – Cooper Jul 01 '19 at 16:28

1 Answers1

0

Try this:

function email() {
  var EMAIL_SENT='EMAIL_SENT';
  var sheet=SpreadsheetApp.getActive().getSheetByName('Sheet5')
  var startRow=2;
  var numRows=1000;
  var dataRange=sheet.getRange(startRow, 2, numRows, sheet.getLastColumn()-1);
  var data=dataRange.getValues();
  for (var i=0;i<data.length; i++) {
    var row=data[i];
    var emailAddress = row[1]; // Column 2
    var message = "b"
    var emailSent = row[26]; // Column 28
    if (emailSent != EMAIL_SENT) {
      var subject = 'Sending emails from a Spreadsheet';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(i + startRow - 1, 28).setValue(EMAIL_SENT);
    }
  }
}

If this doesn't work they we will need to see a copy of your spreadsheet because many of the your comments don't make any sense.

Cooper
  • 59,616
  • 6
  • 23
  • 54