I need to list all the files in subfolders using google script

Question

I am trying to make a summary sheet for all files in a folder. This is what I have so far, but I cannot figure out how to list the files in the subfolders of "folderid". Any ideas would be greatly appreciated.

//GLOBAL VARIABLES

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

function onOpen() {
    ss.getRange(1, 1, 100, 10).clear({contentsOnly: true})

    var id = "folderid";
    var arr = [["CAPITAL PROJECT", "URLs", "OWNER"]];

    var f = DriveApp.getFolderById(id).getFiles()
    while (f.hasNext()) {

        var file = f.next();
        var name = file.getName()
        var url = file.getUrl()
        var own = file.getOwner().getName()

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

        ss.getRange(1,1, arr.length, arr[0].length).setValues(arr);
    }

    ss.getRange(2,1, arr.length, arr[0].length).sort(1)

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

look up the concept of recursion. – Zig Mandel May 23 '16 at 15:43 — Zig Mandel, May 23 '16 at 15:43

score 1 · Accepted Answer · answered May 23 '16 at 15:57

Once you get the main folder, you can get the sub-folders. If there are multiple sub-folders, you'll need an inner loop:

I've tested this code and it works:

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 = "Put_Your_FolderID_Here";
  arr = [["CAPITAL PROJECT", "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()
}

I need to list all the files in subfolders using google script

1 Answers1

Linked