How can I optimize my script to cope with Google Apps Script's 6-minute time limit?

Question

As a beginner, I wrote a script to populate all my Google Drive folders into Google Sheets. Even though the script works, I'm getting Error: Maximum execution time exceeded, and my folder results list isn't complete yet.

I can't figure out how to handle/fix execution timeouts. I've searched multiple sources but haven't found anything that can help me speed up my script.

My expectation is that the script will execute super fast and the list of "possibly" thousands of folders will be populated in 1 minute at most.

GASScript

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 with Apps Script is here.

Answer 1

Your application script writes to the spreadsheet as each folder or subfolder appears. Instead, you should collect the information into an array and then write the entire array to a spreadsheet. Try this, although initially some of the cells' values ​​are objects rather than strings, so it won't work until you decide which property of those objects you want to capture. I just used 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);
  }
}

References

• Best Practices