如何批量提取文件夹内所有Google Sheet指定单元格并聚合数据

实现逻辑 #

基于Google Apps Script直接调用Drive和Sheets官方接口实现，不需要手动下载文件、不需要第三方工具，所有逻辑运行在个人谷歌账号环境下，数据不会外流。

前置操作 #

• 打开任意一份你有编辑权限的Google Sheet，点击顶部「扩展程序」→「Apps 脚本」，打开脚本编辑器
• 把下方代码粘贴到编辑器中，替换对应配置参数后，按提示完成账号授权即可运行
• 注意：首次运行脚本会弹出权限授权提示，按引导允许脚本访问Google Drive、Sheets权限即可，脚本不会上传任何数据到第三方

核心代码 #

配置项（先修改这部分参数再运行） #

// 目标文件夹ID，从文件夹URL中folders/后的字符串获取
const TARGET_FOLDER_ID = '你的文件夹ID填在这里';
// 需要提取的目标单元格位置，示例为C3
const TARGET_CELL = 'C3';
// 目标工作表名称，留空则默认读取每个文件的第一个工作表
const SHEET_NAME = '';

批量提取单元格+统计总和 #

function batchSumTargetCell() {
  const folder = DriveApp.getFolderById(TARGET_FOLDER_ID);
  const allSheetFiles = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  let sumResult = 0;
  const valueDetail = [];

  while (allSheetFiles.hasNext()) {
    const currentFile = allSheetFiles.next();
    const currentSs = SpreadsheetApp.openById(currentFile.getId());
    const currentSheet = SHEET_NAME ? currentSs.getSheetByName(SHEET_NAME) : currentSs.getSheets()[0];
    const cellValue = currentSheet.getRange(TARGET_CELL).getValue();
    // 自动跳过非数值内容
    if (typeof cellValue === 'number' && !isNaN(cellValue)) {
      sumResult += cellValue;
      valueDetail.push([currentFile.getName(), currentFile.getId(), cellValue]);
    }
  }

  // 结果写入当前表格的新工作表，方便查看
  const outputSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('批量统计结果');
  outputSheet.getRange(1,1,1,3).setValues([['文件名','文件ID', `${TARGET_CELL}单元格值`]]);
  outputSheet.getRange(2,1,valueDetail.length,3).setValues(valueDetail);
  outputSheet.getRange(valueDetail.length + 2, 1).setValue(`数值总和：${sumResult}`);
  Logger.log(`统计完成，${TARGET_CELL}单元格总和为${sumResult}`);
  return {sumResult, valueDetail};
}

批量标记最大值、最小值 #

function markMaxAndMin() {
  const statResult = batchSumTargetCell();
  const allValues = statResult.valueDetail.map(row => row[2]);
  const maxValue = Math.max(...allValues);
  const minValue = Math.min(...allValues);

  statResult.valueDetail.forEach(row => {
    const [, fileId, val] = row;
    const currentSs = SpreadsheetApp.openById(fileId);
    const currentSheet = SHEET_NAME ? currentSs.getSheetByName(SHEET_NAME) : currentSs.getSheets()[0];
    const targetRange = currentSheet.getRange(TARGET_CELL);
    // 最大值标浅红背景，最小值标浅绿背景，存在并列最值时会同时标记
    if (val === maxValue) targetRange.setBackground('#ffcccc');
    if (val === minValue) targetRange.setBackground('#ccffcc');
  })
  Logger.log(`标记完成，最大值：${maxValue}，最小值：${minValue}`);
}

使用说明 #

• 配置项修改完成后，在脚本编辑器的函数选择下拉框选中batchSumTargetCell，点击运行按钮，等待执行完成后回到打开的Google Sheet，就能看到自动生成的「批量统计结果」表，包含所有文件的目标单元格值和总和
• 需要标记最值时，选中markMaxAndMin函数运行即可，脚本会自动打开对应表格给目标单元格上色
• 如果文件夹内文件数量超过300份，触发脚本运行时长限制，可以把文件夹拆成多个子文件夹分批处理
• 这套方案没有绑定固定业务场景，只要修改配置项里的文件夹ID、单元格位置、工作表名称，就能适配所有需要批量聚合同结构Google Sheet数据的需求，比如汇总多部门上报的报表、统计批量生成的报价单数值、核对多份调研表单结果都可以直接用

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