I'm trying to populate a Google sheet from a Python program using its API, using the quickstart guide (https://developers.google.com/sheets/api/quickstart/python) as a starting point:
from __future__ import print_function
from apiclient.discovery import build
from httplib2 import Http
from oauth2client import file as oauth_file, client, tools
# Setup the Sheets API
SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
store = oauth_file.Storage('token.json')
creds = store.get()
if not creds or creds.invalid:
flow = client.flow_from_clientsecrets('credentials.json', SCOPES)
creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))
However, I was only able to get this to work using the flow_from_clientsecrets() method, which (by default) opens an authentication page in the browser. This does not seem suitable for something I'd like to run periodically on a production server.
In any case, according to https://pypi.org/project/oauth2client/, oauth2client is deprecated and developers are recommended to use google-auth instead.
Therefore, I tried to adapt this example as follows (following https://google-auth.readthedocs.io/en/latest/user-guide.html#service-account-private-key-files):
from google.oauth2 import service_account
credentials = service_account.Credentials.from_service_account_file(
'google_client_secret.json')
Where 'google_client_secret.json' is the JSON file downloaded from the API console, which looks like this (scrambled and pretty-printed):
{
"installed": {
"client_id": "33e.apps.googleusercontent.com",
"project_id": "nps-survey-1532981793379",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_secret": "foobar",
"redirect_uris": [
"urn:ietf:wg:oauth:2.0:oob",
"http://localhost"
]
}
}
When I run the script, however, I get the following error:
(lucy-web-CVxkrCFK) bash-3.2$ python nps.py
Traceback (most recent call last):
File "nps.py", line 25, in <module>
'google_client_secret.json')
File "/Users/kurtpeek/.local/share/virtualenvs/lucy-web-CVxkrCFK/lib/python3.7/site-packages/google/oauth2/service_account.py", line 209, in from_service_account_file
filename, require=['client_email', 'token_uri'])
File "/Users/kurtpeek/.local/share/virtualenvs/lucy-web-CVxkrCFK/lib/python3.7/site-packages/google/auth/_service_account_info.py", line 73, in from_filename
return data, from_dict(data, require=require)
File "/Users/kurtpeek/.local/share/virtualenvs/lucy-web-CVxkrCFK/lib/python3.7/site-packages/google/auth/_service_account_info.py", line 51, in from_dict
'fields {}.'.format(', '.join(missing)))
ValueError: Service account info was not in the expected format, missing fields token_uri, client_email.
From dropping into the debugger, I noticed that the problem is basically that the dictionary passed into the from_dict() method is the entire dictionary in the google_client_secret.json file, which has only one key, "installed". What the from_dict() method seems to be 'looking for' is the sub-dictionary, as this contains a token_uri key, although even this doesn't contain the required client_email key.
What I'm suspecting is that I've created the wrong type of OAuth2 client for my use case, because the JSON containing the client secrets isn't in the expected format. Any ideas how I could fix this?
From https://developers.google.com/api-client-library/python/guide/aaa_oauth, there are three types of client IDs:
My use case is a Service Account, and upon creating one and choosing the Furnish a new private key option, I found that I obtained a JSON file which does match the expected format. (The one I had was for an installed application).