Google Sheets多输入输出自定义函数内部错误：改用范围公式

问题根源 #

当表格行数增加时，自定义函数fifo频繁触发「Error Internal error while executing the custom function.」，本质是逐单元格调用函数导致的性能瓶颈——Google Sheets对自定义函数的单次调用资源、调用次数有隐性限制，大量分散调用会触发内部错误。按照《Optimizing Custom Functions》的建议，必须将函数改造为范围输入+范围输出模式，一次性处理批量数据，减少调用次数。

核心改造思路 #

1. 接收整个数据范围作为输入（而非单个单元格或零散范围），将Google Sheets传入的二维数组转换为一维数组处理
2. 批量遍历所有行数据，复用原FIFO核心逻辑完成计算
3. 将所有行的结果整理为二维数组返回，直接匹配输出范围的行列结构

改造后的代码框架 #

function fifo(datesRange, assetQtysRange, transVolumesRange, minHoldTime) {
  // 扁平化范围输入：Google Sheets传入的范围是二维数组（[[val1],[val2],...]），转为一维数组
  const flattenRange = range => range.map(row => row[0]);
  const dates = flattenRange(datesRange);
  const assetQtys = flattenRange(assetQtysRange);
  const transVolumes = flattenRange(transVolumesRange);
  // 处理minHoldTime：如果是范围输入则取第一个值，否则直接使用
  const holdTime = Array.isArray(minHoldTime) ? minHoldTime[0][0] : minHoldTime;

  // 参数校验逻辑（保留原有规则，适配批量输入）
  if (!transVolumes.length || !assetQtys.length || !dates.length) {
    throw new Error('交易数量、资产数量和日期必须至少包含一个元素！');
  }
  if (transVolumes.length !== assetQtys.length || transVolumes.length !== dates.length) {
    throw new Error('交易数量、资产数量和日期的元素数量必须一致！');
  }
  if (assetQtys[0] < 0) {
    throw new Error('第一笔交易必须是买入（BUY）！');
  }

  // 初始化FIFO全局状态（如果原逻辑需要累积持仓，比如FIFO队列，在此定义）
  const fifoQueue = [];
  const results = [];

  // 批量处理每一行数据
  for (let i = 0; i < dates.length; i++) {
    const currentDate = dates[i];
    const currentQty = assetQtys[i];
    const currentVolume = transVolumes[i];

    // --- 此处替换为原代码中"Something happening here"的核心FIFO逻辑 ---
    // 注意：如果原逻辑依赖全局持仓状态（比如fifoQueue），直接在此更新状态并计算结果
    const return_1 = /* 当前行计算结果1 */;
    const return_2 = /* 当前行计算结果2 */;
    const return_3 = /* 当前行计算结果3 */;
    const return_4 = /* 当前行计算结果4 */;
    const return_5 = /* 当前行计算结果5 */;
    const return_6 = /* 当前行计算结果6 */;
    // --- 核心逻辑结束 ---

    // 将当前行结果加入结果数组，保持二维数组格式（适配Sheets范围输出）
    results.push([return_1, return_2, return_3, return_4, return_5, return_6]);
  }

  // 返回二维数组，直接输出到选中的范围
  return results;
}

使用方法 #

1. 在Sheet中选中需要输出结果的连续范围（比如要输出6列结果，选中B2:G100）
2. 输入公式（根据实际列调整范围参数）：

   =fifo(A2:A100, C2:C100, D2:D100, $E$2)
   

   （如果minHoldTime是全局固定值，使用绝对引用；如果是每行不同，传入对应范围）
3. 按下Enter键，新版Google Sheets会自动识别范围公式并填充结果

关键注意事项 #

• 禁止在循环中调用SpreadsheetApp等服务类方法，所有数据必须通过函数参数传入，避免额外性能开销
• 如果原FIFO逻辑需要维护累积持仓状态（比如逐笔买入的队列），必须在函数内部维护全局状态变量（如示例中的fifoQueue），确保计算的连续性
• 若处理超大规模数据（数千行），可通过CacheService缓存中间状态，进一步优化性能

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