基于Google Apps Script调用外部工作表实现SUMIFS多条件统计

实现逻辑说明 #

• 示例选取「营销渠道」「活动状态」2个字段作为求和匹配条件，你可以根据实际需求修改条件字段和匹配值
• 自动读取源表「Raw Data」中符合条件的行，对最后5个指标字段分别求和
• 自动匹配目标表「Destination」的字段顺序，将求和结果写入对应字段下方的新行

完整代码 #

function multiConditionSumAndWrite() {
  // 请替换为你自己的表格ID，ID可从表格链接的/d/和/edit之间的字符串提取
  const SOURCE_SPREADSHEET_ID = "你的源表ID";
  const TARGET_SPREADSHEET_ID = "你的目标表ID";
  const SOURCE_SHEET_NAME = "Raw Data";
  const TARGET_SHEET_NAME = "Destination";

  // 示例2个求和条件，可根据实际需求修改字段名和匹配值
  const CONDITIONS = [
    { fieldName: "营销渠道", matchValue: "抖音" },
    { fieldName: "活动状态", matchValue: "已上线" }
  ];

  // 1. 读取源表全量数据
  const sourceSs = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID);
  const sourceSheet = sourceSs.getSheetByName(SOURCE_SHEET_NAME);
  const sourceData = sourceSheet.getDataRange().getValues();
  const sourceHeader = sourceData[0];

  // 2. 定位条件字段、最后5个指标字段的列索引
  const conditionColIndexes = CONDITIONS.map(cond => sourceHeader.indexOf(cond.fieldName));
  const metricColIndexes = [];
  const metricCount = 5;
  for (let i = sourceHeader.length - metricCount; i < sourceHeader.length; i++) {
    metricColIndexes.push(i);
  }

  // 3. 执行多条件求和计算
  const sumResult = new Array(metricCount).fill(0);
  // 跳过表头从第二行开始遍历
  for (let rowIndex = 1; rowIndex < sourceData.length; rowIndex++) {
    const row = sourceData[rowIndex];
    // 校验当前行是否匹配所有预设条件
    const isMatch = CONDITIONS.every((cond, idx) => {
      const colIdx = conditionColIndexes[idx];
      return row[colIdx] === cond.matchValue;
    });
    if (isMatch) {
      // 累加对应指标值，非数值格式默认按0计算
      metricColIndexes.forEach((colIdx, metricIdx) => {
        sumResult[metricIdx] += Number(row[colIdx]) || 0;
      });
    }
  }

  // 4. 按目标表字段顺序写入结果
  const targetSs = SpreadsheetApp.openById(TARGET_SPREADSHEET_ID);
  const targetSheet = targetSs.getSheetByName(TARGET_SHEET_NAME);
  const targetHeader = targetSheet.getDataRange().getValues()[0];
  // 初始化新行数据
  const targetRow = new Array(targetHeader.length).fill("");
  metricColIndexes.forEach((colIdx, metricIdx) => {
    const metricName = sourceHeader[colIdx];
    const targetColIdx = targetHeader.indexOf(metricName);
    if (targetColIdx > -1) {
      targetRow[targetColIdx] = sumResult[metricIdx];
    }
  });
  // 追加新行到目标表已有数据的下方
  targetSheet.appendRow(targetRow);
}

使用步骤 #

• 打开任意Google Sheet，点击顶部菜单「扩展程序」-「Apps Script」进入脚本编辑器
• 替换代码中的源表ID、目标表ID为实际值，根据你的业务需求修改CONDITIONS数组里的条件配置
• 点击保存按钮，首次运行需要按提示授权脚本访问表格的权限
• 运行完成后即可在目标表「Destination」中看到新增的求和结果行

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