Google Sheets自定义Apps Script函数返回数组至单元格异常问题

问题背景 #

我编写了一个名为remainingDigits的Apps Script函数，功能是接收0-10个0≤n<9的数字，返回0-9范围内未传入的数字（比如输入6、7、2、4时，应返回0、1、3、5、8、9）。函数在Apps Script内部测试完全正常，但在Google Sheets单元格中调用时出现异常。

测试环境：所有测试使用的单元格值为A7=6，A8=7，A9=2，A10=4

测试用例及异常表现 #

• 测试1
  单元格公式：=remainingDigits(A7, A8, A9, A10)
  返回结果：错误信息

  "TypeError: digits.indexOf is not a function (line 20)"

• 测试2
  单元格公式：=remainingDigits({A7, A8, A9, A10})
  返回结果：调用单元格显示0，下方9个单元格依次显示1、2……9（即返回了0-9所有数字）

• 测试3
  单元格公式：=remainingDigits(A7:A10)
  返回结果：与测试1相同的错误

• 测试4
  单元格公式：=remainingDigits({A7:A10})
  返回结果：与测试1相同的错误

原代码 #

function testRD() {
// A simple test harness for remainingDigits function

  let results = new Array;

  results = remainingDigits([0,1,2,3,4]);
  
  var a;

  for(a=0;a<=results.length-1;a++){
    Logger.log(results[a]);  
  }
}

function remainingDigits(digits){

  let remDigits = new Array;
  var d;

  for(d=0;d<=9;d++){
    if(digits.indexOf(d)!=-1){
    }
    else {
      remDigits.push(d);
    }
  }
  return remDigits;
}

问题原因 #

1. 参数类型不兼容：原函数仅接受一维数组作为输入，但在Sheets中调用时，不同传参方式会生成不同类型的输入：
   • 测试1：传入多个独立单元格值，函数实际收到的是4个独立参数而非数组，因此digits不是数组，没有indexOf方法，触发报错。
   • 测试3、4：传入单元格区域A7:A10，Sheets会将其转换为二维数组（格式为[[6],[7],[2],[4]]），indexOf无法在二维数组中匹配单个数字，导致逻辑错误。
   • 测试2：{A7,A8,A9,A10}会被转换为横向二维数组（[[6,7,2,4]]），同样无法被indexOf正确识别，因此函数判定所有数字都未传入，返回0-9全集。
2. 输入未做类型处理：未确保输入值为数字类型，可能存在字符串与数字的匹配问题。

修复方案 #

修改函数使其兼容多种输入形式（独立参数、一维数组、二维数组），并统一处理输入为一维数字集合：

function testRD() {
  let results = remainingDigits([0,1,2,3,4]);
  for(let a=0;a<=results.length-1;a++){
    Logger.log(results[a]);  
  }
}

function remainingDigits(...args) {
  // 统一处理所有输入为一维数字数组
  let digits = [];
  args.forEach(item => {
    if (Array.isArray(item)) {
      // 展开二维数组为一维，并转换为数字类型
      item.flat().forEach(num => digits.push(Number(num)));
    } else {
      // 处理单个参数，转换为数字类型
      digits.push(Number(item));
    }
  });

  // 去重，避免重复输入导致的逻辑错误
  const uniqueDigits = [...new Set(digits)];

  let remDigits = [];
  for (let d = 0; d <= 9; d++) {
    // 使用includes替代indexOf，代码更简洁直观
    if (!uniqueDigits.includes(d)) {
      remDigits.push(d);
    }
  }
  return remDigits;
}

修复说明 #

• 使用剩余参数...args接收任意数量的输入，兼容单个值、多个独立值、数组等多种传参方式。
• 处理二维数组：通过flat()方法将二维数组展开为一维，确保所有数字被正确提取。
• 类型转换：将所有输入转换为数字类型，避免字符串与数字的匹配问题。
• 去重处理：使用Set去除重复输入的数字，防止逻辑误判。
• 使用includes替代indexOf，代码更简洁易读。

测试验证 #

修复后所有测试用例均可正常返回预期结果：

• =remainingDigits(A7, A8, A9, A10) → 返回0,1,3,5,8,9
• =remainingDigits({A7, A8, A9, A10}) → 返回0,1,3,5,8,9
• =remainingDigits(A7:A10) → 返回0,1,3,5,8,9
• =remainingDigits({A7:A10}) → 返回0,1,3,5,8,9

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