Google Apps Script遍历超大Drive文件夹至Sheet效率低下求助

我写了一段Google Apps Script，用来将Google Drive中一个超大文件夹（容量超150GB，部分子文件夹深度达10级）的内容写入Google Sheet。由于单次迭代无法完成任务，采用分批次处理方案：每10分钟触发一次脚本，先在Sheet中初始化根文件夹并标记为“To-do”；脚本从第一个“To-do”标记行开始逐行读取，若为待处理文件夹，则调用getFiles和getFolders获取其直接子文件和子文件夹，写入Sheet并将子文件夹标记为“To-do”，再将当前文件夹标记为“Done”。

该方案可按层级逐步遍历，但运行速度极慢，getRange、getValues、setValues等操作有时耗时约60秒，严重限制每次运行可处理的文件夹数量。我尝试使用flush方法和sleep（最长5秒）确保操作完成，但仍耗时过久。请问这属于正常现象吗？是否有优化技巧？

以下是我使用的代码：

var TODO_FLAG = "To-do";
var DONE_FLAG = "Done";
var DOING_FLAG = "Trop long";

function listFullTree(){

  var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONTENT_SHEET);

  var statusColumn = currentSheet.getRange(1, 1).getDataRegion(SpreadsheetApp.Dimension.ROWS).getValues().flat();
  var i = statusColumn.indexOf(TODO_FLAG);
  
  if (i == -1){
    return
  }

  var nbRows = currentSheet.getLastRow() - 1;
  var nbColumns = currentSheet.getLastColumn();

  var premier_passage = true;

  while (i <= nbRows){

    var currentRange = currentSheet.getRange(i+1, 1, 1, nbColumns);
    var currentRow = currentRange.getValues()[0];
    
    if (premier_passage) {
      currentSheet.getRange(i+1, 1).setValue(DOING_FLAG);
    }

    if (currentRow[3] == "Dossier" & currentRow[0] == TODO_FLAG){

      var id = currentRow[11];
      var folderName = currentRow[2];
      var parentPath = currentRow[1];
      var depth = currentRow[4];

      try{
        var folderName = DriveApp.getFolderById(id).getName();

        /** 
         * Write folders
         */ 
        var rows = getChildFolders(id, parentPath + "/" + folderName, depth+1);
        var nbFolders = rows.length;

        if (nbFolders > 0){
          currentSheet.getRange(nbRows + 2, 1, nbFolders, rows[0].length).setValues(rows);
        }

        /** 
         * Write files
         */ 
        var rows = getChildFiles(id, parentPath + "/" + folderName, depth+1);
        var nbFiles = rows.length;

        if (nbFiles > 0) {
          currentSheet.getRange(nbRows + nbFolders +2, 1, nbFiles, rows[0].length).setValues(rows);

          var size = 0;
          for(var j = 0; j < rows.length; j++) {
            size = size + rows[j][10];
          }
        }

        currentSheet.getRange(i+1,nbColumns-2,1,3).setValues([[nbFiles, nbFolders, size]])
        currentSheet.getRange(i+1, 1).setValue(DONE_FLAG);

        nbRows = nbRows + nbFiles + nbFolders;
      }
      catch(e)
      {
        currentSheet.getRange(i+1, 1).setValue(e);
      }
    }
}

耗时是否正常？ #

这种耗时不完全正常，但Google Apps Script与Sheet、Drive的交互本身存在性能瓶颈：

• 每次getRange/setValues都是跨服务的网络请求，频繁调用会累积延迟；
• 超大文件夹的文件/文件夹数量多，Drive API的批量获取本身有开销；
• 分批次触发的脚本运行环境有资源限制，单次运行超时时间为6分钟，频繁Sheet操作会快速消耗时间。

核心优化方案 #

1. 大幅减少Sheet交互次数 #

你的代码存在大量单行/单单元格读写，这是性能杀手：

• 批量读取全量数据：一次性读取Sheet所有数据到内存数组，后续逻辑直接操作数组，避免反复调用getRange；
• 批量写入更新：把需要修改的行（标记状态、统计数据）和新增的文件/文件夹行分别存入内存数组，最后仅用2-3次setValues完成所有写入；
• 合并文件夹与文件写入：将getChildFolders和getChildFiles返回的行合并成一个大数组，一次性写入，减少一次setValues调用。

2. 优化Drive API调用 #

• 移除重复获取操作：代码中先从Sheet取folderName，又调用DriveApp.getFolderById(id).getName()重复获取，直接删除重复调用；
• 改用Advanced Drive Service：启用高级Drive服务后，用Files.list批量获取子项，比DriveApp的循环getFolders/getFiles效率更高；
• 缓存已获取数据：用CacheService缓存文件夹的子项信息，避免重复请求（即使当前标记机制避免了重复处理，也能预防异常场景）。

3. 简化循环与逻辑 #

• 删除冗余标记：premier_passage仅在第一次循环时生效，可直接在找到第一个To-do行后设置状态，无需放在循环内；
• 优化循环终止条件：每次处理完一个文件夹后，重新从内存数组中查找下一个To-do行，避免因新增行导致的循环范围错误；
• 提前计算统计数据：在getChildFiles方法内直接计算总大小，不用写入后再循环累加，减少内存占用和计算时间。

4. 调整脚本触发策略 #

• 延长触发间隔：10分钟间隔过于频繁，冷启动和Auth验证会消耗额外时间，改为15-30分钟一次，让单次运行处理更多文件夹；
• 合理使用flush：仅在批量写入完成后调用一次，避免频繁调用带来的网络开销；
• 删除sleep：sleep会浪费脚本运行时间，依赖批量操作减少请求次数即可避免Drive API限流。

优化后核心代码示例 #

var TODO_FLAG = "To-do";
var DONE_FLAG = "Done";
var DOING_FLAG = "Trop long";

function listFullTree(){
  var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONTENT_SHEET);
  
  // 一次性读取全量数据到内存
  var allData = currentSheet.getDataRange().getValues();
  var statusColumn = allData.map(row => row[0]);
  var i = statusColumn.indexOf(TODO_FLAG);
  
  if (i === -1) return;

  var newRows = []; // 存储新增的文件/文件夹行

  // 处理当前待办文件夹
  var currentRow = allData[i];
  if (currentRow[3] === "Dossier" && currentRow[0] === TODO_FLAG) {
    // 标记为处理中
    allData[i][0] = DOING_FLAG;
    
    var id = currentRow[11];
    var folderName = currentRow[2];
    var parentPath = currentRow[1];
    var depth = currentRow[4];
    var nbColumns = allData[0].length;

    try {
      // 获取子文件夹和文件并合并
      var folderRows = getChildFolders(id, parentPath + "/" + folderName, depth+1);
      var fileRows = getChildFiles(id, parentPath + "/" + folderName, depth+1);
      newRows = [...folderRows, ...fileRows];

      // 计算统计数据
      var nbFolders = folderRows.length;
      var nbFiles = fileRows.length;
      var totalSize = fileRows.reduce((sum, row) => sum + row[10], 0);

      // 更新当前行的状态和统计数据
      allData[i][0] = DONE_FLAG;
      allData[i][nbColumns-3] = nbFiles;
      allData[i][nbColumns-2] = nbFolders;
      allData[i][nbColumns-1] = totalSize;

      // 批量写入更新后的旧数据和新增数据
      currentSheet.getRange(1, 1, allData.length, nbColumns).setValues(allData);
      if (newRows.length > 0) {
        currentSheet.getRange(allData.length + 1, 1, newRows.length, newRows[0].length).setValues(newRows);
      }

    } catch(e) {
      allData[i][0] = e.toString();
      currentSheet.getRange(i+1, 1).setValue(e.toString());
    }
  }
}

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