Search code examples
pythongoogle-sheetsgspread

How to read data from Google Sheets in Python

I have a Google Sheet https://docs.google.com/spreadsheets/d/1Ycg7zTxds9DZnDvTrFcyNNKuTUxg6Yy6WF0a8Wc02WQ/edit#gid=0 I can read only. I need to read it in Python. I uploaded a video of what I am doing https://youtu.be/4cK0g9Bm_7w

import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ["https://docs.google.com/spreadsheets/d/1Ycg7zTxds9DZnDvTrFcyNNKuTUxg6Yy6WF0a8Wc02WQ/edit#gid=0",
     "https://docs.google.com/spreadsheets/d/1Ycg7zTxds9DZnDvTrFcyNNKuTUxg6Yy6WF0a8Wc02WQ/edit?usp=sharing"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("[XO] developer test data").sheet1

I have an error:

Traceback (most recent call last):
File "C:\Python379\lib\site-packages\google\oauth2\_client.py", line 196, in jwt_grant
access_token = response_data["access_token"]
KeyError: 'access_token'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "E:/Прогрммирование/Последние алгоритмы/Git/GoogleSheets/sheets.py", line 11, in <module>
sheet = client.open("[XO] developer test data").sheet1
File "C:\Python379\lib\site-packages\gspread\client.py", line 121, in open
self.list_spreadsheet_files(title),
File "C:\Python379\lib\site-packages\gspread\client.py", line 98, in list_spreadsheet_files
res = self.request('get', url, params=params).json()
File "C:\Python379\lib\site-packages\gspread\client.py", line 70, in request
headers=headers,
File "C:\Python379\lib\site-packages\requests\sessions.py", line 546, in get
return self.request('GET', url, **kwargs)
File "C:\Python379\lib\site-packages\google\auth\transport\requests.py", line 478, in request
self.credentials.before_request(auth_request, method, url, request_headers)
File "C:\Python379\lib\site-packages\google\auth\credentials.py", line 133, in before_request
self.refresh(request)
File "C:\Python379\lib\site-packages\google\oauth2\service_account.py", line 377, in refresh
request, self._token_uri, assertion
File "C:\Python379\lib\site-packages\google\oauth2\_client.py", line 199, in jwt_grant
six.raise_from(new_exc, caught_exc)
File "<string>", line 3, in raise_from
google.auth.exceptions.RefreshError: ('No access token in response.', {'id_token': 'eyJhbGciOiJSUzI1NiIsImtpZCI6IjE5ZmUyYTdiNjc5NTIzOTYwNmNhMGE3NTA3OTRhN2JkOWZkOTU5NjEiLCJ0eXAiOiJKV1QifQ.eyJhdWQiOiJodHRwczovL2RvY3MuZ29vZ2xlLmNvbS9zcHJlYWRzaGVldHMvZC8xWWNnN3pUeGRzOURabkR2VHJGY3lOTkt1VFV4ZzZZeTZXRjBhOFdjMDJXUS9lZGl0I2dpZD0wLGh0dHBzOi8vZG9jcy5nb29nbGUuY29tL3NwcmVhZHNoZWV0cy9kLzFZY2c3elR4ZHM5RFpuRHZUckZjeU5OS3VUVXhnNll5NldGMGE4V2MwMldRL2VkaXQ_dXNwPXNoYXJpbmciLCJhenAiOiJhY2MxMC0yMjhAcHJvamVjdC0xMC0zMTc0MDIuaWFtLmdzZXJ2aWNlYWNjb3VudC5jb20iLCJlbWFpbCI6ImFjYzEwLTIyOEBwcm9qZWN0LTEwLTMxNzQwMi5pYW0uZ3NlcnZpY2VhY2NvdW50LmNvbSIsImVtYWlsX3ZlcmlmaWVkIjp0cnVlLCJleHAiOjE2MjQxNjA4NTIsImlhdCI6MTYyNDE1NzI1MiwiaXNzIjoiaHR0cHM6Ly9hY2NvdW50cy5nb29nbGUuY29tIiwic3ViIjoiMTA4OTUzMjg5ODAwOTQ4MzEyOTgxIn0.RYEt9eTvaCXSRPH-ddjB4s5wCE60tt2w-ZgL2aaeBT8ZTyKtlDbpMKV1zG8XRkxqCLs_xde-TEwTZoRQ8OKuJGQIZr6U8mSmqjW7xlhz5cvDLL03o-muks06FbLsgAFE2yEow5EdFcEL8hKt8LGuFoQWuiNWNT-sd-Z-vJMG0-XC6twrYgYp59tdmxCCG3MEprKbtgHpwAWHPoWjybKkETGXVnv17DSV99LIXY9FIzQVV4FV4reBn_4sgcT4y6IcbHqe6hpt4joEDYBsVxImzKk7IJjJfsRCg-kGpfc1yOC831qbupMlF45H75XMq9WWmnrn3Su4YFIBTugUxrMpdA'})
Solution

  • Issues:

    • You are not providing valid scopes. A scope is not the URL of a file you want to access. A scope determines what actions an API client can make, like reading user's spreadsheets, or reading and writing user's spreadsheets; it doesn't refer to specific files. Here you can see a list of the existing scopes in Sheets API: Authorizing requests with OAuth 2.0.
    • oauth2client is deprecated. Consider using google-auth instead.

    Code sample:

    import gspread
from google.oauth2.service_account import Credentials

scopes = [
  'https://www.googleapis.com/auth/spreadsheets' # Change according to your preferences
]

credentials = Credentials.from_service_account_file(
  'creds.json',
  scopes=scopes
)

client = gspread.authorize(creds)
sheet = client.open("[XO] developer test data").sheet1

    Notes:

    • gspread is not officially supported by Google. Here is the official library. And here is the corresponding quickstart for Sheets API.
    • I am assuming that creds.json refers to valid service account credentials.
    • If you store creds.json to ~/.config/gspread/creds.json, you should be able to do this, according to the gspread documentation:
    import gspread
client = gspread.service_account()
sheet = client.open("[XO] developer test data").sheet1

    Reference: