Google Apps Script访问BigQuery关联GoogleSheet表时Drive凭证被拒如何解决

问题原因 #

该报错是因为BigQuery查询关联Google Sheet的外部表时，需要获取Google Drive访问权限，而默认的Google Apps Script授权配置没有传递Drive权限给BigQuery服务，导致权限校验失败，你可以按照以下步骤修复：

解决步骤 #

1. 开启appsscript.json显示权限 #

打开Google Apps Script项目，点击左侧齿轮形状的「项目设置」图标，勾选「在编辑器中显示appsscript.json 清单文件」，回到代码编辑器即可看到该清单文件。

2. 修改appsscript.json配置 #

将以下配置合并到你现有的清单文件中，注意不要覆盖已有的runtimeVersion、timeZone等自定义配置：

{
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "BigQuery",
        "serviceId": "bigquery",
        "version": "v2"
      }
    ]
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/bigquery",
    "https://www.googleapis.com/auth/drive.readonly",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

核心变更为新增了drive.readonly的OAuth授权范围，确保脚本可以将你的Drive访问权限传递给BigQuery服务。

3. 调整BigQuery查询代码 #

在你的查询请求参数中，必须指定使用标准SQL（外部表不支持旧版SQL），示例代码如下：

const projectId = '你的GCP项目ID';
const queryRequest = {
  query: '你要执行的SQL语句',
  useLegacySql: false
};

// 执行查询
const queryResults = BigQuery.Jobs.query(queryRequest, projectId);

4. 重新授权脚本 #

修改完成后首次运行脚本会弹出授权窗口，按提示完成授权即可。如果之前已经授权过脚本，需要先撤销旧授权再重新操作：

• 打开Google账号「安全」设置页
• 进入「第三方应用访问权限管理」
• 找到当前对应的Apps Script项目，删除已有访问权限
• 回到脚本编辑器重新运行触发授权流程

异常排查 #

如果完成以上操作仍报错，可检查两个配置：

• 确认对应Google Sheet的所有者和你当前使用的账号一致，或你已经被授予至少查看权限
• 确认BigQuery中的外部表是使用你当前账号创建，而非其他服务账号创建

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