Google Script用作表格公式报错无权限调用setActiveSheet 如何适配？

Google Sheets 自定义公式（直接在单元格输入=函数名()调用的公式）有严格的权限和能力限制：

• 禁止调用setActiveSheet、setValue这类需要用户授权、可修改表格其他区域的接口，这是触发权限报错的核心原因
• 自定义公式仅支持返回计算结果到公式所在的单元格，不能主动修改其他单元格内容，你原有代码的逻辑是主动读取最后一行、给对应单元格赋值，不符合自定义公式的使用规则

将函数改造为接收参数的形式，使用时传入当前行的街道、门牌号即可，同时修正原代码只匹配街道、可能出现同街道不同门牌号误匹配的问题：

function CHECK_ADDRESS(currentStreet, currentAddress) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Feuil1');
  const allData = sheet.getDataRange().getValues();
  // 遍历匹配地址
  for(let i = 0; i < allData.length; i++){
    // 同时匹配街道+门牌号，且过滤掉Nb units列的无效值
    if(allData[i][0] === currentStreet && allData[i][1] === currentAddress && typeof allData[i][2] === 'number'){
      return allData[i][2];
    }
  }
  // 无匹配时返回空值，也可自定义返回提示文本
  return "";
}

使用方法 #

在需要计算Nb units的单元格（比如第二行的C列单元格C2）输入公式=CHECK_ADDRESS(A2,B2)，下拉填充到所有需要计算的行即可正常使用。
如果需要整列自动计算不需要手动填充，可在C1单元格输入数组公式：
=ARRAYFORMULA(IF(ROW(A:A)=1,"Nb units",IF(A:A="","",CHECK_ADDRESS(A:A,B:B))))

替代方案 #

如果需要保持原有「新增行自动写入结果」的逻辑，不要将该函数作为自定义公式使用，可给函数绑定onEdit或onChange触发器，触发器场景下可正常调用setValue等修改接口，不会有权限报错。

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