Search code examples
google-apps-scriptgoogle-sheetsgoogle-drive-api

Exception: Cannot retrieve the next object: iterator has reached the end. when running by other user

I have a code to get the parent folder of my GoogleSheet by using this code

var ssId = SpreadsheetApp.getActiveSpreadsheet().getId(); //get my GSheet ID
var parentFolder = DriveApp.getFileById(ssId).getParents().next(); //get the parent folder name

I've already share the base folder to the whole organization with editor rights.

Running the app by me it is running without any issue.

But when my colleague run the app, it is getting this error.

Exception: Cannot retrieve the next object: iterator has reached the end.

tried hasNext()

var ssId = SpreadsheetApp.getActiveSpreadsheet().getId(); //get my GSheet ID
var parentFolder = DriveApp.getFileById(ssId).getParents(); //get the parent folder

    while (parentFolder .hasNext()) {
      var temp1 = parentFolder .next();
      Logger.log("Temp : "+temp1)
    }

this is not returning anything from other users.

Any idea is this something to do with Google Permission Security issue or anything I've missed?

Edit Code Snippet

Code.gs

function doGet(request) {
 return HtmlService.createTemplateFromFile('CustomerForm').evaluate().setTitle("Demo Form");
}

function uploadFile(vFolder,vFile ,vdata){
  //upload folder
  var drivepath = uploadFiletoDrive(vFolder);

  var contentType = vdata.substring(5,vdata.indexOf(';')),
        bytes = Utilities.base64Decode(vdata.substr(vdata.indexOf('base64,')+7)),
        blob = Utilities.newBlob(bytes, contentType, vFile);
  var folder = DriveApp.getFolderById(drivepath);  
  var file = folder.createFile(blob);

  return file.getId();
}

function uploadFiletoDrive(folderName){
 
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const parentFolder = DriveApp.getFileById(ssId).getParents().next(); // get the parent folder location
  
  Logger.log(ssId)
  Logger.log(parentFolder)
 
  const subFolders = parentFolder.getFolders();
  while (subFolders.hasNext()) {
    let folder = subFolders.next();
 
    // Returns the existing folder if found.
    Logger.log(folder.getName())
    if (folder.getName() === folderName) {
      Logger.log("Folder exists")
      Logger.log("Folder SSID : "+folder.getId())
      return folder.getId();
    }
  }
  // Creates a new folder if one doesn't already exist.
   Logger.log("Folder Creating")
   //creating the folder if not exists and will put the description
  folder = parentFolder.createFolder(folderName);
   Logger.log("New Folder SSID : "+folder.getId())
  return folder.getId(); 
}

CustomerForm.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <table>
      <tr>
        <td> Folder Name : </td>
        <td> <input type="text" id="FolderName"> </td>
      </tr>
      <tr>
        <td> Input File : </td>
        <td> <input type='file' id="FileAttach"></td>
      </tr>
      <tr>
        <td>
          <button type="submit" id="SubmitFile" onclick="submitForm()" >Submit</button>
        </td>
      </tr>
    </table>

  </body>
</html>

<script>
  function submitForm(){
    var myFile =  document.getElementById('FileAttach').files;
    var myFolder = document.getElementById('FolderName').value;
    var reader = new FileReader();
    tempFile = myFile[0];

    if(tempFile){
        reader.readAsDataURL(tempFile);
        reader.onloadend = function(e) {
          if (e.target.error != null) {
            alert("File " + tempFile.name + " could not be read.");
            return;
          } else {
            google.script.run.withSuccessHandler(finishUpload).uploadFile(myFolder, tempFile.name, e.target.result);
          }
        }; 
      }
  }

  function finishUpload(vFileID){
    alert("Uploaded File ID is "+vFileID)
  }

</script>
Solution

  • Based on your last comment MainFolder -> Share -> Get link -> change -> Change to 'Editor'. "Anyone in this group with this link can edit", I understood that you are sharing the folder with your domain.

    Issue:

    Due to a relatively recent security update (see Reference below), and since the folder is shared via type=domain, users need to provide the resourceKey the first time they access it.

    For example, if users try to access the share link on the browser, and the share link doesn't include the resourceKey, they won't be able to access it if they haven't done it before. The resourceKey is included in the share link, when sharing the files, like this:

    https://drive.google.com/drive/folders/{FILE_ID}?resourcekey={RESOURCE_KEY}

    Therefore, it is not accessible via File.getParents() if it hasn't been previously accessed, nor via Drive API's Parents.list.

    Possible workarounds:

    Here are possible alternatives to solve this:

    • If you are an admin, you can remove the security update, so resourceKey is not required.
    • Change the sharing settings so that the folder is directly shared with your desired users instead of the whole domain.
    • Make the users access the folderbefore executing this, either via browser (using the full share link, including resourceKey), or programmatically, using DriveApp.getFolderByIdAndResourceKey(id, resourceKey). In this case, an option would be to change your current code workflow, so that your parent folder is retrieved directly, instead of as a parent of the active spreadsheet.

    Reference: