pythongoogle-app-enginegoogle-oauthgoogle-sheets-apigdata-python-client

AccessTokenRefreshError: Google Spreadsheet API with oAuth 2.0 Service Account of App Engine App

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.

  1. The app uses the recent gdata-python-client from Google, v. 2.0.18 (gdata and atom).
  2. The app uses the recent google-api-python-client-gae, v. 1.2.
  3. 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
  4. The desired spreadsheet in Google Drive belongs to a Google Apps for Work domain, here referred to as "mygoogleappsdomain.com".
  5. I have granted read+write access for the spreadsheet to my-gae-app@appspot.gserviceaccount.com 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 = 'user@mygoogleappsdomain.com'
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?

Solution

  • I've figured out that calling SignedJwtAssertionCredentials with-out the sub parameter (for the "impersonated" user) will not produce AccessTokenRefreshError: access_denied

    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 = 'user@mygoogleappsdomain.com'
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
# http://stackoverflow.com/questions/16026286/using-oauth2-with-service-account-on-gdata-in-python
# but with-out the sub parameter!
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)
# this pattern would eventually also work using SpreadsheetsService()
# SpreadsheetsService methods are different from SpreadsheetsClient, though
#srv = gdata.spreadsheet.service.SpreadsheetsService()
#srv = auth2token.authorize(srv)

clt = gdata.spreadsheets.client.SpreadsheetsClient()
clt = auth2token.authorize(clt)

wks = clt.get_worksheets(spreadsheetKey)
# works now!