Wie kann ich mein Skript optimieren, um das 6-Minuten-Zeitlimit von Google Apps Script einzuhalten?

Question

Als Anfänger habe ich ein Skript geschrieben, um alle meine Google Drive-Ordner in Google Sheets zu füllen. Obwohl das Skript funktioniert, stoße ich auf 错误：超过最大执行时间 und meine Ordner-Ergebnisliste ist noch nicht vollständig.

Ich kann nicht herausfinden, wie ich Ausführungszeitüberschreitungen behandeln/beheben kann. Ich habe mehrere Quellen durchsucht, aber nichts gefunden, was mir helfen könnte, mein Skript zu beschleunigen.

Meine Erwartung ist, dass das Skript superschnell ausgeführt wird und die Liste mit „möglicherweise“ Tausenden von Ordnern in höchstens 1 Minute gefüllt wird.

GAS-Skript

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

Google Sheets mit Apps Script ist da.

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

参考资料

• 最佳实践