Search code examples
pythongoogle-sheets-apigspread

Q about scope value when using gspread API to read google sheet with Python

I am new to google apis. I am looking to read a google spreadsheet via a python program and following instructions found here:

https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html

I have a google sheet called Legislators 2017 that I am using for this.

The following is the code to print out some content from the sheet.

import gspread
from oauth2client.service_account import ServiceAccountCredentials


scope = ['https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('google_drive_oct_6_2020.json', scope)
client = gspread.authorize(creds)

sheet = client.open("Legislators 2017").sheet1

list_of_hashes = sheet.get_all_records()
print(list_of_hashes)

The above works.

I am inclined to use the smallest scope possible in general, and would like to use

https://www.googleapis.com/auth/spreadsheets or 
https://www.googleapis.com/auth/spreadsheets.readonly

but they don't work and I get an exception with the following message:

Insufficient Permission: Request had insufficient authentication scopes.

What am I missing ?

Solution

  • When client.open("Legislators 2017") is used, the method of "Files: list" in Drive API is used. Ref By this, the scope of Drive API is required to be used. In your script, https://www.googleapis.com/auth/drive or https://www.googleapis.com/auth/drive.readonly are required.

    When you don't want to use the scopes of Drive API and want to use the smallest scope possible in general, how about the following modification?

    From:

    scope = ['https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('google_drive_oct_6_2020.json', scope)
client = gspread.authorize(creds)

sheet = client.open("Legislators 2017").sheet1

    To:

    scope = ['https://www.googleapis.com/auth/spreadsheets.readonly']
creds = ServiceAccountCredentials.from_json_keyfile_name('google_drive_oct_6_2020.json', scope)
client = gspread.authorize(creds)

sheet = client.open_by_key("###").sheet1
    • ### is the Spreadsheet ID of Legislators 2017.
    • In this modification, the scope of https://www.googleapis.com/auth/spreadsheets.readonly can be used. Also, https://www.googleapis.com/auth/spreadsheets can be used.

    References: