将数据验证COUNTIF公式转为AppScript自定义IsUnique函数的问题

问题背景 #

原本通过数据验证公式=COUNTIF(col_Customers_Categories_Ids, B4)<=1（其中col_Customers_Categories_Ids指向开放范围Customers Categories'!B4:B）实现单元格唯一值约束，尝试转为AppScript自定义函数IsUnique(range, cell)后，输入值时触发错误提示Invalid The cell content violates its validation rules，无法正常输入。计划结合IsText函数通过=AND(IsUnique(range, cell), IsText(cell))实现多重验证，仅IsUnique逻辑存在问题。

原错误代码：

function IsUnique(range, cell) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rangeValues = sheet.getRange(range).getValues();
  var cellValue = sheet.getRange(cell).getValue();
  
  // Flatten the rangeValues array and filter out empty values
  var values = rangeValues.flat().filter(value => value !== '');

  // Count the occurrences of the cell value in the range
  var count = values.filter(value => value === cellValue).length;

  // If the count is greater than 1, it's not unique
  return count < 2;
}

错误原因 #

自定义函数接收的range参数是范围对应的值数组，cell参数是单元格的当前值，而非A1格式的引用字符串。原代码中用sheet.getRange(range)尝试解析数组为引用，会直接报错，导致函数返回错误结果，触发数据验证规则判定失败。

修复后的代码 #

function IsUnique(rangeValues, cellValue) {
  // 扁平化范围数组并过滤空单元格值
  const filteredValues = rangeValues.flat().filter(val => val !== '');
  // 统计当前单元格值在范围内的出现次数
  const occurrenceCount = filteredValues.filter(val => val === cellValue).length;
  // 允许出现1次（当前单元格本身），因此返回次数≤1
  return occurrenceCount <= 1;
}

使用方法 #

1. 数据验证中直接使用公式：=IsUnique(col_Customers_Categories_Ids, B4)
2. 多重验证组合公式：=AND(IsUnique(col_Customers_Categories_Ids, B4), IsText(B4))

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