Google Spreadsheet Cannot retrieve the next object: iterator has reached the end

Question

I have a Spreadsheet that I use to make a list of worked hours per client. I use a script to make a pdf of the sheet and directly places the pdf in the client folder that is related to the pdf it just created. It knows which folder it needs to use by the client name that it takes from the spreadsheet (name in Spreadsheet is the same as the folder name).

Everything goes super smooth... As long as the client name is 1 word (for example WeSellApples), as soon as the client name has more words (for example We Do Not Sell Apples) I get the error Cannot retrieve the next object: iterator has reached the end.

Because everything works perfect with one-word names I have no clue why this happens.

function createPDF() {
   var sheet1 = SpreadsheetApp.getActiveSpreadsheet();
    var gebruikSheet1 = sheet1.getSheetByName("Urenregistratie maak pdf");
  var ui = SpreadsheetApp.getUi();
  var result = ui.alert( // Asks if I'm sure I want to save
      "Opslaan als PDF?",
      "Sla " +sheet1.getName()+ " " + "week " + gebruikSheet1.getRange("L6").getValue() + 
" " + gebruikSheet1.getRange("C6").getValue() +".pdf op als PDF", 
// Shows the name so I can double check if I selected the right client, 
// client name is in C6 en weeknumber in L6. Both cell values are dynamic (with Vlookup)
      ui.ButtonSet.OK_CANCEL);
  if (result == ui.Button.OK) {
    var hideSheet = sheet1.getSheetByName('Uren bijhouden'); 
// to make sure only the second sheet is exported as pdf
    hideSheet.hideSheet();
    var docblob = SpreadsheetApp.getActiveSpreadsheet().getAs("application/pdf"); 
    docblob.setName(sheet1.getName()+ " " + "week " + gebruikSheet1.getRange("L6").getValue() + 
" " + gebruikSheet1.getRange("C6").getValue() + ".pdf"); 

  var file = DriveApp.createFile(docblob);
    ui.alert(sheet1.getName()+ " " + "week " + gebruikSheet1.getRange("L6").getValue() 
+ " " + gebruikSheet1.getRange("C6").getValue() + " is opgeslagen"); 
  } else {
    ui.alert("Opslaan als .pdf is niet gelukt");
  }

  hideSheet.showSheet(); 


   // file to move
  var klantmap = gebruikSheet1.getRange("C6").getValue(); 
// the client name is in cell C6
  var id = DriveApp.getFoldersByName(klantmap).next().getId(); 
// script can find id of client folder without problem as long as client name 
// is one word
// this is the line where the error occures when client name contains more words
  Logger.log(id);

  DriveApp.getFolderById(id).addFile(file); // add file to client folder

  DriveApp.getRootFolder().removeFile(file); // removes file from root folder

}

I would like to recieve some tips where to look to figure out the problem myself.

After testing, I noticed that the behavior could happen because of spaces, using getFoldersByName() worked fine regardless of how many words there were in the name of the folder. However, if either the folder or the search name did not match exactly (maybe an extra space at the end of either string) I'd see the same error, since it wasn't finding "any" folder with that name. Check to see if the string you retrieve from the Sheet has extra spaces, also, check by manually using getFoldersByName() with a hard-coded string name of a folder with spaces. — AMolina
Oh yeah sorry, there was a problem with spaces indeed. I'm now working on a function that automatically creates a client folder if it doesn't exist instead of creating folders by hand to make sure there will no longer be errors. — Nina Bakker
Glad to hear it was solved! As for creating the folders, creating the folders like that will work better, that way you'll know there are no unwanted names. — AMolina

Cooper Cooper · Accepted Answer · 2019-09-18T19:05:58

After regrouping here a bit I noticed that:

this line: var docblob = SpreadsheetApp.getActiveSpreadsheet().getAs("application/pdf");

I think it should be like this: var docblob = SpreadsheetApp.getActiveSpreadsheet().getBlob().getAs("application/pdf");

Google Spreadsheet Cannot retrieve the next object: iterator has reached the end

1 Answers