I am trying to build a script on my local machine using Python that will download a Google sheet from my account using a short lived federated identity token. I have seen documentation that speaks about differences between doing this depends on context, so starting on a MacOS running Monterey and VScode, and later to HA (Minikube, k9s, etc. and then cloud).
I have enabled the Sheets API via the browser for the current project, obtained a client ID and secret, and have the cli installed. I ran a gcloud auth application-default login. I am currently seeing a 403 and potentially insufficient scopes. Older posts here speaks of clobbering/modifying the .credentials and starting with a proper scope. I know a lot has changed with variable length tokens, scope and all since 2019...
Also I looked here on recommendation from Workspaces support, and seem to be in a pickle, pardon the pun;) Is it still a pickle I'm going to deal with here?
Here is my current code:
"""
BEFORE RUNNING:
---------------
1. If not already done, enable the Google Sheets API
and check the quota for your project at
https://console.developers.google.com/apis/api/sheets
2. Install the Python client library for Google APIs by running
`pip install --upgrade google-api-python-client`
"""
from pprint import pprint
from googleapiclient import discovery
# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
# 'https://www.googleapis.com/auth/drive'
# 'https://www.googleapis.com/auth/drive.file'
# 'https://www.googleapis.com/auth/drive.readonly'
# 'https://www.googleapis.com/auth/spreadsheets'
# 'https://www.googleapis.com/auth/spreadsheets.readonly'
credentials = None
service = discovery.build('sheets', 'v4', credentials=credentials)
# The ID of the spreadsheet to retrieve data from.
spreadsheet_id = '' # TODO: Update placeholder value.
# The A1 notation of the values to retrieve.
range_ = 'A1:Y1' # TODO: Update placeholder value.
# How values should be represented in the output.
# The default render option is ValueRenderOption.FORMATTED_VALUE.
value_render_option = '' # TODO: Update placeholder value.
# How dates, times, and durations should be represented in the output.
# This is ignored if value_render_option is
# FORMATTED_VALUE.
# The default dateTime render option is [DateTimeRenderOption.SERIAL_NUMBER].
date_time_render_option = '' # TODO: Update placeholder value.
# request = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_, valueRenderOption=value_render_option, dateTimeRenderOption=date_time_render_option)
# response = request.execute()
# errors here from above
request = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_)
response = request.execute()
# TODO: Change code below to process the `response` dict:
pprint(response)
and the latest error:
raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/1234567long_spreadsheet_id/values/A1%3AY1?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.GetValues'}}]"
If someone could point me in a good direction- either detailed current documentation as it relates to python (I may have to back up and punt with Apps Script or something for the time being and POC), or nudge me with some pseudocode in the way I need to properly authenticate to get my credential file working with a short lived token (IMHO better for the time being than having a small business trying to manage keys on GCP) and possibly for a refresh after it's stale, it would be greatly appreciated. Thanks in advance.
