Iterator error when trying to save Google Doc to subfolder in Shared Drive (Apps Script)

Question

I have a code that creates a Google Doc from data on a sheet using a template. After creation, I want that document to save into a specific subfolder on a Shared Drive that I have editor access to.

Specifically, I have it check for the person's name {{Name}} which is row[3] in the sheet. I then have it check for the month {{Month}} which is row[2] in the sheet, and it's supposed to save within the month subfolder. However, my problem is that I keep getting the error Exception: Cannot retrieve the next object: iterator has reached the end. It works for the first NAME folder and then breaks for all after that.

I searched stackoverflow for this issue because it is a common one. It seems that my code is missing hasNext() function to actually perform the iteration. However, I can't figure out how to add this to my code (I am a newbie). I've tried several variations of while and if statements but they always throw various errors.

Here is my current code WITHOUT the hasNext(), can someone help me correct it?

/* Loops through spreadsheet rows. If there is no Document Link, a new document will be generated. */
function createNewGoogleDocs() {

  const googleDocTemplate = DriveApp.getFileById('TEMPLATE_ID_HERE');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
  const rows = sheet.getDataRange().getValues();
  
  rows.forEach(function(row, index) {
    if (index === 0) return;
    if (row[13]) return;

    const parent_folder = DriveApp.getFoldersByName(row[3]).next();
    const subfolder = parent_folder.getFoldersByName(row[2]).next();
    const destinationFolder = DriveApp.getFoldersByName(subfolder).next();
    const copy = googleDocTemplate.makeCopy(`QCP - ${row[3]} - ${row[2]}`, destinationFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    body.replaceText('{{Month}}', row[2]);
    body.replaceText('{{Name}}', row[3]);
    body.replaceText('{{Improvement 1}}', row[11]);
    body.replaceText('{{Improvement 2}}', row[12]);

    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index + 1, 14).setValue(url);
  });
}

I was able to find the answer thanks to Yuri. The problem was that the subfolder had a space the data did not. To correct this problem, Yuri showed me how to make my code create the folder if it doesn't exist. Please see below for fixed code.

/* Loops through spreadsheet rows. If there is no Document Link, a new document will be generated. */
function createNewGoogleDocs() {

  const googleDocTemplate = DriveApp.getFileById('TEMPLATE_ID_HERE');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
  const rows = sheet.getDataRange().getValues();
  
  rows.forEach(function(row, index) {
    if (index === 0) return;
    if (row[13]) return;

    const parent_folder = DriveApp.getFoldersByName(row[3]).next();
    try { 
      subfolder = parent_folder.getFoldersByName(row[2]).next(); 
      }
    catch(e) { 
      subfolder = parent_folder.createFolder(row[2]);
      }
    const copy = googleDocTemplate.makeCopy(`QCP - ${row[3]} - ${row[2]}`, subfolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    body.replaceText('{{Month}}', row[2]);
    body.replaceText('{{Name}}', row[3]);
    body.replaceText('{{Improvement 1}}', row[11]);
    body.replaceText('{{Improvement 2}}', row[12]);

    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index + 1, 14).setValue(url);
  });
}

As a guess. Try to change const destinationFolder = DriveApp.getFoldersByName(subfolder).next(); to const destinationFolder = subfolder; — Yuri Khristich
@YuriKhristich Unfortunately, that still produces the "Exception: Cannot retrieve the next object: iterator has reached the end." error. — Addy
Make sure that you really have on Drive the subfolder with the name from row[3] within the folder with the name from row[2]. Otherwise I can show how the folder could be created on the fly. — Yuri Khristich
@YuriKhristich Okay, I think that found the issue! The subfolder did exist, but there was an error with spacing in the folder name. When I renamed the folder to fix the spacing, the iteration error fixed. That being said, I can see how this would be a problem in the future.. Could you teach me how to create the folder if it doesn't exist? — Addy

Yuri Khristich Yuri Khristich · Accepted Answer · 2022-07-28T15:11:52

As a guess. Try to change these lines:

const subfolder = parent_folder.getFoldersByName(row[2]).next();
const destinationFolder = DriveApp.getFoldersByName(subfolder).next();
const copy = googleDocTemplate.makeCopy(`QCP - ${row[3]} - ${row[2]}`, destinationFolder);

With:

try { const subfolder = parent_folder.getFoldersByName(row[2]).next() }
catch(e) { const subfolder = parent_folder.createFolder(row[2]) }
const copy = googleDocTemplate.makeCopy(`QCP - ${row[3]} - ${row[2]}`, subfolder);

It tries to get the subfolder. In case the subfolder doesn't exists it creates the subfolder.

Iterator error when trying to save Google Doc to subfolder in Shared Drive (Apps Script)

1 Answers