Home > Net >  List every file and folder of a shared drive in a spreadsheet with Apps Script
List every file and folder of a shared drive in a spreadsheet with Apps Script

Time:02-26

I found a script that works flawlesly for My Drive that I want to adapt to also support Shared Drives.

    function onOpen() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('List Files/Folders')
    .addItem('List All Files and Folders', 'listFilesAndFolders')
    .addToUi();
};

function listFilesAndFolders(){
  var folderId = Browser.inputBox('Enter folder ID', Browser.Buttons.OK_CANCEL);
  if (folderId === "") {
    Browser.msgBox('Folder ID is invalid');
    return;
  }
  getFolderTree(folderId, true); 
};

// Get Folder Tree
function getFolderTree(folderId, listAll) {
  try {
    // Get folder by id
    var parentFolder = DriveApp.getFolderById(folderId);
    
    // Initialise the sheet
    var file, data, sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
    sheet.appendRow(["Full Path", "Name","Type" ,"Date", "URL", "Last Updated", "Description", "Size","Owner Email"]);
    
    // Get files and folders
    getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);
  } catch (e) {
    Logger.log(e.toString());
  }
};

// Get the list of files and folders and their metadata in recursive mode
function getChildFolders(parentName, parent, data, sheet, listAll) {
  var childFolders = parent.getFolders();
 
  // List folders inside the folder
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    var folderId = childFolder.getId();
    data = [ 
      parentName   "/"   childFolder.getName(),
      childFolder.getName(),
      "Folder",
      childFolder.getDateCreated(),
      childFolder.getUrl(),
      childFolder.getLastUpdated(),
      childFolder.getDescription(),
      childFolder.getSize()/1024,
      childFolder.getOwner().getEmail()
    ];
    // Write
    sheet.appendRow(data);
    
    // List files inside the folder
    var files = childFolder.getFiles();
    while (listAll & files.hasNext()) {
      var childFile = files.next();
      data = [ 
        parentName   "/"   childFolder.getName()   "/"   childFile.getName(),
        childFile.getName(),
        "Files",
        childFile.getDateCreated(),
        childFile.getUrl(),
        childFile.getLastUpdated(),
        childFile.getDescription(),
        childFile.getSize()/1024,
        childFile.getOwner().getEmail(),
      ];
      // Write
      sheet.appendRow(data);
    }
    // Recursive call of the subfolder
    getChildFolders(parentName   "/"   childFolder.getName(), childFolder, data, sheet, listAll);  
  }
};

I honestly still don't fully understand how

{supportsAllDrives: true}

works but I'll assume that it is exactly what's missing.

I've been trying to get some more information about it, but what I've found is't exactly useful for me and it's not a complete explanation on how to use it.

I'm very new to Apps Script but I'm doing my best to make fully operational code or at least understand it, so I would really appreciate if anyone could link me to any good pages about it.

CodePudding user response:

I have a function that copies the information to a sheet for me all it needs is a folder. You can modify as you require:

Starting function:

function AddDataValidation(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');  
  const folder = DriveApp.getFolderById("folder id");
  getFnF(folder);
}

The recursive function:

var level = 1;
function getFnF(folder = DriveApp.getRootFolder()) {
  const ss = SpreadsheetApp.getActive();//active spreadsheet
  const sh = ss.getSheetByName('Sheet0')//change sheet name
  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--;
}

CodePudding user response:

{supportsAllDrives: true} is used for Drive API. But, fortunately, in the current stage, when the file ID and folder ID is used, the files and folders in the shared drive can be retrieved using Drive service (DriveApp). But unfortunately, the owner of files and folders cannot be retrieved. I thought that this might be the reason of your current issue of your showing script. If my understanding is correct, how about the following modification?

From:

data = [ 
  parentName   "/"   childFolder.getName(),
  childFolder.getName(),
  "Folder",
  childFolder.getDateCreated(),
  childFolder.getUrl(),
  childFolder.getLastUpdated(),
  childFolder.getDescription(),
  childFolder.getSize()/1024,
  childFolder.getOwner().getEmail()
];

To:

var ownerOfFolder = childFolder.getOwner();
data = [ 
  parentName   "/"   childFolder.getName(),
  childFolder.getName(),
  "Folder",
  childFolder.getDateCreated(),
  childFolder.getUrl(),
  childFolder.getLastUpdated(),
  childFolder.getDescription(),
  childFolder.getSize()/1024,
  ownerOfFolder ? ownerOfFolder.getEmail() : ""
];

And also, please modify as follows.

From:

data = [ 
  parentName   "/"   childFolder.getName()   "/"   childFile.getName(),
  childFile.getName(),
  "Files",
  childFile.getDateCreated(),
  childFile.getUrl(),
  childFile.getLastUpdated(),
  childFile.getDescription(),
  childFile.getSize()/1024,
  childFile.getOwner().getEmail(),
];

To:

var ownerOfFile = childFile.getOwner();
data = [ 
  parentName   "/"   childFolder.getName()   "/"   childFile.getName(),
  childFile.getName(),
  "Files",
  childFile.getDateCreated(),
  childFile.getUrl(),
  childFile.getLastUpdated(),
  childFile.getDescription(),
  childFile.getSize()/1024,
  ownerOfFile ? ownerOfFile.getEmail() : "",
];

Note:

  • When I tested your script reflected the above modification to the shared Drive, I could confirm that the script works.
  • Related