how to resolve google sheets API error with python

Question

I am trying to read / write to google sheets using python.

I run this boilerplate code, but get the following error:

APIError: {'code': 403, 'message': 'Request had insufficient authentication scopes.', 'errors': [{'message': 'Insufficient Permission', 'domain': 'global', 'reason': 'insufficientPermissions'}], 'status': 'PERMISSION_DENIED', 'details': [{'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'ACCESS_TOKEN_SCOPE_INSUFFICIENT', 'domain': 'googleapis.com', 'metadata': {'method': 'google.apps.drive.v3.DriveFiles.List', 'service': 'drive.googleapis.com'}}]}

what i have done:

I have followed the instructions and setup the google-API key.
i have downloaded the json key (named it google_keys.json).
i created a new google sheet called test sheet for testing.

import gspread
from google.oauth2.service_account import Credentials


# Use the credentials to create a client to interact with the Google Drive API
scopes = ['https://www.googleapis.com/auth/spreadsheets']
creds = Credentials.from_service_account_file('google_keys.json', scopes=scopes)

client = gspread.authorize(creds)

# Open a sheet from a spreadsheet in Google Sheets
sheet = client.open("test sheet").worksheet("Sheet1")

# Read a range of cells
cell_list = sheet.range('A1:C7')

# Print the values in the range of cells
for cell in cell_list:
    print(cell.value)

# Write values to the sheet
sheet.update_acell('B2', "I am writing to B2")

It appears to be an authentication problem. So my question is, what else to i need to do to get this test code working ?

Daniel · Accepted Answer · 2023-01-16 22:32:59Z

1

The message says that the request has insufficient scopes, and points out that the method google.apps.drive.v3.DriveFiles.List is the one that triggered the error. This means that you need to add a Drive scope so the script can read your files, probably to search for a spreadsheet with the specified filename.

From the gspread documentation, these are the scopes that you need to authenticate with a service account:

scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

Note that you also need to share your spreadsheet with the service account that you got the google_keys.json file from. It ends in ...gserviceaccount.com. Make sure that you generated the right keys.

Reference:

answered Jan 16, 2023 at 22:32

Daniel

3,7952 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

darren Over a year ago

works, this is an excellent, well explained, answer. Thanks.

darren Over a year ago

i also had to enable the Google Drive API.

Collectives™ on Stack Overflow

how to resolve google sheets API error with python

1 Answer 1

Reference:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related