Get list of Google Drive folders into Google Sheet

Question

I am trying to be a little organized, and want a way to get a list of all folders in my Google Drive in Google Sheet.

It should not have the Files, only folders.

I was able to find following code, but it lists file name too, I only want folders. And also a way to specify how deep in hierarchy should it go, like 2 levels or 3 levels etc.

// replace your-folder below with the folder for which you want a listing
function listFolderContents() {
 var foldername = 'your-folder';
 var folderlisting = 'listing of folder ' + foldername;
 
 var folders = DriveApp.getFoldersByName(foldername)
 var folder = folders.next();
 var contents = folder.getFiles();
 
 var ss = SpreadsheetApp.create(folderlisting);
 var sheet = ss.getActiveSheet();
 sheet.appendRow( ['name', 'link'] );
 
 var file;
 var name;
 var link;
 var row;
 while(contents.hasNext()) {
   file = contents.next();
   name = file.getName();
   link = file.getUrl();
   sheet.appendRow( [name, link] );     
 }  
};

Answer 1

There are a variety of precedents for listing Google Drive folders and subfolders (with and without file information). Some have been suggested in the comments, and I accept that removing references for files can be sometimes easier said than done.

This answer is based on an some excellent answers offered in StackOverflow:

• List all folders and subfolders in a google sheets script
• Google script listing folders, subfolders and files
• Google apps script - iterate folder and subfolder

The original script is by @AnupamBera. It has/had several nice features such as:

• creating a "treelike" structure of the folders and sub-folders.
• printing the files in each folder and sub-folder (deleted: not required by OP)
• grouping sub-folder content. (deleted: not required by OP)
• creates a Spreadsheet in which the folder information is to be placed. (deleted: not required by OP)

Instructions:

• Insert the ID of the folder to be analysed.
• Insert the sheet name where the output is to be displayed
• Run genFolderTree

---

function genFolderTree() {

  try {

    //Replace the folder id with the folder id of the parent folder in the Google Drive, where you would like to start sorting through the drive. 
    var folderId = 'FOLDERIDGOESHERE'; 
    
    // Get folder by id
    var foldername = DriveApp.getFolderById(folderId);
    var parentFolder = DriveApp.getFoldersByName(foldername).next();

    // select the sheet for output
    var ss = SpreadsheetApp.getActiveSpreadsheet()

    //Replace the sheet name with the sheet name in the active spreadsheet where you want the output to be displayed. 
    var sheetname = "SHEETNAMEGOESHERE"
    var sheet = ss.getSheetByName(sheetname)

    // clear any existing contents
    sheet.clear();

    // create a array for pasting contentrs into the sheet
    var frontCell = []

    // start with the folder that you chose
    sheet.appendRow([foldername]).getCurrentCell().setFontWeight('bold').setFontColor('red');
    frontCell.push(" ");

    // get the sub-folders
    getChildNode(parentFolder,sheet,frontCell);


  } catch (e) {

    Logger.log(e.toString());

  }

}


function getChildNode(parent,sheet,frontCell) {

  var childFolders = parent.getFolders();
  while (childFolders.hasNext()) {

    var childFolder = childFolders.next();

    // assign the folder name
    // append it to the sheet
    // set the formatting
    // remove the folder name
    frontCell.push(childFolder.getName())
    sheet.appendRow(frontCell);
    sheet.getRange(sheet.getLastRow(), frontCell.length).setFontWeight('bold').setFontColor('red');
    frontCell.pop();
    

    frontCell.push(" ");
    // Recursive call for any sub-folders
    getChildNode(childFolder,sheet,frontCell);
    frontCell.pop();
  }

}