如何通过Google Apps Script自动导入本地指定xls至Google Sheets并替换数据

Google Apps Script（GAS）无法直接访问本地文件系统，因此需要先将本地C:\Reports\文件夹同步到Google Drive，再通过GAS处理云端文件。以下是完整实现步骤：

1. 本地文件夹同步到Google Drive #

• 安装Google Drive桌面客户端，将C:\Reports\设置为同步文件夹，确保本地生成的report*.xls文件会自动上传到云端Drive的对应目录。

2. 获取目标Drive文件夹ID #

• 打开Google Drive，找到同步后的Reports文件夹，查看浏览器地址栏，末尾的一串字符就是文件夹ID（格式类似1AbCDefGhIjKlMnOpQrStUvWxYz），记下来备用。

3. 导入替换数据的GAS脚本 #

打开目标Google Sheet，点击扩展程序 > Apps Script，清空默认代码，粘贴以下脚本：

function importReportToDetails() {
  // 替换为你的Drive文件夹ID
  const folderId = "1AbCDefGhIjKlMnOpQrStUvWxYz";
  const sheetName = "Details";
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  if (!targetSheet) {
    throw new Error(`未找到名为${sheetName}的工作表`);
  }

  // 获取Drive文件夹中所有以report开头的xls文件
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFiles();
  let latestFile = null;
  let latestDate = new Date(0);

  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    // 匹配以report开头且后缀为xls的文件
    if (fileName.startsWith("report") && fileName.endsWith(".xls")) {
      const fileDate = file.getLastUpdated();
      // 筛选最新的文件（避免处理旧文件）
      if (fileDate > latestDate) {
        latestDate = fileDate;
        latestFile = file;
      }
    }
  }

  if (!latestFile) {
    throw new Error("未找到符合条件的report*.xls文件");
  }

  // 清空Details工作表现有数据（若需保留表头，可调整为从第2行开始清空）
  targetSheet.clearContents();

  // 将xls文件导入到工作表
  const xlsBlob = latestFile.getBlob();
  const importedSpreadsheet = SpreadsheetApp.open(xlsBlob);
  const sourceSheet = importedSpreadsheet.getSheets()[0]; // 取xls的第一个工作表
  const sourceData = sourceSheet.getDataRange().getValues();

  // 将数据写入Details工作表
  targetSheet.getRange(1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);

  // 可选：删除已处理的xls文件，避免重复处理
  // latestFile.setTrashed(true);
}

4. 设置每周自动执行触发器 #

• 在GAS编辑器中，点击左侧的触发器图标（时钟样式）。
• 点击添加触发器，配置：
  • 选择函数：importReportToDetails
  • 选择事件源：时间驱动
  • 选择时间类型：周计时器
  • 设置每周执行的具体时间，保存即可。

关键问题解答 #

• 关于report*匹配：脚本中通过fileName.startsWith("report") && fileName.endsWith(".xls")实现了类似通配符的匹配逻辑，能筛选所有以report开头的xls文件。
• 权限说明：首次运行脚本时，需要授权GAS访问你的Google Drive和Spreadsheet，按照提示完成授权即可。

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