Search code examples
pythongoogle-apigoogle-sheets-apigoogle-api-python-clientservice-accounts

Retrieving google sheet values with google sheet api on company restricted spreadsheet

I want to be able to read a spreadsheet, that has been shared with me, inside the company I work. I have tried adapting the script from this link. This is the code I use:

from apiclient import discovery
from oauth2client.service_account import ServiceAccountCredentials
import httplib2

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

SERVICE_ACCOUNT_FILE = "gsheetread-293005-d7e75122e4c7.json"

credentials = ServiceAccountCredentials.from_json_keyfile_name(
    SERVICE_ACCOUNT_FILE, scopes=scope)

# Use the create_delegated() method and authorize the delegated credentials
delegated_credentials = credentials.create_delegated('[email protected]')
delegated_http = delegated_credentials.authorize(httplib2.Http())

google_sheet = discovery.build('spreadsheet_id', 'v3', http=delegated_http)

I get an error:

oauth2client.client.HttpAccessTokenRefreshError: unauthorized_client: Client is unauthorized to retrieve access tokens using this method, or client not authorized for any of the scopes requested.

Solution

  • The error you're getting means you didn't add some or all of the specified scopes (https://www.googleapis.com/auth/spreadsheets, https://www.googleapis.com/auth/drive) when granting domain-wide authority to your service account. Please follow these steps:

    enter image description here

    In step 5, you have to add both https://www.googleapis.com/auth/spreadsheets, https://www.googleapis.com/auth/drive (actually, you only need to add one, if you're just accessing a spreadsheet, but you should remove the other one from your code).

    Additional notes:

    • The library you're using, oauth2client, is deprecated. Use google-auth instead, which doesn't rely on httplib2.
    • You're not using a valid service name (spreadsheet_id). If you want to use Sheets API, the service name should be sheets.
    • Sheets API V3 is deprecated and will be shut down in January 2021. Use V4 instead.
    • As I mentioned before, there's no need to use the drive scope if you're accessing a spreadsheet. spreadsheets is enough.

    Code snippet (using non-deprecated library):

    from googleapiclient.discovery import build
from google.oauth2 import service_account

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = "gsheetread-293005-d7e75122e4c7.json"

creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
delegated_credentials = creds.with_subject('[email protected]')
service = build('sheets', 'v4', credentials=delegated_credentials)
spreadsheet = service.spreadsheets().get(spreadsheetId="YOUR_SPREADSHEET_ID").execute()
print(spreadsheet)