使用服务账号查询关联Google Sheet的BigQuery表遇403权限错误

使用服务账号及其生成的JSON密钥文件查询关联外部Google Sheet的BigQuery表时，持续报错：
google.api_core.exceptions.Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

已完成的配置：

• 服务账号拥有目标表的编辑/查看权限
• 密钥文件中配置了Drive API权限范围
• 项目IAM中已启用Google Drive API

from google.cloud import bigquery
from google.oauth2 import service_account
import pandas as pd

# 初始化BigQuery客户端
credentials = service_account.Credentials.from_service_account_file("service_account_file.json")
client = bigquery.Client(credentials=credentials)

gbq_staging_table = "Table ID" # 格式为dataset.table

query = f"""
SELECT * FROM `{gbq_staging_table}`
"""
staging_df = client.query(query).to_dataframe()

{
  "type": "service_account",
  "project_id": "projectid",
  "private_key_id": "xxxx",
  "private_key": "-----BEGIN PRIVATE KEY-----",
  "client_email": "serviceaccount.iam.gserviceaccount.com",
  "client_id": "xxxxx",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/xxxxx",
  "scopes": ["https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/cloud-platform","https://www.googleapis.com/auth/bigquery"]
}

• 共享Google Sheet给服务账号：打开关联的Google Sheet，点击右上角「共享」，输入密钥文件中client_email对应的邮箱地址，至少赋予「查看者」权限，保存设置。
• 显式指定权限范围：在初始化Credentials对象时，显式传入scopes参数，避免权限范围自动获取异常。修改后的代码如下：

  from google.cloud import bigquery
  from google.oauth2 import service_account
  import pandas as pd
  
  # 显式声明所需权限范围
  SCOPES = [
      "https://www.googleapis.com/auth/drive",
      "https://www.googleapis.com/auth/cloud-platform",
      "https://www.googleapis.com/auth/bigquery"
  ]
  
  credentials = service_account.Credentials.from_service_account_file(
      "service_account_file.json",
      scopes=SCOPES
  )
  client = bigquery.Client(credentials=credentials)
  
  gbq_staging_table = "Table ID" # 格式为dataset.table
  
  query = f"""
  SELECT * FROM `{gbq_staging_table}`
  """
  staging_df = client.query(query).to_dataframe()
  

• 检查BigQuery外部表配置：进入BigQuery控制台，找到目标外部表，编辑表配置，确认「驱动身份验证」设置为「服务账号身份」，且关联的Google Sheet路径正确。
• 确认API启用状态：在Google Cloud控制台的「API和服务」->「库」中，再次确认Google Drive API和BigQuery API已启用。

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