-1

How would I get a list of folders in a google drive directory on to a googlespread sheet that will include the name of the folders and the count of the files that are in that folder on to a google sheet.

function listFilesAndFolders() {
  var folderid = '<the google folder ID>'; // change FolderID
  var sh = SpreadsheetApp.getActiveSheet();
  sh.clear();
  sh.appendRow(["parent","folder", "name", "update", "size", "URL", "ID", "description", "type"]);
  try {
    var parentFolder =DriveApp.getFolderById(folderid);
    listFiles(parentFolder,parentFolder.getName())
    listSubFolders(parentFolder,parentFolder.getName());
  } catch (e) {
    Logger.log(e.toString());
  }
}

function listSubFolders(parentFolder,parent) {
  var childFolders = parentFolder.getFolders();
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    Logger.log("Fold : " + childFolder.getName());
    listFiles(childFolder,parent)
    listSubFolders(childFolder,parent + "|" + childFolder.getName());
  }
}

function listFiles(fold,parent){
  var sh = SpreadsheetApp.getActiveSheet();
  var data = [];
  var files = fold.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    data = [ 
      parent,
      fold.getName(),
      file.getName(),
      file.getLastUpdated(),
      file.getSize(),
      file.getUrl(),
      file.getId(),
      file.getDescription(),
      file.getMimeType()
      ];
    sh.appendRow(data);
  }
}

var folders = DriveApp.getFolders();
while (folders.hasNext()) {
  var folder = folders.next();
  Logger.log(folder.getName());
}
Johnny Quest
  • 41
  • 1
  • 8
  • First try [`getFolders`](https://developers.google.com/apps-script/reference/drive/folder) and [`getFiles`](https://developers.google.com/apps-script/reference/drive/file-iterator) yourself. – idfurw Oct 24 '22 at 03:38
  • Thank you for getting back to me. How do I print just the name of the folder the type of file and the count of files in the folders. – Johnny Quest Oct 24 '22 at 03:50

1 Answers1

1

Get Files and Folders:

var level = 1;
function getFnF(folder = DriveApp.getRootFolder()) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0')
  const files = folder.getFiles();
  sh.getRange(sh.getLastRow() + 1, level).setValue(folder.getName()).setFontWeight('bold');
  if (files.hasNext()) {
    sh.getRange(sh.getLastRow() + 1, level).setValue('Files:');
  }
  while (files.hasNext()) {
    let file = files.next();
    let firg = sh.getRange(sh.getLastRow() + 1, level + 1);
    firg.setValue(Utilities.formatString(file.getName()));
  }
  const subfolders = folder.getFolders()
  while (subfolders.hasNext()) {
    let subfolder = subfolders.next();
    level++;
    getFnF(subfolder);
  }
  level--;
}

function getFilesAndFolders() {
  const fldr = DriveApp.getRootFolder();
  SpreadsheetApp.getActive().getSheetByName('Sheet0').clearContents();
  SpreadsheetApp.getActive().toast('Entry');
  getFnF(fldr);
  SpreadsheetApp.getActive().toast('EOF');
}

Makes a list of all files and folders on to Sheet0. You specify a starting folder or root folder for the entire drive. Does not include shared drives.

This a solution for shared drives: https://stackoverflow.com/a/71270825/7215091

Cooper
  • 59,616
  • 6
  • 23
  • 54