How to get list all sub folders name from parent folders in google sheet with apps script google drive

Question

How can i list all sub folders name from google drive into google sheets?

Here is the code, it's working but I cant put them in to google sheets.

enter image description here In the place of logger i need replace the google sheet. Please help me out in this scenario?

Answer 1

If you are planning to use custom function to write the list of sub-folder names in Google Sheets, It will not work because your function require authorization when using DriveApp it will throw an error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function as stated here.

You can write your sub-folder names in Google Sheets using Spreadsheet Service. But you need to specify the specific sheet and the cell where you want to write your data.

Sample Code:

function findSubFolder(){
  var childFolder = DriveApp.getFolderById('xxxxxxxx').getFolders();
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeCell = activeSheet.getActiveCell();

  var childNames = [];
  while (childFolder.hasNext()) {
    var child = childFolder.next();
    Logger.log(child.getName());
    childNames.push([child.getName()]);
  }
  
 
 activeSheet.getRange(activeCell.getRow(),activeCell.getColumn(),childNames.length,1).setValues(childNames);
}

What it does?

1. Get your active sheet using SpreadsheetApp.getActiveSheet() and the active cell using Sheet.getActiveCell()
2. Get all your sub-folder names and add that to childNames array using Array.prototype.push()

Notice that when I pushed the child names I used [sub-folder name], This is needed when we want use Range.setValues() which accepts a 2-d array where I want to have multiple rows.

3. Write the childNames array to the active sheet starting from the active cell. Using Sheet.getRange(row, column, numRows, numColumns) and Range.setValues(values)

Output:

---

If you want to write the sub-folder names on a single cell(in your active cell), you can replace

activeSheet.getRange(activeCell.getRow(),activeCell.getColumn(),childNames.length,1).setValues(childNames);

to

activeCell.setValue(childNames.flat().toString());

Output: