How do I retrieve and store files and folders structure in a google spreadsheet using apps script?

Question

I know this question has been asked before, but I am trying to go a bit deeper in the problem. I have this apps script code I have taken online and I am using it in a google spreadsheet, to scan for a list of folders.

In short, I have a (shared) folder structure with most likely over 1000 sub-folders, which contain sub-subfolders, that looks similar to this:

MAIN FOLDER

• FOLDER 1

  • FILE 1.1
  • SUBFOLDER 1.A
    • SUBFILE 1.A.2
    • SUBFILE 1.A.3

• FOLDER 2

  • FILE 2.1
  • SUBFOLDER 2.A
    • SUBFILE 2.A.2
    • SUBFILE 2.A.3

• FOLDER 3

  • FILE 3.1
  • SUBFOLDER 3.A
    • SUBFILE 3.A.2
    • SUBFILE 3.A.3 ...

I am trying to fix 2 things: 1) My function obviously breaks when running because there are too many folders and files. I don't think it's due to the limit of rows in a spreadsheet. So, ideally, I would have to manually batch 200 folders in the main folder, run the function, and re-iterate. Any hints how to avoid that, and how to potentially, dump temporary results in a first spreadsheet and create a new spreadsheet if it's a caching problem?

2) I am not able to retrieve subfolders details and the details of the files it contains. Can anyone help adapt the code to go deeper in the folder hierarchy? Do I need some kind of loop function here?

With the code provided hereunder which I have used and tested a few times, I can only retrieve the folders 1,2,3 and files 1.1, 2.1, 3.1...but not subfolders or subfiles.

Fixing this function, would save me weeks of manual work. Any help is welcomed. Thanks in advance for your help.

function getAndListFilesInFolder() {
  var arr,f,file,folderName,subFolders,id,mainFolder,name,own,sh,thisSubFolder,url;

  sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  sh.getRange(1, 1, 100, 10).clear({contentsOnly: true})

  id = "FOLDER ID HERE";
  arr = [["FILE NAME", "URLs", "OWNER","FOLDER"]];


  mainFolder = DriveApp.getFolderById(id);
  subFolders = mainFolder.getFolders();
  folderName = mainFolder.getName();

  f = mainFolder.getFiles();

  while (f.hasNext()) {
    file = f.next();
    name = file.getName()
    url = file.getUrl()
    own = file.getOwner().getName()

    arr.push([name, url, own, folderName]);
  };

  while (subFolders.hasNext()) {
    thisSubFolder = subFolders.next();
    f = thisSubFolder.getFiles();
    folderName = thisSubFolder.getName();

    while (f.hasNext()) {
      file = f.next();
      name = file.getName()
      url = file.getUrl()
      own = file.getOwner().getName()

      arr.push([name, url, own,folderName]);  
    };
  };

  sh.getRange(1,1, arr.length, arr[0].length).setValues(arr);
  sh.getRange(2,1, arr.length, arr[0].length).sort(1);


  //var second = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2")
  //second.activate()
}

Answer 1

The reason you aren't seeing any of the subfolders is because you are not actually adding them to your array (arr) at any time.

What you really need here is is to write a recursive function, which receives a folder and returns itself plus it's file's details details in an array. When you encounter a folder, you pass that folder back to the same function.

Investigate recursion and recursive functions.

Here is a brief example you could expand, it'll need some tweaking since I'm writing this off the top of my head:

  function getFilesFromFolder(folder){
    var arr = [];
    arr.push([folder.getName(),folder.getUrl(),folder.getOwner().getName()])
    var subfolders = folder.getFolders();
    var files = folder.getFiles():

    while(files.hasNext()){
      arr.push(/*file details*/);
    }

    //recurse into subfolders here.
    while(subFolders.hasNext()){    
      arr.concat(getFilesFromFolder(subFolders.next());  //notice how we call the function from within itself.
    }

    return arr;
  }