Google Sheets脚本加flush仍超最大执行时间，如何优化5万+Drive文件遍历

实现思路 #

• 把原有的递归遍历文件夹逻辑改造成队列式遍历，方便存储中断进度
• 每次处理完单个文件夹后检查运行时长，接近6分钟执行上限时自动存储当前进度、创建1分钟后触发的续跑触发器
• 全量遍历完成后自动清理存储的进度信息和临时触发器，避免残留

整合后的完整代码 #

// 配置常量，无需修改
const MAX_RUNNING_TIME = 4.5 * 60 * 1000; // 单次最长运行4.5分钟，留30秒余量避免触发上限
const REASONABLE_WAIT_TIME = 60 * 1000; // 超时后续跑等待1分钟
const TARGET_FOLDER_NAME = 'Beelden'; // 替换为你要遍历的文件夹名称

// 主入口函数，直接运行这个即可
function mainListDriveFiles() {
  const startTime = new Date().getTime();
  const scriptProps = PropertiesService.getScriptProperties();
  let progress;
  
  // 判断是首次运行还是超时后续跑
  const savedProgress = scriptProps.getProperty('scanProgress');
  if (!savedProgress) {
    // 首次运行初始化：清空表格、写入表头、初始化遍历队列
    const sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
    sheet.appendRow(["Folder","Name", "Last Updated", "Size MB", "URL"]);
    const rootFolder = DriveApp.getFoldersByName(TARGET_FOLDER_NAME).next();
    progress = {
      currentRow: 2,
      pendingFolders: [
        {
          id: rootFolder.getId(),
          path: rootFolder.getName()
        }
      ]
    };
  } else {
    // 续跑：读取之前存储的进度
    progress = JSON.parse(savedProgress);
  }

  // 遍历待处理文件夹队列
  while (progress.pendingFolders.length > 0) {
    // 检查是否接近超时
    const currTime = new Date().getTime();
    if (currTime - startTime >= MAX_RUNNING_TIME) {
      // 存储当前进度
      scriptProps.setProperty('scanProgress', JSON.stringify(progress));
      // 删除旧的同函数触发器避免重复触发
      deleteOldTriggers();
      // 创建新的续跑触发器
      ScriptApp.newTrigger('mainListDriveFiles')
        .timeBased()
        .at(new Date(currTime + REASONABLE_WAIT_TIME))
        .create();
      return;
    }

    // 取出当前要处理的文件夹
    const folderInfo = progress.pendingFolders.shift();
    const currentFolder = DriveApp.getFolderById(folderInfo.id);
    const currentPath = folderInfo.path;

    // 写入当前文件夹下的所有文件，更新当前行号
    progress.currentRow = listFilesInFolder(currentFolder, currentPath, progress.currentRow);

    // 把子文件夹加入待处理队列尾部
    const subFolders = currentFolder.getFolders();
    while (subFolders.hasNext()) {
      const subFolder = subFolders.next();
      progress.pendingFolders.push({
        id: subFolder.getId(),
        path: `${currentPath}/${subFolder.getName()}`
      });
    }
  }

  // 全部文件夹处理完成，清理进度和临时触发器
  scriptProps.deleteProperty('scanProgress');
  deleteOldTriggers();
}

// 辅助函数：删除主函数的所有旧触发器
function deleteOldTriggers() {
  const allTriggers = ScriptApp.getProjectTriggers();
  for (const trigger of allTriggers) {
    if (trigger.getHandlerFunction() === 'mainListDriveFiles') {
      ScriptApp.deleteTrigger(trigger);
    }
  }
}

// 辅助函数：写入单个文件夹下的所有文件，返回更新后的行号
function listFilesInFolder(folder, path, startRow) {
  const datos = [];
  const files = [];
  const fileIterator = folder.getFiles();
  const bulk = 500;
  const sheet = SpreadsheetApp.getActiveSheet();

  // 收集当前文件夹所有文件
  while (fileIterator.hasNext()) {
    files.push(fileIterator.next());
  }
  const totalFiles = files.length;

  // 按文件名升序排序
  files.sort((a, b) => {
    const aName = a.getName().toUpperCase();
    const bName = b.getName().toUpperCase();
    return aName.localeCompare(bName);
  });

  // 组装写入数据
  files.forEach(file => {
    const fname = file.getName();
    const fdate = file.getLastUpdated();
    const fsize = +(file.getSize()/1024/1024).toFixed(2);
    const furl = file.getUrl();
    datos.push([`${path} (${totalFiles})`, fname, fdate, fsize, furl]);
  });

  // 批量写入表格
  let currentRow = startRow;
  for (let i = 0; i < datos.length; i += bulk) {
    const chunk = datos.slice(i, i + bulk);
    sheet.getRange(currentRow, 1, chunk.length, 5).setValues(chunk);
    SpreadsheetApp.flush();
    currentRow += chunk.length;
  }

  return currentRow;
}

使用说明 #

• 替换代码中TARGET_FOLDER_NAME常量的值为你要遍历的Google Drive文件夹名称
• 首次运行直接执行mainListDriveFiles函数，按提示完成授权即可，触发超时会自动创建续跑触发器，无需人工干预
• 如需每日自动运行，可在Apps Script编辑器的「触发器」页面，给mainListDriveFiles函数添加每日定时触发规则即可

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