按账户分组校验Count of deletions列数据有效性问题

需求说明 #

数据表中每个唯一Account ID仅在首次出现的行包含Count of deletions的有效条目，需要按账户分组，仅校验每个账户首行的该列数据：

• 判断值是否为空
• 判断值是否为非数字（除了"Ignored"视为无错误）
• 仅标记不符合要求的账户首行，而非所有Status为"Completed"的行

修正后的代码 #

function myFunction() {
  const SS = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SS.getSheetByName("Sheet1");
  const Validation_sheet = SS.getSheetByName("Sheet2");
  const last_row = sheet.getLastRow();
  
  // 获取所需列数据：Account ID(列2)、Status(列4)、Count of deletions(列5)
  const accountIds = sheet.getRange(2, 2, last_row - 1).getValues().flat();
  const statusValues = sheet.getRange(2, 4, last_row - 1).getValues().flat();
  const deletionCounts = sheet.getRange(2, 5, last_row - 1).getValues().flat();

  const result = validateAccountFirstRow(accountIds, statusValues, deletionCounts);
  const countSetValue = Validation_sheet.getRange("A2");
  const errors = [];

  for (const rowNum in result) {
    const errorType = result[rowNum];
    if (errorType === "empty" || errorType === "Not a number") {
      errors.push(rowNum);
    }
  }

  if (errors.length > 0) {
    countSetValue.setValue(`Row(s) ${errors.join(" and ")} have error`);
  } else {
    countSetValue.setValue("No errors");
  }
}

function validateAccountFirstRow(accountIds, statusValues, deletionCounts) {
  const processedAccounts = new Set();
  const result = {};

  for (let i = 0; i < accountIds.length; i++) {
    const accountId = accountIds[i];
    const rowNum = i + 2; // 对应表格的实际行号（从第2行开始）

    // 仅处理每个账户的首次出现行
    if (!processedAccounts.has(accountId)) {
      processedAccounts.add(accountId);
      
      // 仅校验Status为Completed的行
      if (statusValues[i] === "Completed") {
        const countValue = deletionCounts[i];
        
        if (countValue === "") {
          result[rowNum] = "empty";
        } else if (countValue === "Ignored") {
          result[rowNum] = "No error";
        } else if (!isNaN(countValue)) {
          result[rowNum] = "number";
        } else {
          result[rowNum] = "Not a number";
        }
      }
    }
  }

  return result;
}

代码修正关键点 #

• 跟踪已处理账户：使用Set存储已经处理过的Account ID，确保每个账户仅校验首次出现的行
• 整合列数据：一次性获取Account ID、Status和Count of deletions列的扁平化数据，简化遍历逻辑
• 精准校验范围：仅对每个账户的首行，且Status为"Completed"的行进行校验
• 保持原有校验规则：保留空值、数字、"Ignored"的判断逻辑，确保符合需求

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