How to set the scope using Google Operators in Airflow

Question

I have a task using the GCSToGoogleSheetsOperator in Airflow where Im trying to add data to a sheet.

I have added the service credential email to the sheet I want to edit with editor privileges, and received this error:

googleapiclient.errors.HttpError: 
<HttpError 403 when requesting 
https://sheets.googleapis.com/v4/spreadsheets/<SHEET_ID>/values/Sheet1?valueInputOption=RAW&includeValuesInResponse=false&responseValueRenderOption=FORMATTED_VALUE&responseDateTimeRenderOption=SERIAL_NUMBER&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': {
    'service': 'sheets.googleapis.com', 
    'method': 'google.apps.sheets.v4.SpreadsheetsService.UpdateValues'}
    }]>

I cant update the sheet, but the GCS and BigQuery operators work fine.

My connection configuration looks like the following:

AIRFLOW_CONN_GOOGLE_CLOUD=google-cloud-platform://?extra__google_cloud_platform__key_path=%2Fopt%2Fairflow%2Fcredentials%2Fgoogle_credential.json

I tried following the instructions to add the scope https://www.googleapis.com/auth/spreadsheets.

Which URL encoded looks like:

AIRFLOW_CONN_GOOGLE_CLOUD=google-cloud-platform://?extra__google_cloud_platform__key_path=%2Fopt%2Fairflow%2Fcredentials%2Fgoogle_credential.json&extra__google_cloud_platform__scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets

Now, operators which previously worked error out like this:

google.api_core.exceptions.Forbidden: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/my-project/jobs?prettyPrint=false: Request had insufficient authentication scopes.

And the GCSToGoogleSheetsOperator operator still error out like this:

google.api_core.exceptions.Forbidden: 403 GET https://storage.googleapis.com/download/storage/v1/b/my-bucket/o/folder%2Fobject.csv?alt=media: Insufficient Permission: ('Request failed with status code', 403, 'Expected one of', <HTTPStatus.OK: 200>, <HTTPStatus.PARTIAL_CONTENT: 206>)

How can I set the permissions correctly to use both BigQuery, GCS and Sheets operators?

This error message is about the scope of your request. Can you share with us the list of scope/s that you are using for this request? — PatrickdC, May 19 '22 at 01:20
You can refer to this other post with a similar error message as a reference: https://stackoverflow.com/questions/38534801/google-spreadsheet-api-request-had-insufficient-authentication-scopes — PatrickdC, May 19 '22 at 01:22
Thanks @patrickdc. I tried setting the scope, but got more errors instead, and edited the question. I think Im setting it incorrectly somehow, do you know how to fix it? — Nilo Araujo, May 19 '22 at 13:38

score 1 · Accepted Answer · answered May 19 '22 at 19:42

Adding a scope seems to ignore the IAM roles, so its either one or the other.

The service account had roles needed to access GCS and BigQuery, but by adding the scope https://www.googleapis.com/auth/spreadsheets, the service would ignore the privileges granted by the roles and look only at the ones specified by the scopes.

So, to recover it, you must add both the spreadsheet and cloud-platform scopes (or more strict scopes). cloud-platform will provide access to GCS and BigQuery and spreadsheets to Google Sheets API.

If you set your connection using environment variables, you have to URL encode the arguments, so to create a GOOGLE_CLOUD connection, you will have to do something like this, which is not encoded...

AIRFLOW_CONN_GOOGLE_CLOUD=google-cloud-platform://?extra__google_cloud_platform__key_path=/abs/path_to_file/credential.json&extra__google_cloud_platform__scope=https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets

To encode, which is the version you have to use, replace /, , and ::

AIRFLOW_CONN_GOOGLE_CLOUD=google-cloud-platform://?extra__google_cloud_platform__key_path=%2Fabs%2Fpath_to_file%2Fcredentials%2Fgoshare-driver-c08e0904285b.json&extra__google_cloud_platform__scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcloud-platform%2Chttps%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets

How to set the scope using Google Operators in Airflow

1 Answers1