创建Google Drive触发器同步子子文件夹名至Google Sheets

创建一个触发器，每当指定Google Drive文件夹发生变更（如上传文件/文件夹）时，自动将该文件夹下的子子文件夹名称输入/更新至Google Sheets的单元格中。

该触发器需自动执行以下函数：

var folderId = 'folderId';

function listFolders(){
  getFolderTree(folderId, false);
};

function listAll(){
  getFolderTree(folderId, true); 
};

function getFolderTree(folderId, listAll) {
  try {
    var parentFolder = DriveApp.getFolderById(folderId);

    var file, data, sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Resources");
    sheet.clear('A1');
    sheet.appendRow(["Name"]);

    getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);

  } catch (e) {
    Logger.log(e.toString());
  }
};

function getChildFolders(parentName, parent, data, sheet, listAll) {
  var childFolders = parent.getFolders();

  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    // Logger.log("Folder Name: " + childFolder.getName());
    data = [ 
      childFolder.getName()
    ];
    sheet.appendRow(data);

    var files = childFolder.getFiles();
    while (listAll & files.hasNext()) {
      var childFile = files.next();
      // Logger.log("File Name: " + childFile.getName());
      data = [ 
        parentName + "/" + childFolder.getName() + "/" + childFile.getName(),
        childFile.getName()
      ];
      // Write
      sheet.appendRow(data);
    }

    getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll);  
  }
};

function appendToColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Resources");
  var data = "End";
  //Get the column where you want to append the data
  var range = sheet.getRange("A1:A").getValues().flat();
  var row = range.filter(String).length;
  Logger.log(row);
  sheet.getRange(row+1,2).setValue(data);
}

预期流程 #

• 将文件夹上传至指定Google Drive文件夹
• 确认子子文件夹的结构内容
• 触发器自动将子子文件夹名称同步到Google Sheets对应单元格

目前尚未找到实现该需求的可安装触发器或简单触发器完整解决方案。

内容的提问来源于stack exchange，提问作者crumb