Read time: 4 minutes
Read data from a Google Sheets spreadsheet using Python. Quickstart: https://developers.google.com/sheets/api/quickstart/python Go to the above URL and click on the “Enable the Google Sheets API” button which will look like the following image: 
- Create a project, fill the details and it will download a credentials.json file.
- Move it to your project’s working directory.
- Rename this file to client_secret.json
- Install the dependencies:
pip install --upgrade google-api-python-client oauth2client - Run this script: https://github.com/gsuitedevs/python-samples/blob/master/sheets/quickstart/quickstart.py
- It will open up the browser to ask for access via your google account. Allow it.
- I ran this script again and got this error:
$ python quickstart.py
Traceback (most recent call last): File "quickstart.py", line 22, in range=RANGE_NAME).execute()
File "/home/mandeep/envs/gapi/lib/python3.6/site-packages/googleapiclient/_helpers.py", line 130, in positional_wrapper return wrapped(*args, **kwargs)
File "/home/mandeep/envs/gapi/lib/python3.6/site-packages/googleapiclient/http.py", line 842, in execute
raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/2BviMVs2XJA5pFNdKuBdBYagnWVqrtlos38OgvE2urmc/values/Class%20Data%21A2%3AE?alt=json
returned "Google Sheets API has not been used in project 9272345580241 before or it is disabled.
Enable it by visiting https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=9272345580241 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.">
- Now as said in the error above, open the link they suggested (the https://console.developers.google.com …. one).
- And enable the Sheets API.
-
Once it gets enabled, run the script again and you should see some data.
-
Then I tried using it with my own spreadsheet.
- First you need to get the spreadsheet ID of the spreadsheet you want to access via api.
- The spreadsheet ID can be taken from the spreadsheet URL itself.
For example: Here is a sample spreadsheet: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0 Here “1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms” is the ID of this spreadsheet.
- After I put the spreadsheet ID in the code and ran again it gave this error:
Traceback (most recent call last):
File "quickstart.py", line 15,
in if not creds or creds.invalid: File "/home/mandeep/envs/gapi/lib/python3.6/site-packages/googleapiclient/_helpers.py", line 130,
in positional_wrapper return wrapped(*args, **kwargs)
File "/home/mandeep/envs/gapi/lib/python3.6/site-packages/googleapiclient/http.py", line 842,
in execute
raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1Mhd1-CxxhnXv0VmxdDAWj4hSX1zhQAlVZuAfNXGnHak/values/Class%20Data%21A2%3AB?alt=json
returned "Unable to parse range: Class Data!A2:E">
- Strange! It worked for the Google’s sample spreadsheet but not for mine. Searched about this error with keywords: google sheets api “Unable to parse range”. Got the answer. It was about the way we give the range of columns.
- In Google’s example it was:
RANGE_NAME = 'Class Data!A2:E'But it has to be:RANGE_NAME = 'A2:E'It will get all rows in the column range from A to E. Here A2 specifies that we need the data starting from the 2nd row. It was done because it the first row contains the column heading names (like Name, Email, etc.) and not the actual data. Now we’ve got a working script that downloads the data from your spreadsheet. Here is the code: https://github.com/mandeeps708/api_playground/tree/master/GoogleSheets