如何使用Apps Script按小时对表格指定列实现求和及乘积汇总

实现逻辑 #

• 读取表格中D列的时间数据、F列的数值数据
• 提取每个时间的小时作为分组标识
• 遍历所有行，对同一小时对应的F列值分别累加、累乘
• 将分组统计结果输出到表格指定区域

完整代码 #

function aggregateByHour() {
  // 获取当前激活的表格
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const statisticMap = {};

  // 跳过表头行从第二行开始遍历，无表头可将i的初始值改为0
  for (let i = 1; i < values.length; i++) {
    const timeValue = values[i][3]; // D列对应索引为3
    const fValue = Number(values[i][5]); // F列对应索引为5
    // 过滤无效数据
    if (!timeValue || isNaN(fValue)) continue;
    // 提取时间的小时数
    const hour = timeValue.getHours();
    // 初始化该小时的统计值
    if (!statisticMap[hour]) {
      statisticMap[hour] = { sum: 0, product: 1 };
    }
    // 累加求和、累乘求积
    statisticMap[hour].sum += fValue;
    statisticMap[hour].product *= fValue;
  }

  // 构造输出内容，默认表头为小时、求和结果、乘积结果
  const outputArr = [["小时", "F列求和", "F列乘积"]];
  // 按小时升序排列结果
  Object.keys(statisticMap).sort((a, b) => a - b).forEach(hour => {
    outputArr.push([`${hour}时`, statisticMap[hour].sum, statisticMap[hour].product]);
  });
  // 输出到H列开始的区域，可修改getRange的第二个参数调整输出起始列
  sheet.getRange(1, 8, outputArr.length, outputArr[0].length).setValues(outputArr);
}

使用步骤 #

• 打开需要处理的谷歌表格，点击顶部菜单栏「扩展程序」-「Apps Script」进入脚本编辑器
• 清空编辑器内默认的示例代码，将上述代码粘贴后点击保存按钮，自定义项目名称
• 首次运行需要完成账户授权，按页面提示完成授权操作后执行函数
• 回到表格即可在H列及后续列看到按小时统计的结果

调整说明 #

• 如果不需要输出表头，可以删除outputArr初始化时的表头行
• 要修改输出位置时，调整sheet.getRange(1, 8)中的第二个参数即可，比如要从M列输出就把8改成13
• 若需要将汇总结果填充到原数据对应小时的行，可自行修改输出逻辑适配需求

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