Get folders and files listed in Google Sheets from a google drive folder using a Google Apps script

Question

I have a Google sheet where a user can cut and paste the google drive folder URL, and it will extract the folder ID. This is extracted in a named range "FolderID".

I then want to list the folders and files in the folder (subfolders and files within as well if possible) into the worksheet "Files".

I have played around with another script I wrote, but am coming up with an error for the initial folders list (I haven't gotten to the files list yet).

I get this error: Exception: Unexpected error while getting the method or property getFolderById on object DriveApp.

The current script is:

function listFoldersAndFilesInAFolder() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Files');
  var foldername = ss.getRangeByName('FolderID');
  sh.clearContents();
  sh.appendRow(["Name", "Link"]);
  var fA = [];
  var folders = DriveApp.getFolderById(foldername).getFolders();//Enter folder id
  while (folders.hasNext()) {
    var folder = folders.next();
    var folderURLstart = "https://drive.google.com/drive/folders/"
    fA.push([folder.getName(), folderURLstart + folder.getId()]);
  }
  sh.getRange(sh.getLastRow() + 1, 1, fA.length, 2).setValues(fA);
  sh.getRange(2, 1, sh.getLastRow() - 1, 2).sort({ column: 1, ascending: true });
}

Can anyone help:

fix the script above so I can list folders.
direct me on what to add to list files, and subfolders and files?

In your situation, is this thread useful? stackoverflow.com/q/67385200 — Tanaike

Mike Steelson Mike Steelson · Accepted Answer · 2021-06-11T02:09:06

To add list of files, subfolders and files, use

function listFilesAndFolders() {
  var folderid = '18akqHAN7PSPMnG3h5HpCskQsMCv4TqCM'; // 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);
  }
}

https://docs.google.com/spreadsheets/d/13fHMrWR9OOWa045uJbkz67YoVkOG4Pg6ScvrRpE-WBc/copy

Get folders and files listed in Google Sheets from a google drive folder using a Google Apps script

1 Answers