求Google Sheets公式/脚本：返回行内含validation Error的单元格引用

方法一：使用内置公式（无需脚本） #

在需要显示结果的单元格（比如A1）中输入以下公式，下拉即可应用到整列：

=LET(
  currentRow, ROW(),
  checkRange, INDIRECT("B"&currentRow&":ZZ"&currentRow),
  errorCells, FILTER(ADDRESS(currentRow, COLUMN(checkRange), 4), NOT(ISVALID(checkRange))),
  IF(COUNTA(errorCells)=0, "", "Validation Error in Cell(s) "&TEXTJOIN(", ", TRUE, errorCells))
)

公式说明： #

• LET 定义变量简化逻辑，避免重复计算
• currentRow 获取当前单元格所在行号
• checkRange 指定要检查的列范围（示例从B列到ZZ列，可根据实际调整）
• ISVALID 函数判断单元格值是否符合数据验证规则，NOT(ISVALID(...))筛选出验证错误的单元格
• ADDRESS 生成错误单元格的引用文本，TEXTJOIN 将多个单元格引用拼接成字符串

方法二：自定义Apps脚本（高效处理大量列） #

如果列数极多或公式运行卡顿，可使用自定义脚本实现：

1. 打开Google Sheets，点击「扩展」→「Apps脚本」
2. 替换默认代码为以下内容：

function GET_VALIDATION_ERRORS(rowNum) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 定义检查范围：从B列开始，当前行，覆盖到工作表最后一列
  const range = sheet.getRange(rowNum, 2, 1, sheet.getLastColumn() - 1);
  const cellValues = range.getValues()[0];
  const validations = range.getDataValidations()[0];
  const errorCellRefs = [];

  for (let i = 0; i < cellValues.length; i++) {
    const validationRule = validations[i];
    const cellValue = cellValues[i];
    // 仅检查有验证规则且值不符合规则的单元格
    if (validationRule && !validationRule.isValid(cellValue)) {
      // 转换列索引为字母（B列对应索引1，ASCII码66）
      const colLetter = String.fromCharCode(65 + 1 + i);
      errorCellRefs.push(`${colLetter}${rowNum}`);
    }
  }

  return errorCellRefs.length === 0 
    ? "" 
    : `Validation Error in Cell(s) ${errorCellRefs.join(", ")}`;
}

3. 保存脚本，返回工作表，在A1单元格输入=GET_VALIDATION_ERRORS(ROW())，下拉应用到其他行。

脚本优势： #

• 自动适配工作表的最后一列，无需手动调整范围
• 运行效率优于复杂数组公式，适合大量列的场景

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