2

I'm trying to connect google sheets with python using gspread library.

I have tried a ginormous set of using examples of gspread, for no avail. I have also used google official libraries with the same tsunami of disappointment and despair (actually google code leads to a authentication page under terminal that doesn't work "because javascript is not enabled in your, well, browser")

my example code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile.name('client_secret.json', scope)
gc = gspread.authorize(credentials)
hoja = gc.open('testpy').sheet1
print(hoja.get_all_records())

client.secret.json is renamed from secrey key json file obtained from service account credentials. it is something like this

{
"type": "service_account",
"project_id": "personaltestpygshee6",
"private_key_id": "29etcetc",
"private_key": "-----BEGIN PRIVATE KEY-----\netcetc\n-----END PRIVATE KEY-----\n",
"client_email": "project@project.iam.gserviceaccount.com",
"client_id": 423etcetc",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/project%40project.iam.gserviceaccount.com"
}

when I run the script, this is the error message that I obtain

File "5testgooshee.py", line 6, in <module>
credentials = ServiceAccountCredentials.from_json_keyfile.name('client_secret.json', scope)
AttributeError: type object 'ServiceAccountCredentials' has no attribute 'from_json_keyfile'

I have done my homework, I swear. Steam comes out of my ears. I cannot figure out what is the problem and why it works in a lot of examples except mine

Where is the failure? Why I cannot make it work?

Thanks in advance

Juan Luis Chulilla
  • 338
  • 3
  • 15
  • 1
    Ditch the deprecated `oauth2client`. The google-auth library is geared towards service account authorization. – tehhowch Jan 31 '19 at 17:57
  • 1
    If your script is used, please modify from ``ServiceAccountCredentials.from_json_keyfile.name('client_secret.json', scope)`` to ``ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)``, and try again. – Tanaike Jan 31 '19 at 23:08
  • 1. when I tested account authorization, in the terminal appears what seems to be a google account login, like a text browser. When I fill the fields, the service answers that javascript is not authorized in my browser, and I cannot progress beyond that point. Besides, gspread is used by quite a lot of people and they seem to use it seamessly :( – Juan Luis Chulilla Feb 01 '19 at 09:42
  • Thanks, @tanaike – Juan Luis Chulilla Feb 01 '19 at 09:43
  • Thanks, @Tanaike. Now the error message is different: `oauth2client.client.HttpAccessTokenRefreshError: invalid_grant: Invalid JWT Signature.` I would think that it is not related with wrong application name (project ID) or wrong service account ID (email), because error is located on line 8, in authorization, before opening any sheet – Juan Luis Chulilla Feb 01 '19 at 10:00
  • 1
    @Juan Luis Chulilla Thank you for replying. From the error message, it is found that the script works. In my environment, the script works. But about ``Invalid JWT Signature.``, this might be useful for your situation. https://stackoverflow.com/questions/37710245/gspread-to-access-google-spreadsheet-httpaccesstokenrefresherror-invalid-jwt – Tanaike Feb 01 '19 at 22:46
  • thanks again @Tanaike. I suspect that at least part of my problem is related with my windows box and file codification, but all my tests have revealed themselves as cul-de-sacs :( They also recommend to ServiceAccountCredentials, but when I checked the official example from google itself using o ServiceAccountCredentials, it start a login screen in the terminal, and when I authenticate, it returns a error that said that javascript is not enabled. – Juan Luis Chulilla Feb 01 '19 at 23:09
  • @Juan Luis Chulilla I'm sorry. I cannot understand about your current situation. – Tanaike Feb 01 '19 at 23:27

1 Answers1

0

Finally there is a (temporary?) solution for the problem. We need to keep in mind that OAuth2 is deprecated and that all solutions based on it such as GSpread can stop functioning.

Now, the solution:

  1. As @Tanaike pointed out, the starting point was a typo on the call of service account credentials. It needs to be pointed out that in some tutos it is spelled as ServiceAccountCredentials.from_json_keyfile.name('client_secret.json', scope), where it should be ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope). There are tutorials over there in which this typo is reproduced.
  2. Besides, there is another problem with Windows. You need to install PyOpenSSL with python. If you use a windows box or WSL, you need to install the complete version of win32OpenSSL (source: ImportError: cannot import name SignedJwtAssertionCredentials) http://slproweb.com/products/Win32OpenSSL.html You need to install it before PyOpenSSL... and I have discovered that you have to install the same version as python, either 32 or 64 bits. It can sound silly or trivial, but if you don't consider it, it will not work

Thanks all for your time and answers

Juan Luis Chulilla
  • 338
  • 3
  • 15