Google Sheets自定义函数初始正常，一段时间后返回#NUM错误求助

云原生阿岚

2026-7-12

问题原因及解决方法

核心问题：`getActiveSheet()`的不确定性

你的WaterCost函数里用了SpreadsheetApp.getActiveSheet()来获取工作表，这是导致后续报错的关键：

自定义函数运行时，活动工作表是当前用户正在查看的表，而非存放costWaterM3的设置表。当你切换到其他工作表后，函数重新计算时，getActiveSheet()会指向当前显示的表，这张表没有costWaterM3命名区域，getValue()会返回非数值（比如#REF!或空值），和avgUsage相乘就触发#NUM错误。
同一脚本里的另一个函数正常，大概率是它没有依赖getActiveSheet()，而是直接指定了工作表或不涉及跨表读取。

两种修复方案

方案1：直接指定目标工作表

把getActiveSheet()改成明确指向存放命名区域的工作表（替换成你的设置表实际名称）：

/**
 * Calculates the total water cost
 *
 * @param {number} m3 water usage per year (in m3) 
 * @return total cost
 * @customfunction
 */
function WaterCost(avgUsage) {
  // 替换为你的设置表名称
  var settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('设置表');
  var costWaterM3 = settingsSheet.getRange('costWaterM3').getValue();
  return avgUsage * costWaterM3;
}

方案2：将`costWaterM3`作为参数传入

更符合自定义函数设计规范，避免依赖外部单元格读取，稳定性更高：

/**
 * Calculates the total water cost
 *
 * @param {number} avgUsage water usage per year (in m3)
 * @param {number} costPerM3 cost per cubic meter of water
 * @return total cost
 * @customfunction
 */
function WaterCost(avgUsage, costPerM3) {
  return avgUsage * costPerM3;
}

调用时改为：=WaterCost(waterUsageCoffeeShop, costWaterM3)