The folloing code works great to get list of folders, but i cannot find a way to get subfolders upto 3 levels.

can anyone suggest how it can be achievable?

function generateFolderIndex(id) {
   var folder = DriveApp.getFolderById('[ID]');
   var subFolders = folder.getFolders();
   var childFolders = subFolders
   var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Projekt');

   sheet.clear();
   sheet.appendRow(["Projekt", "Länk", "Skapad"]);

while (subFolders.hasNext())
  {    
  var childFolder = childFolders.next(); 

  var folderID = childFolder.getId();
  var name = childFolder.getName()
  var link = childFolder.getUrl()
  var date = childFolder.getDateCreated()


   data = [name, link, date, ]
    sheet.appendRow(data);

  }
};

CodePudding user response：

If you need upto 3 levels, and only need data in specified structure you mentioned in your question.

How about trying this :

function generateFolderIndex(id) 
{
  var folder = DriveApp.getFolderById('[ID]'); //replace it with parent folder
  var subFolders = folder.getFolders();
  var childFolders = subFolders
  var ss = SpreadsheetApp.openById('Projekt')
  var sheet = ss.getSheetByName('_cache_');
  sheet.clear();
  sheet.appendRow(["Projekt", "Länk", "Skapad"]);
  
  while (subFolders.hasNext())// level1
  {    
    var childFolder = childFolders.next(); 
    var folderID = childFolder.getId();
    var name = childFolder.getName()
    var link = childFolder.getUrl()
    var date = childFolder.getDateCreated()     
    data = [name, link, date]
    sheet.appendRow(data);
    var folder = DriveApp.getFolderById(folderID);
    var level2 = folder.getFolders()
    var levelChild = level2
          while (level2.hasNext()) // level 2
          { 
            var childFolder = levelChild.next(); 
            var folderID = childFolder.getId();
            var name = childFolder.getName()
            var link = childFolder.getUrl()
            var date = childFolder.getDateCreated()
            
            data = [name, link, date]
            sheet.appendRow(data);
            
            var folder = DriveApp.getFolderById(folderID);
            var level3 = folder.getFolders()
            var levelChild = level3
            
                while (level3.hasNext()) // level 3
                { 
                  var childFolder = levelChild.next(); 
                  var folderID = childFolder.getId();
                  var name = childFolder.getName()
                  var link = childFolder.getUrl()
                  var date = childFolder.getDateCreated()
                  
                  data = [name, link, date]
                  sheet.appendRow(data);
                  
                }
            
          }
    
  }
}

CodePudding user response：

I believe your goal is as follows.

• You want to retrieve the folder list including the subfolders in the 3rd level using Google Apps Script.

In this case, how about the following modification?

I thought that when appendRow is used in a loop, the process cost becomes high. Ref So, in this answer, the modification for this issue is also included.

Modified script:

function getData(folder, n = 4, res = []) {
  if (n != 4) res.push([folder.getName(), folder.getUrl(), folder.getDateCreated()]);
  if (--n == 0) return;
  const subfolders = folder.getFolders();
  while (subfolders.hasNext()) getData(subfolders.next(), n, res);
  return res;
}

function generateFolderIndex(id = '[ID]') {
  const folder = DriveApp.getFolderById(id);
  const values = [["Projekt", "Länk", "Skapad"], ...getData(folder)];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Projekt');
  sheet.clear();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• In your script, id of function generateFolderIndex(id) { might not be used. So this modification uses id. When you use this script, please set '[ID]' or give it as the argument.

• In this modified script, when n = 4 is changed, you can change the level you want to retrieve. n = 4 means the level including the top folder. So, for example, if you want to retrieve 2 levels under the top folder, please modify it to n = 3.

Note:

• Also, you can use a Google Apps Script library (FilesApp) for retrieving the file list under the specific folder.