Google Sheets数组型自定义公式如何单独返回对应错误值？

要实现输入数组中部分元素出错时，对应单元格显示错误、其余正常返回结果的需求，核心是对数组的每个元素单独做错误捕获与处理，不能让单个元素的错误导致整个公式失效。以下是具体实现方案：

核心思路 #

用Array.prototype.map()遍历输入数组的每一行和每个单元格，对每个元素单独执行计算逻辑，结合try...catch块捕获错误，出错时返回Google Sheets原生的错误对象（如#NUM!、#VALUE!），正常情况返回计算结果。

示例代码（以自定义平方根公式为例） #

假设你需要一个自定义公式CUSTOM_SQRT，输入负数时对应单元格显示#NUM!，非数字输入显示#VALUE!，正常数值返回平方根：

function CUSTOM_SQRT(inputArray) {
  // 兼容单单元格输入（转为二维数组格式）
  if (!Array.isArray(inputArray)) {
    inputArray = [[inputArray]];
  }

  // 遍历每一行，再遍历该行每个元素
  return inputArray.map(row => {
    return row.map(cell => {
      try {
        // 替换成你的实际计算逻辑
        if (typeof cell !== 'number') {
          throw new Error("非数字输入");
        }
        if (cell < 0) {
          throw new Error("负数无法开平方");
        }
        return Math.sqrt(cell);
      } catch (e) {
        // 根据错误类型返回对应Sheet错误
        const ss = SpreadsheetApp.getActiveSpreadsheet();
        if (e.message === "负数无法开平方") {
          return ss.createError(SpreadsheetApp.ErrorType.NUM);
        } else {
          return ss.createError(SpreadsheetApp.ErrorType.VALUE);
        }
      }
    });
  });
}

关键细节说明 #

1. 数组兼容处理：先判断输入是否为数组，把单单元格输入转为二维数组，确保公式同时支持单个单元格和数组范围输入。
2. 双层遍历：通过两层map分别处理行和单元格，保证每个元素的计算相互独立，单个元素出错不会影响其他元素。
3. 错误类型匹配：用createError()创建Google Sheets原生错误对象，这样出错单元格会显示标准的错误提示（如#NUM!），而不是自定义文本或空值。

你之前两种写法的问题分析 #

• 第一种整列报错：没有对单个元素做错误捕获，只要有一个元素计算出错，整个函数就抛出全局错误，导致所有单元格显示错误。
• 第二种无错误提示：捕获错误后没有返回Sheet原生错误对象，而是返回了空值或默认值，所以看起来没有错误提示。

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