利用Google Apps Script实现Google Drive新增文件自动同步至Google Sheets

背景 #

我们正在为社区搭建图书库，网站从Google Sheets获取数据展示，图书文件存储在Google Drive中。目前已实现通过Google Apps Script将Drive中所有文件信息写入Sheets，但现有脚本存在明显问题。

现有脚本代码 #

function onOpen() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('List Files/Folders')
    .addItem('List All Files and Folders', 'getListFilesandFolders')
    .addToUi();
};

function getListFilesandFolders(){
  var folderId = Browser.inputBox('Enter folder ID', Browser.Buttons.OK_CANCEL);
  if (folderId === "") {
    Browser.msgBox('Folder ID is invalid');
    return;
  }
  makeListFilesAndFolders(folderId, true); 
};


function makeListFilesAndFolders(folderId, listAll) {
  const sh = SpreadsheetApp.getActiveSheet();
  const range = sh.getRange('A2:H')
  range.clear();
  sh.appendRow(["parent","folder", "name", "date created", "URL","MimeType"]);
  try {
    var parentFolder =DriveApp.getFolderById(folderId);
    listFiles(parentFolder,parentFolder.getName())
    listSubFolders(parentFolder,parentFolder.getName());
  } catch (e) {
    Logger.log(e.toString());
  }
};

function listSubFolders(parentFolder,parent) {
  var childFolders = parentFolder.getFolders();
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    Logger.log("Fold : " + childFolder.getName());
    listFiles(childFolder,parent)
    listSubFolders(childFolder,parent + "|" + childFolder.getName());
  }
};

function listFiles(fold,parent){
  var sh = SpreadsheetApp.getActiveSheet();
  var data = [];
  var files = fold.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    data = [ 
      parent,
      fold.getName(),
      file.getName(),
      file.getDateCreated(),
      //file.getLastUpdated(),
      file.getUrl(),       
      //file.getId(),
      //file.getSize(),
      //file.getDescription(),     
      file.getMimeType()
      ];
    sh.appendRow(data);
  }
}

当前问题 #

• 需手动运行脚本，无法自动同步文件数据
• 每次运行会从头遍历所有文件，当文件数达到800个左右时，会因超出时间限制强制停止

需求 #

需要一个可每日定时（如21:00）运行的Google Apps Script，仅将Drive中新增的文件信息追加至Sheets末尾，无需从头遍历所有文件。

核心思路 #

• 用PropertiesService记录脚本上次运行时间，只处理上次运行后新增的文件
• 设置每日定时触发器，自动执行同步任务
• 批量写入数据到表格，减少API调用次数，避免超时问题

完整代码 #

// 打开表格时添加自定义菜单（用于手动测试）
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('图书库更新')
    .addItem('手动同步新增文件', 'syncNewFiles')
    .addToUi();
}

// 主函数：同步新增文件到表格
function syncNewFiles() {
  const props = PropertiesService.getScriptProperties();
  const lastRunTime = props.getProperty('lastRunTime');
  const currentTime = new Date();
  
  // 首次运行默认取当前时间前1分钟，避免遗漏刚添加的文件
  const startTime = lastRunTime ? new Date(lastRunTime) : new Date(currentTime.getTime() - 60000);
  
  // 替换为你的Drive图书库根文件夹ID
  const rootFolderId = '你的文件夹ID';
  const sheet = SpreadsheetApp.getActiveSheet();
  
  // 收集新增文件数据
  const newFilesData = [];
  
  // 递归遍历文件夹，筛选新增文件
  function traverseFolder(folder, parentPath) {
    // 只获取上次运行后创建的文件
    const files = folder.getFilesByType(MimeType.ALL)
      .filter(file => file.getDateCreated() > startTime);
    
    while (files.hasNext()) {
      const file = files.next();
      newFilesData.push([
        parentPath,
        folder.getName(),
        file.getName(),
        file.getDateCreated(),
        file.getUrl(),
        file.getMimeType()
      ]);
    }
    
    // 递归处理子文件夹
    const subFolders = folder.getFolders();
    while (subFolders.hasNext()) {
      const subFolder = subFolders.next();
      traverseFolder(subFolder, `${parentPath}|${subFolder.getName()}`);
    }
  }
  
  try {
    const rootFolder = DriveApp.getFolderById(rootFolderId);
    traverseFolder(rootFolder, rootFolder.getName());
    
    // 批量写入新增数据
    if (newFilesData.length > 0) {
      // 检查并设置表头（首次运行自动添加）
      const header = ["上级路径","文件夹", "文件名", "创建时间", "文件链接","文件类型"];
      const existingHeader = sheet.getRange(1, 1, 1, 6).getValues()[0];
      if (!existingHeader.every((val, idx) => val === header[idx])) {
        sheet.clearContents();
        sheet.appendRow(header);
      }
      // 批量追加数据
      sheet.getRange(sheet.getLastRow() + 1, 1, newFilesData.length, 6).setValues(newFilesData);
    }
    
    // 更新上次运行时间
    props.setProperty('lastRunTime', currentTime.toISOString());
    SpreadsheetApp.getUi().alert(`同步完成，新增${newFilesData.length}条文件记录`);
  } catch (e) {
    Logger.log(`同步出错：${e.toString()}`);
    SpreadsheetApp.getUi().alert(`同步失败：${e.message}`);
  }
}

// 设置每日定时触发器（只需运行一次）
function setupDailyTrigger() {
  // 清除已有的同名触发器，避免重复
  const existingTriggers = ScriptApp.getProjectTriggers();
  for (const trigger of existingTriggers) {
    if (trigger.getHandlerFunction() === 'syncNewFiles') {
      ScriptApp.deleteTrigger(trigger);
    }
  }
  
  // 创建每日21:00运行的触发器（时区设为北京时间）
  ScriptApp.newTrigger('syncNewFiles')
    .timeBased()
    .everyDays(1)
    .atHour(21)
    .inTimezone('Asia/Shanghai')
    .create();
  
  SpreadsheetApp.getUi().alert('每日定时触发器已设置完成，将在每天21:00自动同步新增文件');
}

使用步骤 #

1. 替换文件夹ID：把代码中的'你的文件夹ID'换成你的Google Drive图书库根文件夹ID
2. 设置定时触发器：运行一次setupDailyTrigger函数，完成每日21:00自动同步的配置
3. 手动测试：点击表格顶部的「图书库更新」→「手动同步新增文件」，验证功能是否正常
4. 调整时区/时间：如果需要修改定时时间或时区，修改atHour(21)和inTimezone('Asia/Shanghai')即可

优化亮点 #

• 避免重复遍历：只处理上次运行后新增的文件，大幅缩短运行时间
• 批量写入：用setValues替代多次appendRow，减少API调用，避免超时
• 自动表头管理：首次运行自动添加规范表头，确保数据格式统一
• 错误提示：同步出错时弹出提示并记录日志，方便排查问题

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