使用Airflow Google Provider编辑云端文件：凭证权限不足排查

问题 #

尝试通过Apache Airflow Google Provider修改Google Sheets文件并上传CSV到Google Cloud Storage，执行代码如下：

df = pd.DataFrame(data)
csv_data = df.to_csv(index=True)

gcs_hook = GCSHook(gcp_conn_id=GOOGLE_CONNECTION)
gcs_hook.upload(
     bucket_name=GOOGLE_CLOUD_BUCKET_NAME,
     object_name=csv_name,
     data=csv_data,
)

sheets_hook = GSheetsHook(gcp_conn_id=GOOGLE_CONNECTION)
sheets_hook.clear(GOOGLE_SHEET_ID, range_=sheet_name)
sheets_hook.append_values(
   GOOGLE_SHEET_ID,
   range_=sheet_name,
   values=[df.columns.tolist()] + df.values.tolist(),
)

GOOGLE_CONNECTION是Airflow GUI中定义的Google连接，指向已存在且可被识别的credentials.json文件，获取凭证的步骤为：

• 创建Google Cloud项目
• 启用Google Drive与Google Sheets API
• 创建服务账号并授予Editor角色
• 为服务账号创建密钥并导出为credentials.json
• 通过Drive GUI的Share选项给服务账号邮箱设置Editor权限

但任务执行失败，报错信息：

[2025-04-27, 10:09:26] ERROR - Task failed with exception: source="task"
HttpError: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/1qeNnfd74h6EgjAZkYPWKb3BzYGW5ZZ8h9UyuGvL3_PQ/values/countries:clear?alt=json returned "Request had insufficient authentication scopes.". Details: "[{'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'ACCESS_TOKEN_SCOPE_INSUFFICIENT', 'domain': 'googleapis.com', 'metadata': {'method': 'google.apps.sheets.v4.SpreadsheetsService.ClearValues', 'service': 'sheets.googleapis.com'}}]">

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/sdk/execution_time/task_runner.py", line 825 in run

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/sdk/execution_time/task_runner.py", line 1088 in _execute_task

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/sdk/bases/operator.py", line 408 in wrapper

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/providers/standard/operators/python.py", line 212 in execute

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/providers/standard/operators/python.py", line 235 in execute_callable

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/sdk/execution_time/callback_runner.py", line 81 in run

File "/opt/airflow/dags/ingest_and_save.py", line 139 in export_to_gsheet

File "/opt/airflow/dags/ingest_and_save.py", line 127 in write_table_to_Google

File "/home/airflow/.local/lib/python3.12/site-packages/airflow/providers/google/suite/hooks/sheets.py", line 335 in clear

File "/home/airflow/.local/lib/python3.12/site-packages/googleapiclient/_helpers.py", line 130 in positional_wrapper

File "/home/airflow/.local/lib/python3.12/site-packages/googleapiclient/http.py", line 938 in execute

尝试手动在credentials.json中添加认证范围字段但无效，Google Cloud API中也没找到设置凭证范围的选项，请问哪里操作有误？

解决方法 #

错误原因 #

服务账号的credentials.json本身不存储OAuth作用域信息，作用域是在Airflow的Google连接配置中指定的，Airflow在生成访问令牌时会使用这些作用域。你之前手动修改密钥文件的操作无效，且忽略了Airflow连接中作用域的配置。

修复步骤 #

1. 打开Airflow GUI，进入Admin > Connections页面，找到你的GOOGLE_CONNECTION连接。
2. 编辑该连接，在Extra字段中添加所需的作用域配置，示例如下：

{"scope": ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]}

• https://www.googleapis.com/auth/spreadsheets：授予Google Sheets的读写权限，满足clear和append_values操作需求
• https://www.googleapis.com/auth/drive：授予Google Drive的访问权限（如果需要操作Drive中的文件）

3. 保存连接配置，重新运行Airflow任务即可。

补充说明 #

你之前的服务账号角色分配和Drive共享权限配置是正确的，问题核心是Airflow连接未配置足够的OAuth作用域，导致生成的访问令牌没有操作Google Sheets的权限。

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