Google App Script嵌套forEach循环实现区域日期维度测试结果汇总

需求背景 #

管理多组测试区域，不同点位在各区域内分多轮、多日期开展测试，测试不通过会安排其他日期重测。原有汇总脚本仅能按区域维度输出最终结果，未遍历日期列、未携带日期信息，需要调整实现按区域+测试日期维度的结果汇总，匹配预期输出格式。

原有脚本代码 #

function  Summary() {
  try {
    let sheet = SpreadsheetApp.openById(SpreadsheetID).getSheetByName(SheetName03);
    let sheet04 = SpreadsheetApp.openById(SpreadsheetID).getSheetByName(SheetName04);
    let values = sheet.getDataRange().getValues();
   
    values.shift(); // remove the headers
    
    // get unique sites
    let areas = values.map( row => row[0] );
    areas = [...new Set(areas)]
    
    // find results for each area
    let results = areas.map( area => [area,"Pass"]);
    
    values.forEach( row => {  let index = areas.findIndex( area => area === row[0] );
                              let tests = row.slice(2);

                              if( tests.indexOf("Fail") >= 0 ) {
                                results[index][1] = "Fail";
                                return;
                              }
                              else if( tests.indexOf( "" ) >= 0 ) {
                                results[index][1] = "Data Missing";
                                return;
                              }
                            }
                  );
  


  sheet04.getRange(2,1,results.length,results[0].length).setValues(results);

  }
  catch(err) {
    console.log(err);
  }
}

格式参考 #

• 原始数据格式：
• 现有脚本输出效果：
• 预期输出效果：

优化实现 #

不需要硬套多层循环做全量遍历，采用键值对聚合的方式实现效率更高，逻辑如下：

1. 提取表头行的所有测试日期，建立日期列索引映射
2. 以「区域+测试日期」为唯一键初始化所有待汇总条目
3. 逐行逐列遍历测试结果，按优先级判定汇总状态：同组下只要存在Fail即标记Fail，无Fail但存在空值标记Data Missing，全部通过标记Pass
4. 汇总结果按区域、测试日期排序后写入目标表，自动生成表头

优化后完整代码：

function Summary() {
  try {
    const sheet = SpreadsheetApp.openById(SpreadsheetID).getSheetByName(SheetName03);
    const sheet04 = SpreadsheetApp.openById(SpreadsheetID).getSheetByName(SheetName04);
    const allValues = sheet.getDataRange().getValues();
    
    // 提取表头日期信息
    const headers = allValues.shift();
    const dateColList = headers.slice(2).map((date, offset) => ({
      colIdx: offset + 2,
      date: date
    }));

    // 获取所有唯一区域
    const areaList = [...new Set(allValues.map(row => row[0]))];
    // 初始化聚合结果
    const resultMap = {};
    areaList.forEach(area => {
      dateColList.forEach(dateItem => {
        const mapKey = `${area}_${dateItem.date.getTime()}`;
        resultMap[mapKey] = {
          area: area,
          testDate: dateItem.date,
          result: "Pass"
        }
      })
    })

    // 遍历所有数据判定结果
    allValues.forEach(row => {
      const currentArea = row[0];
      dateColList.forEach(dateItem => {
        const mapKey = `${currentArea}_${dateItem.date.getTime()}`;
        const currentRes = row[dateItem.colIdx];
        const currentEntry = resultMap[mapKey];
        // 已判定为最高优先级Fail则跳过后续校验
        if (currentEntry.result === "Fail") return;
        if (currentRes === "Fail") {
          currentEntry.result = "Fail";
          return;
        }
        if (currentRes === "" && currentEntry.result !== "Fail") {
          currentEntry.result = "Data Missing";
        }
      })
    })

    // 转换为表格写入格式，按区域、日期排序
    const outputData = Object.values(resultMap)
      .sort((a, b) => {
        if (a.area === b.area) return a.testDate - b.testDate;
        return a.area.localeCompare(b.area);
      })
      .map(item => [item.area, item.testDate, item.result]);

    // 清空目标表旧数据，写入表头和结果
    sheet04.clearContents();
    sheet04.getRange(1, 1, 1, 3).setValues([["Area", "Test Date", "Result"]]);
    sheet04.getRange(2, 1, outputData.length, 3).setValues(outputData);

  } catch (err) {
    console.log(err);
  }
}

注意事项 #

• 状态判定优先级固定为Fail > Data Missing > Pass，避免低优先级状态覆盖高优先级结果
• 同区域的测试结果自动按日期升序排列，和预期输出结构对齐
• 写入前自动清空目标表历史数据，无需手动清理旧内容

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