AppScript自动刷新Google Sheet关联BigQuery表时权限报错排查

问题背景 #

• 两个BigQuery表分属不同数据集，权限配置一致，本人为表所有者
• 手动点击Google Sheet的「全部刷新」按钮，所有表均可正常加载数据
• 通过AppScript调用refreshAllDataSources()时，仅一个表刷新成功，另一个抛出权限错误：

You do not have access to the connected BigQuery table. The required permission is: "bigquery.tables.getData"

核心原因 #

手动刷新与脚本执行的权限上下文不同：手动操作使用你的个人用户身份直接访问BigQuery，但AppScript默认使用项目服务账号执行，该服务账号未被授予报错数据集的访问权限，即便你是表所有者。

解决方案 #

方案1：为脚本服务账号添加BigQuery权限 #

1. 获取脚本服务账号邮箱：
   • 打开AppScript编辑器，点击右上角「项目设置」
   • 勾选「显示appsscript.json清单文件」
   • 打开appsscript.json，服务账号格式为：[你的项目ID]@appspot.gserviceaccount.com
2. 配置BigQuery权限：
   • 登录BigQuery控制台，找到报错的数据集
   • 进入数据集「权限」页面，添加上述服务账号邮箱
   • 授予「BigQuery数据查看者」角色（该角色包含bigquery.tables.getData权限）

方案2：强制脚本使用用户身份执行 #

修改脚本并重新授权：

function BigQueryRefresh() {
  var sheet = SpreadsheetApp.getActive();
  SpreadsheetApp.enableAllDataSourcesExecution();
  sheet.refreshAllDataSources();
   
  ScriptApp.newTrigger("myFunction")
            .timeBased()
            .after(60*1000)
            .create();
}

首次运行脚本时，在授权弹窗中确保勾选所有BigQuery相关权限，完成用户身份授权。

方案3：检查数据源连接身份 #

• 打开Google Sheet，右键点击报错的数据区域 → 「数据源设置」
• 确认连接使用的是「我的账户」（你的个人账号）而非「服务账号」
• 若为服务账号连接，切换为个人账号重新建立数据源连接

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