things to do

Problems that occurred before accessing Google Spreadsheet from a Python program, and a memorandum of countermeasures.

Method

Use gspread, a Python library for reading and writing Google Spreadsheets. https://github.com/burnash/gspread

How to use gspread

I created a spreadsheet named testbook on Google Spreadsheet in advance.

Access is http://gspread.readthedocs.org/en/latest/oauth2.html On the street. Execute the following, which is almost the same as the sample.

    import gspread
    from oauth2client.service_account import ServiceAccountCredentials

    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        'sample-xxxxxx.json', scope)

    gc = gspread.authorize(credentials)
    wks = gc.open("testbook")

Just open the spreadsheet on Google Sheets in preparation for reading and writing. But it failed. Below, the contents and countermeasures for each of the multiple problems that occurred before moving the above.

trouble shooting

oauth2client.client.HttpAccessTokenRefreshError First error

Traceback (most recent call last):
(Omission)
    gc = gspread.authorize(credentials)
  File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 331, in authorize
    client.login()
  File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 101, in login
    self.auth.refresh(http)
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/client.py", line 659, in refresh
    self._refresh(http.request)
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/client.py", line 864, in _refresh
    self._do_refresh_request(http_request)
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/client.py", line 933, in _do_refresh_request
    raise HttpAccessTokenRefreshError(error_msg, status=resp.status)
oauth2client.client.HttpAccessTokenRefreshError: invalid_grant: Invalid JWT: Token must be a short-lived token and in a reasonable timeframe

The cause was that I didn't want to synchronize the time (it was behind the real time). If you re-execute the time after synchronizing ʻOauth2client.client.HttpAccessTokenRefreshError` no longer appears.

google api - Token must be a short-lived token and in a reasonable timeframe - Stack Overflow http://stackoverflow.com/questions/36189612/token-must-be-a-short-lived-token-and-in-a-reasonable-timeframe

But failed with another error.

requests.exceptions.SSLError

Traceback (most recent call last):
(Omission)
    wks = gc.open("testbook")
  File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 145, in open
    feed = self.get_spreadsheets_feed()
  File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 231, in get_spreadsheets_feed
    r = self.session.get(url)
  File "/usr/local/lib/python2.7/dist-packages/gspread/httpsession.py", line 75, in get
    return self.request('GET', url, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/gspread/httpsession.py", line 67, in request
    response = func(url, data=data, headers=request_headers)
  File "/usr/lib/python2.7/dist-packages/requests/api.py", line 60, in get
    return request('get', url, **kwargs)
  File "/usr/lib/python2.7/dist-packages/requests/api.py", line 49, in request
    return session.request(method=method, url=url, **kwargs)
  File "/usr/lib/python2.7/dist-packages/requests/sessions.py", line 457, in request
    resp = self.send(prep, **send_kwargs)
  File "/usr/lib/python2.7/dist-packages/requests/sessions.py", line 569, in send
    r = adapter.send(request, **kwargs)
  File "/usr/lib/python2.7/dist-packages/requests/adapters.py", line 420, in send
    raise SSLError(e, request=request)
requests.exceptions.SSLError

Does it occur with python 2.7 series? It didn't happen in Python 3.5.1. The immediate solution in python 2.7 is to include the old version of certifi.

pip uninstall -y certifi && pip install certifi==2015.04.28

ssl - SSL3_GET_SERVER_CERTIFICATE certificate verify failed on Python when requesting (only) *.google.com - Stack Overflow http://stackoverflow.com/questions/34646942/ssl3-get-server-certificate-certificate-verify-failed-on-python-when-requesting

The version of certifi that was originally included is

pip list | grep certifi
certifi (2015.11.20.1)

When I re-executed, requests.exceptions.SSLError disappeared, but another error occurred.

SpreadsheetNotFound

Traceback (most recent call last):
(Omission)
    wks = gc.open("testbook")
  File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 152, in open
    raise SpreadsheetNotFound
gspread.exceptions.SpreadsheetNotFound

The gspread documentation has the following sentence:

Using OAuth2 for Authorization — gspread 0.3.0 documentation http://gspread.readthedocs.org/en/latest/oauth2.html

Go to Google Sheets and share your spreadsheet with an email you have in your json_key['client_email']. Otherwise you’ll get a SpreadsheetNotFound exception when trying to open it.

As you can see, in the client_email described in the JSON file of the Credential information Granted editor rights.

When I ran it again, it succeeded.

Other troubleshooting

Failed to install `PyOpenSSL` package

Failed during PyOpenSSL dependent package installation (cffi package). The cause is lack of dependent packages on the Linux side.

Looking at the documentation for the cffi package, there is a sentence like the following

http://cffi.readthedocs.org/en/latest/installation.html

on CPython, on non-Windows platforms, you also need to install libffi-dev in order to compile CFFI itself.

$ sudo aptitude search libffi-dev
p   libffi-dev                                                                               - Foreign Function Interface library (development files)
$ sudo aptitude install libffi-dev

pip install PyOpenSSL --upgrade

success

[Python] Troubleshooting before accessing Google Spreadsheet with gspread