0

Trying to send a pdf attachment to an email using Google sheets. I have checked and tried to use the replies in previous responses but my code fails after sending the first email.

This is the code I am using. Please ignore the comments in the code - I am trying to make it easy for others (like me) who are trying to swim upstream. The error message I get is cannot retrieve the next object- iterator has reached the end.

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 3; // Number of rows to process Change to last line of DATA minus 1
  // Fetch the range of cells A2:N4 Change to last line of DATA minus 1
  var dataRange = sheet.getRange(startRow, 1, numRows, 3);
  // Fetch values for each row in the Range.
  //The Next line is added to try to find the pdf
  var file = DriveApp.getFilesByName('2019 05 18 Update.pdf')
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // Fifth column
    var message = row[1]; // Third column change heading to message (orlink to)
    var emailSent = row[2]; // Columnn C
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Update from your HOA';
      //The next line is added to link the pdf to the email
      // MailApp.sendEmail(emailAddress, subject, message, {attachments: file});
     // THIS REMOVED AND REPLACED WITH THE NEXT LINE MailApp.sendEmail(emailAddress, subject, message);
     MailApp.sendEmail(emailAddress, subject, message,
   {attachments: file.next().getBlob()} )
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}
  • Welcome. There isn't enough details to give you an specific answer but maybe the answer will be enough. If not, please add more details like the number of files that are returned by var file = DriveApp.getFilesByName('2019 05 18 Update.pdf') and the number of rows on your sheet. – Rubén May 19 at 15:09
  • There is only one file. It is supposed to be attached to all emails that are sent. – Brian Smith May 20 at 20:28
0

Your code is iterating over the rows got by getDataRange / getValues and it's using file.next() on each iteration so it's very likely that that there are fewer files than rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.