在Google表格调用AppScript库自定义函数遇权限错误求助

问题概述 #

希望在Google表格B中，通过导入脚本项目A作为库调用自定义函数getFirstFruit()，但表格单元格返回#ERROR!，错误信息为：Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets

相关资源详情 #

Google表格A #

• 命名区域（A1:C2）：Range_Fruits
• 表格ID：abcde

脚本项目A（关联表格A） #

Code.gs

function getFruits() {
  return SpreadsheetApp
    .openById('abcde')
    .getRangeByName('Range_Fruits')
    .getValues()
    .reduce((a,b) => a.concat(b), []);
}

appscript.json

{
  "timeZone": "Australia/Sydney",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets"]
}

• 在脚本编辑器控制台运行getFruits()可正确返回数组：['Apples','Bananas','Carrots','Dumplings','Eggplant','Figs']
• 已将该脚本部署为库

脚本项目B（关联表格B） #

• 已添加脚本A为库，命名为LibraryScriptA
  Code.gs

function getFirstFruit() {
  return LibraryScriptA.getFruits()[0];
}

• 在脚本编辑器控制台运行getFirstFruit()可正确返回：Apples
  appscript.json

{
  "timeZone": "Australia/Sydney",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "dependencies": {
    "libraries": [
      {
        "userSymbol": "LibraryScriptA",
        "version": "0",
        "libraryId": "AtpircSyrarbiL-lobmySresu_0-noisrev",
        "developmentMode": true
      }
    ]
  },
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets"]
}

Google表格B #

• A1单元格返回#ERROR!，错误提示无权限调用SpreadsheetApp.openById

已尝试操作 #

• 完成两个脚本项目的授权弹窗权限确认
• 手动为两个脚本添加spreadsheets的oauthScopes
• 通过控制台日志排查，脚本在编辑器中运行正常，但表格单元格调用时出错

问题原因及解决方法 #

原因 #

Google表格的单元格自定义函数运行在受限沙箱环境，即使已授予权限，也无法访问当前表格之外的其他文档资源，包括通过库调用的跨文档操作。而脚本编辑器控制台或菜单触发的函数运行在完整授权环境，不受此限制。

解决方法 #

改用自定义菜单触发函数来写入结果，替代单元格公式调用：

在脚本项目B的Code.gs中添加以下代码：

// 打开表格时创建自定义菜单
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('自定义工具')
    .addItem('获取第一个水果', 'writeFirstFruit')
    .addToUi();
}

// 获取结果并写入单元格
function writeFirstFruit() {
  const firstFruit = LibraryScriptA.getFruits()[0];
  SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(firstFruit);
}

添加后，重新加载表格B，顶部会出现「自定义工具」菜单，点击「获取第一个水果」即可将结果写入A1单元格，此方式可正常执行跨文档的库函数调用。

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