如何通过BYROW/BYCOL检测行内后续活动，计算运行中P活动的时长？

问题背景 #

表格结构如下（多组活动代码+开始时间列）：

• 输入Ac.时，onEdit事件自动填充相邻的Start Time。
• 需求：当Ac.为P，且该行后续无其他活动（该P处于运行状态）时，计算从Start Time到当前时间的时长并写入对应单元格。
• 尝试用BYROW/BYCOL但无法判断后续单元格是否有内容，寻求解决方法。

函数实现方案 #

单单元格判断公式 #

针对某个P的Start Time单元格（比如第2行第6列，对应Ac.为P），在其右侧单元格输入：

=IF(AND(E2="P", COUNTA(G2)=0), NOW()-F2, "")

• COUNTA(G2)：检查当前Ac.单元格（E2）右侧所有后续Ac.单元格是否为空
• 满足条件时，NOW()-F2计算时长（可设置单元格格式为HH:mm:ss或自定义时长格式）

批量处理（BYROW+MAP） #

要批量遍历所有行的活动列，自动判断并计算时长，可使用以下数组公式：

=BYROW(A2:H, LAMBDA(row, 
  MAP(
    CHOOSECOLS(row,1,3,5,7), CHOOSECOLS(row,2,4,6,8),
    LAMBDA(acs, sts,
      REDUCE("", SEQUENCE(4), LAMBDA(res, idx,
        IF(
          AND(INDEX(acs, idx)="P", COUNTA(INDEX(acs, idx+1, 1):INDEX(acs, 4, 1))=0),
          NOW()-INDEX(sts, idx),
          res
        )
      ))
    )
  )
))

• CHOOSECOLS分离出所有Ac.列和Start Time列
• REDUCE遍历每组活动，判断当前P是否为该行最后一个活动，满足条件则返回时长

脚本增强方案（结合onEdit） #

如果需要自动将时长写入单元格（无需手动输入公式），修改现有onEdit脚本：

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const row = range.getRow();
  const col = range.getColumn();

  // 仅处理Ac.列（奇数列，跳过表头）
  if (col % 2 !== 1 || row === 1) return;

  // 自动写入Start Time
  const acVal = range.getValue();
  const startTimeCell = sheet.getRange(row, col + 1);
  acVal ? startTimeCell.setValue(new Date()) : startTimeCell.clearContent();

  // 查找该行最后一个非空的Ac.单元格
  const acCols = [1, 3, 5, 7];
  let lastAcCol = null;
  for (let i = acCols.length - 1; i >= 0; i--) {
    const cell = sheet.getRange(row, acCols[i]);
    if (cell.getValue()) {
      lastAcCol = acCols[i];
      break;
    }
  }

  // 最后一个活动是P时计算时长
  if (lastAcCol && sheet.getRange(row, lastAcCol).getValue() === "P") {
    const startTime = startTimeCell.getValue();
    if (startTime) {
      const durationMs = new Date() - startTime;
      // 格式化时长为HH:mm:ss
      const duration = Utilities.formatDate(
        new Date(durationMs),
        Session.getScriptTimeZone(),
        "HH:mm:ss"
      );
      // 写入时长到Start Time右侧单元格（可根据需求调整位置）
      sheet.getRange(row, lastAcCol + 2).setValue(duration);
    }
  }
}

• 编辑Ac.列时自动填充开始时间
• 自动检查该行最后一个活动是否为P，若是则计算并写入时长

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