使用 Apps 脚本在电子表格中列出共享云端硬盘的每个文件和文件夹

我发现一个脚本可以完美地适用于我的驱动器,我想适应它也支持共享驱动器。

    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);  
  }
};

老实说,我仍然不完全了解如何

{supportsAllDrives: true}

有效,但我会假设它正是缺少的。

我一直在尝试获取有关它的更多信息,但我发现的内容对我来说并不完全有用,也不是关于如何使用它的完整解释。

我对 Apps Script 很陌生,但我正在尽我所能制作完全可操作的代码或至少理解它,所以如果有人能将我链接到任何关于它的好页面,我将不胜感激。

stack overflow List every file and folder of a shared drive in a spreadsheet with Apps Script
原文答案
author avatar

接受的答案

{supportsAllDrives: true} 用于 Drive API。但是,幸运的是,在现阶段,当使用文件ID和文件夹ID时,可以使用Drive服务(DriveApp)检索共享驱动器中的文件和文件夹。但不幸的是,无法检索文件和文件夹的所有者。我认为这可能是您当前的放映脚本问题的原因。如果我的理解是正确的,那么下面的修改呢?

### 从:

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

### 至:

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() : ""
];

另外,请进行如下修改。

### 从:

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

### 至:

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() : "",
];

### 笔记:

  • 当我测试您的脚本反映了对共享驱动器的上述修改时,我可以确认该脚本有效。

答案:

作者头像

我有一个功能可以将信息复制到我的工作表中,它只需要一个文件夹。您可以根据需要进行修改:

启动功能:

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

递归函数:

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--;
}

共享驱动器中的文件夹:

生成带有标题、ID 和路径的表格

function getFoldersInASharedFolder() {
  let tr = [];
  let token = '';
  let page = 0;
  do {
    let r = Drive.Files.list({ corpora: 'drive', includeItemsFromAllDrives: true, supportsTeamDrive: true, supportsAllDrives: true, driveId: "driveId", pageToken: token,q: "mimeType = 'application/vnd.google-apps.folder'" });
    let obj = JSON.parse(r);
    tr.push(obj)
    token = obj.nextPageToken
  } while (token != null)

  let folder = DriveApp.getFolderById(gobj.globals.testfolderid);
  folder.createFile(`SharedDriveList ${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")}`, JSON.stringify(tr), MimeType.PLAIN_TEXT);

  let html = '<style>td,th{border:1px solid black;font-size: 16px;}</style><table><tr><th>Title</th><th>Id</th><th>Path</th></tr>';
  tr.forEach((o, i) => {
    o.items.forEach(item => {
      if (item.mimeType = "application/vnd.google-apps.folder") {
        html += `<tr><td>${item.title}</td><td>${item.id}</td><td>${getPathAllDrivesFromId(item.id)}</td></tr>`;
      }
    })
  });
  html += '</table><input type="button" value="exit" onclick="google.script.host.close()" />';
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html).setHeight(500).setWidth(1200), `Folders in: ${JSON.parse(Drive.Drives.get("driveid")).name}`);
}

下面的函数将找到您所有的共享驱动器并为所有文件和文件夹创建一个缩进树。它还为每个共享驱动器选择并填充一个工作表,其中包含文件名和指向每个文件夹和文件的链接。

function sharedDriveTrees() {
  const ss = SpreadsheetApp.openById(gobj.globals.sharedssid);//need to change the ssid for the output spreadsheet
  const r = Drive.Drives.list();
  const drives = JSON.parse(r).items;
  const shts = ss.getSheets().filter((sh, i) => i < drives.length).filter(e => e);
  var trees = [];
  drives.forEach((obj, i) => {
    obj["title"] = obj.name;
    let ob =JSON.parse(Drive.Files.get(obj.id,{ supportsAllDrives: true, supportsTeamDrives: true }));
    obj["alternateLink"] = ob.alternateLink;
    Logger.log('Drive Title: %s Time: %s', obj.title, Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), "HH:mm:ss"));
    shts[i].setName(`${obj.title}n${Utilities.formatDate(new Date(),ss.getSpreadsheetTimeZone(),"yyyy.MM.dd HH:mm:ss")}`);
    let tree = [];
    CacheService.getScriptCache().put("tree", JSON.stringify(tree), 60);
    level = 1;
    gff(obj)
    tree = JSON.parse(CacheService.getScriptCache().get("tree"));
    let l = tree.reduce((a, c) => {
      if (c.length > a) {
        a = c.length;
      }
      return a;
    }, 0);
    tree.forEach((a, j) => {
      if ((l - a.length) > 0) {
        let arr = [...Array.from(new Array(l - a.length).keys(), x => "")];
        tree[j] = a.concat(arr);
      }
    });
    trees.push(tree);
    const sh = shts[i];
    sh.clearContents();
    sh.getRange(1, 1, tree.length, tree[0].length).setValues(tree);
    SpreadsheetApp.flush();
  });
}

level = 1;
function gff(fobj) {
  //Logger.log('Drive Title: %s', fobj.title);
  const r = Drive.Children.list(fobj.id);
  const fldrMime = "application/vnd.google-apps.folder";
  let tree = JSON.parse(CacheService.getScriptCache().get("tree"));
  let files = [];
  let subfolders = [];
  fobj["level"] = level;
  let children = JSON.parse(r).items;
  children.forEach((obj, i) => {
    let o = JSON.parse(Drive.Files.get(obj.id, { supportsAllDrives: true, supportsTeamDrives: true }));
    o["level"] = level;
    if (o.mimeType == fldrMime) {
      subfolders.push(o);
    } else {
      files.push(o);
    }
  });
  //Logger.log('level: %s', level);
  let arr1 = [...Array.from(new Array(level).keys(), x => { if (x < (level - 1)) { return '' } else { return `=HYPERLINK("${fobj.alternateLink}","${fobj.title}")` } })];
  //Logger.log('arr1: %s', JSON.stringify(arr1));
  tree.push(arr1)
  if (files && files.length > 0) {
    files.forEach(obj => {
      let arr2 = [...Array.from(new Array(level + 1).keys(), x => { if (x < (level)) { return '' } else { return `=HYPERLINK("${obj.alternateLink}","${obj.title}")` } })];
      //Logger.log('arr2: %s', JSON.stringify(arr2));
      tree.push(arr2)
    })
  }
  CacheService.getScriptCache().put("tree", JSON.stringify(tree), 60);
  subfolders.forEach(obj => {
    level++;
    obj.level = level;
    CacheService.getScriptCache().put("tree", JSON.stringify(tree), 60);
    gff(obj);
    tree = JSON.parse(CacheService.getScriptCache().get("tree"))
  });
  level--;
  return;
}

enter image description here