I'm trying to access a Google Spreadsheet via the GData API using oAuth 2.0 service-account credentials created for a Python 2.7 app hosted on Google App Engine.
- The app uses the recent gdata-python-client from Google, v. 2.0.18 (gdata and atom).
- The app uses the recent google-api-python-client-gae, v. 1.2.
- In the Google Developer Console for this project (in this example referred to as "my-gae-app"), I have created a Service Account and delegated domain-wide authority to the service account as described here
- The desired spreadsheet in Google Drive belongs to a Google Apps for Work domain, here referred to as "mygoogleappsdomain.com".
- I have granted read+write access for the spreadsheet to
[email protected]
and to the email address shown for this service account and which is assigned to clientEmail
variable in the code below. Not sure which of the two email addresses would be actually needed, so I assigned both. The user with the impersonateUser
email address also has read+write access to this spreadsheet.
With Google API Python Client's AppAssertionCredentials
I can access the meta-data of the desired spreadsheet via the Google Drive API. However, if I try to access the spreadsheet's content using gdata, I'm getting errors. The best result I could get so far with the service account is using SignedJwtAssertionCredentials
, as suggested here. However, I'm stuck with this AccessRefreshTokenError: access denied
and I don't understand what's going wrong.
import os
import httplib2
from google.appengine.api import memcache
from apiclient.discovery import build
from oauth2client.client import SignedJwtAssertionCredentials
import gdata.spreadsheets.client
import gdata.spreadsheet.service
# AppAssertionCredentials is not supported in gdata python client library,
# so we use SignedJwtAssertionCredentials with the credential
# file of this service account.
# Load the key in PKCS 12 format that you downloaded from the Google API
# Console when you created your Service account.
clientEmail = '10346........-g3dp......................3m1em8@developer.gserviceaccount.com'
p12File = 'app.p12'
path = os.path.join(ROOT_DIR, 'data', 'oAuth2', p12File)
impersonateUser = '[email protected]'
spreadsheetKey = '1mcJHJ...................................juQMw' # ID copied from URL of desired spreadsheet in Google Drive
with open(path) as f:
privateKey = f.read()
f.close()
# Getting credentials with AppAssertionCredentials only worked successfully
# for Google API Client Library for Python, e.g. accessing file's meta-data.
# So we use SignedJwtAssertionCredentials, as suggested in
# https://stackoverflow.com/questions/16026286/using-oauth2-with-service-account-on-gdata-in-python
credentials = SignedJwtAssertionCredentials(
clientEmail,
privateKey,
scope=(
'https://www.googleapis.com/auth/drive.file ',
# added the scope above as suggested somewhere else,
# but error occurs with and with-out this scope
'https://www.googleapis.com/auth/drive',
'https://spreadsheets.google.com/feeds',
'https://docs.google.com/feeds'
),
sub=impersonateUser
)
http = httplib2.Http()
http = credentials.authorize(http)
auth2token = gdata.gauth.OAuth2TokenFromCredentials(credentials)
# error will occur, wether using SpreadsheetsService() or SpreadsheetsClient()
#srv = gdata.spreadsheet.service.SpreadsheetsService()
#srv = auth2token.authorize(srv)
clt = gdata.spreadsheets.client.SpreadsheetsClient()
clt = auth2token.authorize(clt)
# Until here no errors
wks = clt.get_worksheets(spreadsheetKey)
# AccessTokenRefreshError: access_denied
This is the error I get in the remote shell:
s~my-gae-app> wks = clt.get_worksheets(spreadsheetKey)
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "gdata/spreadsheets/client.py", line 108, in get_worksheets
**kwargs)
File "gdata/client.py", line 640, in get_feed
**kwargs)
File "gdata/client.py", line 267, in request
uri=uri, auth_token=auth_token, http_request=http_request, **kwargs)
File "atom/client.py", line 122, in request
return self.http_client.request(http_request)
File "gdata/gauth.py", line 1344, in new_request
refresh_response = self._refresh(request_orig)
File "gdata/gauth.py", line 1485, in _refresh
self.credentials._refresh(httplib2.Http().request)
File "/usr/local/lib/python2.7/dist-packages/oauth2client/client.py", line 653, in _refresh
self._do_refresh_request(http_request)
File "/usr/local/lib/python2.7/dist-packages/oauth2client/client.py", line 710, in _do_refresh_request
raise AccessTokenRefreshError(error_msg)
AccessTokenRefreshError: access_denied
I'm not sure if this indicates that this service-account is denied access to the spreadsheet, or if there was an error with refreshing the access token. Do you know what's wrong with this code or setup?
See Question&Answers more detail:
os