如何用Google Scripts模拟COUNTIF功能并标记表格D列状态？

需求说明 #

• 原公式意图：通过COUNTIFS标记首次出现的邮箱为"Request1"，后续重复出现的为"Request2"
• 理想效果：
  • 当前行邮箱地址与下一行不同 → 标记为「Parent」
  • 当前行邮箱地址与下一行相同 → 标记为「Child」

现有代码的问题 #

1. 循环跳过逻辑错误：当EMAIL && USERNAME && ITEM && STATUS == ""时直接跳过，导致本该标记的空状态行被忽略
2. 最后一行未处理：无下一行的最后一行未设置标记（按需求应标记为「Parent」）
3. 写入方式错误：setValues要求传入二维数组，但代码中传入单个值STATUS，且写入位置指向了数据范围之外的行
4. 结果未收集：循环中未将处理后的状态存入数组，无法批量更新列

修正后的Google Scripts代码 #

function checkStatus() {
  const ss = SpreadsheetApp.openById('XXX');
  const sheet = ss.getSheetByName('Sheet1');
  const startRow = 2;
  const lastRow = sheet.getLastRow();
  const dataRange = sheet.getRange(startRow, 1, lastRow - startRow + 1, 4); // 仅读取A-D列数据
  const dataVal = dataRange.getValues();
  const result = [];

  for (let i = 0; i < dataVal.length; i++) {
    const currentEmail = dataVal[i][0];
    let status = dataVal[i][3];
    
    // 已存在状态的行直接保留原内容
    if (status !== "") {
      result.push([status]);
      continue;
    }

    // 判断是否存在下一行并获取下一行邮箱
    const hasNextRow = i < dataVal.length - 1;
    const nextEmail = hasNextRow ? dataVal[i+1][0] : "";

    // 设置状态值
    if (hasNextRow && currentEmail === nextEmail) {
      status = "Child";
    } else {
      status = "Parent";
    }
    result.push([status]);
  }

  // 批量写入D列
  sheet.getRange(startRow, 4, result.length, 1).setValues(result);
}

更优方案：用数组公式替代脚本 #

如果不需要复杂的前置判断，直接用数组公式更高效，无需手动运行脚本：

={"Status";ARRAYFORMULA(IF(LEN(A2:A),IF(A2:A=OFFSET(A2:A,1,0),"Child","Parent"),""))}

• 原理：OFFSET(A2:A,1,0)获取当前行的下一行邮箱地址，与当前行邮箱对比后设置标记；最后一行因无下一行，会自动标记为「Parent」，完全符合需求

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