BigQuery关联Google Sheet触发Cloud Function遇403权限拒绝问题

问题描述 #

我在BigQuery中创建了关联Google Sheet的表，该Sheet包含Apps Script用于调用Cloud Function查询该表并创建新表。查询本身可正常运行，但触发Apps Script时收到Cloud Function返回的错误：

Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

已执行以下操作：

• Cloud Function与Apps Script处于同一Google Cloud项目
• 为Cloud Function调用者添加allUsers权限
• 将Google Sheet共享给服务账号邮箱
• 在「服务」中启用Google Drive API，在Apps Script开头添加DriveApp.getRootFolder()并修改权限范围

但问题仍未解决，附上我的Apps Script代码及JSON配置：

Apps Script代码 #

DriveApp.getRootFolder();
function onEdit(e) {
  var url = 'myCloudFunctionURL'; 
  var range = e.range;
  if (range.getColumn() == 1) { // Check if edited cell is in column A
    var word = range.getValue();

    var payload = {
      "variable": word, // Use the retrieved value as the payload data
    };
    
    var options = {
      'method' : 'post',
      'contentType': 'application/json',
      'payload' : JSON.stringify(payload)
    };
    
    var response = UrlFetchApp.fetch(url, options);
    Logger.log(response.getContentText());
  }
}

JSON配置 #

{
  "timeZone": "America/Argentina/Buenos_Aires",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Drive",
        "version": "v2",
        "serviceId": "drive"
      }
    ]
    
  },
  "oauthScopes": [
          "https://www.googleapis.com/auth/spreadsheets.readonly",
          "https://www.googleapis.com/auth/userinfo.email",
          "https://www.googleapis.com/auth/drive.readonly",
          "https://www.googleapis.com/auth/drive"
        ],
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

解决方案 #

1. 给Cloud Function服务账号添加Drive权限 #

Cloud Function默认使用PROJECT_ID@appspot.gserviceaccount.com服务账号，需在Google Cloud控制台IAM页面给该账号添加Drive File Viewer或Drive Editor角色（至少需要读取权限），确保它能访问Drive资源。

2. 授权BigQuery服务账号访问Sheet #

BigQuery访问关联Sheet时使用的是service-<PROJECT_NUMBER>@gcp-sa-bigquery.iam.gserviceaccount.com服务账号，必须将该账号添加到Sheet的共享列表中，授予至少查看权限。

3. 替换简单触发器为可安装触发器 #

onEdit作为简单触发器，运行时使用匿名权限，无法传递有效OAuth令牌给Cloud Function。操作步骤：

• 将原onEdit函数重命名为triggerOnEdit
• 在Apps Script编辑器中，点击「编辑」→「当前项目的触发器」→「添加触发器」
• 选择函数triggerOnEdit，事件类型选「从电子表格」→「编辑时」，完成授权后保存

4. 传递调用者OAuth令牌到Cloud Function（可选） #

如果Cloud Function需要以调用者身份访问BigQuery，修改UrlFetchApp的请求参数，添加身份令牌：

var options = {
  'method' : 'post',
  'contentType': 'application/json',
  'payload' : JSON.stringify(payload),
  'headers': {
    'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
  }
};

同时在Cloud Function的IAM设置中，给Apps Script运行用户添加roles/cloudfunctions.invoker权限，替代仅依赖allUsers的配置。

5. 确认Cloud Function所在项目启用Drive API #

进入Google Cloud控制台API库，搜索「Google Drive API」，确认项目中该API处于启用状态（仅Apps Script启用不够）。

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