Google Apps Script error - multiple rows from Google Sheets but only one file in Google Drive - “iterator has reached the end”

1
votes

I have a script that pulls fields from a Google Sheet and inserts them into an email template and sends the emails off. That works fine.

I recently wanted to include a PDF as an attachment to the emails. It would be the same PDF for every email. I uploaded the PDF into Google Drive. When I run the script, the first email is sent off fine with the attachment but the following emails are not sent because I encounter this error: "Cannot retrieve the next object: iterator has reached the end"

Pretty sure it has to deal with the attachment/file and me not handling the iteration correctly. Can someone help? Below is the code:

function send2Email()
{
  var filename= 'even_overview2020.pdf';
  var file = DriveApp.getFilesByName(filename);
  
  var spread         =SpreadsheetApp.getActiveSpreadsheet();
  var contactSheet   =spread.getSheetByName(contactSheetName);
  var bodySheet      =spread.getSheetByName(templateSheetName);
  var contactData    =contactSheet.getDataRange().getValues();
  var bodyData       =bodySheet.getDataRange().getValues();
  var fname,company,sign,template,email,subject,body,sender,file;
  for (var i =1;i<contactData.length;i++)

  {
    contactData[i][statusCol-1]="";
  }
  contactSheet.getDataRange().setValues(contactData);
  for (var i =1;i<contactData.length;i++)
  {
    fname=trim_(contactData[i][fnameCol-1]);
    company=trim_(contactData[i][companyCol-1]);
    sign=trim_(contactData[i][signCol-1]);
    template=trim_(contactData[i][templateCol-1]);
    email=trim_(contactData[i][emailCol-1]);
    sender=trim_(contactData[i][senderCol-1]);
    Logger.log(email);
    for(var j=1;j<bodyData.length;j++)
    {
      if(trim_(bodyData[j][tempRefCol-1]).toUpperCase()==String(template).toUpperCase())
      {
        body=bodyData[j][bodyCol-1];
        subject=bodyData[j][subjectCol-1];
      }
    }
    Logger.log(j+","+email+','+body+','+subject);
    body=body.replace(/\n/g,"<br>");
    body=body.replace("(w)",sign).replace("(x)",fname).replace("(y)",company).replace("(s)",sender.split(" ")[0]);
    Logger.log(email+','+body+','+subject);

    
    MailApp.sendEmail({to:email,subject:subject,name:sender,htmlBody:body,attachments: [file.next().getAs(MimeType.PDF)]});
    contactSheet.getRange(i+1, statusCol).setValue('Y');
  }
}
1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

How about this modification?

Modification point:

  • In your script, attachments: [file.next().getAs(MimeType.PDF)]} is used in the loop. By this, the 1st loop works by file.next(). But after 2nd loop, an error occurs at file.next() because the file of filename is one file in Google Drive. I think that this is the reason of your issue.

In order to avoid this issue, how about the following modification?

Modified script:

From:

var file = DriveApp.getFilesByName(filename);

To:

var files = DriveApp.getFilesByName(filename);
var file;
if (files.hasNext()) {
  file = files.next().getAs(MimeType.PDF);
} else {
  throw new Error("No file");
}

And also, please modify as follows.

From:

MailApp.sendEmail({to:email,subject:subject,name:sender,htmlBody:body,attachments: [file.next().getAs(MimeType.PDF)]});

To:

MailApp.sendEmail({to:email,subject:subject,name:sender,htmlBody:body,attachments: [file]});

Reference: