如何优化我的脚本以应对Google Apps Script的6分钟时间限制？

Question

作为一个初学者，我编写了一个脚本，将所有我的Google Drive文件夹填充到Google表格中。即使脚本可以工作，但我遇到了错误：超过最大执行时间，而我的文件夹结果列表还没有完成。

我无法弄清楚如何处理/修复执行超时。我已经搜索了多个来源，但没有找到可以帮助我加快脚本速度的东西。

我的期望是脚本将超快地执行，并且“可能”的数千个文件夹列表将在最多1分钟内填充。

GAS脚本

function generateFolderList() {
  var rootFolder = DriveApp.getRootFolder();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd HH:mm:ss");
  var newSheet = spreadsheet.insertSheet(sheetName);
  newSheet.getRange("A1:N1").setValues([["structure_level", "parent_folder", "folder_name", "folder_description", "folder_id", "folder_url", "owner_email", "sharing_access", "sharing_permissions", "mime_type", "starred_status", "trashed_status", "date_created", "last_updated"]]);
  listFolders(rootFolder, "", newSheet, 1);
}

function listFolders(folder, parentName, sheet, level) {
  var folderName = folder.getName();
  var description = folder.getDescription();
  var folderId = folder.getId();
  var url = folder.getUrl();
  var owner = folder.getOwner().getEmail();
  var sharingAccess = folder.getSharingAccess();
  var sharingPermission = folder.getSharingPermission();
  var mimeType = "folder";
  var starred = folder.isStarred();
  var trashed = folder.isTrashed();
  var dateCreated = folder.getDateCreated();
  var lastUpdated = folder.getLastUpdated();

  var rowData = [level, parentName, folderName, description, folderId, url, owner, sharingAccess, sharingPermission, mimeType, starred, trashed, dateCreated, lastUpdated];
  sheet.appendRow(rowData);

  var subFolders = folder.getFolders();
  level++;
  while (subFolders.hasNext()) {
    var subFolder = subFolders.next();
    listFolders(subFolder, folderName, sheet, level);
  }
}

包含Apps脚本的Google表格在此处。

Answer 1

您的应用程序脚本会在每个文件夹或子文件夹出现时向电子表格写入。相反，您应该将信息收集到一个数组中，然后将整个数组写入电子表格。尝试这样做，尽管一开始有些单元格的值是对象而不是字符串，所以在决定要捕获这些对象的哪个属性之前，它不会起作用。我只是使用了toString()。

function generateFolderList() {
  var rootFolder = DriveApp.getRootFolder();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd HH:mm:ss");
  var newSheet = spreadsheet.insertSheet(sheetName);
  // 注意我创建了一个名为folders的数组，并将其传递给listFolders，而不是newSheet
  let folders = [["structure_level", "parent_folder", "folder_name", "folder_description", "folder_id", "folder_url", "owner_email", "sharing_access", "sharing_permissions", "mime_type", "starred_status", "trashed_status", "date_created", "last_updated"]];
  listFolders(rootFolder, "", folders, 1);
  // 完成后复制到电子表格
  newSheet.getRange(1,1,folders.length,folders[0].length.setValues(folders);
}

function listFolders(folder, parentName, folders, level) {
  var folderName = folder.getName();
  var description = folder.getDescription();
  var folderId = folder.getId();
  var url = folder.getUrl();
  var owner = folder.getOwner().getEmail();
  var sharingAccess = folder.getSharingAccess().toString();
  var sharingPermission = folder.getSharingPermission().toString();
  var mimeType = "folder";
  var starred = folder.isStarred();
  var trashed = folder.isTrashed();
  var dateCreated = folder.getDateCreated();
  var lastUpdated = folder.getLastUpdated();

  folders.push([level, parentName, folderName, description, folderId, url, owner, sharingAccess, sharingPermission, mimeType, starred, trashed, dateCreated, lastUpdated]);

  var subFolders = folder.getFolders();
  level++;
  while (subFolders.hasNext()) {
    var subFolder = subFolders.next();
    listFolders(subFolder, folderName, folders, level);
  }
}

参考资料

• 最佳实践