I can't access the blank google spreadsheet i created using python

user_16958259 0
Viewed 61

So i have been trying to create a new blank google spreadsheet using python. I am using a python script i found online (also added a few modifications of my own) and i just can't get it to work the way i want. I've never actually used python before, neither google spreadsheets so i am a little confused!! The current issue is that whenever i run the code it seems to be working, but when i copy/paste the URL of the newly generated google spreadsheet, i don't even have permission to view it. Here is my code....Thank you in advance!!

"""
BEFORE RUNNING:
---------------
1. If not already done, enable the Google Sheets API
   and check the quota for your project at
   https://console.developers.google.com/apis/api/sheets
2. Install the Python client library for Google APIs by running
   `pip install --upgrade google-api-python-client`
"""
from pprint import pprint
from googleapiclient import discovery
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from google.oauth2 import service_account

# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
#     'https://www.googleapis.com/auth/drive'
#     'https://www.googleapis.com/auth/drive.file'
#     'https://www.googleapis.com/auth/spreadsheets'
SERVICE_ACCOUNT_FILE = 'client_secret.json'
f = open('client_secret.json','r')
print(f.readline())
f.close()

SCOPES = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"]

credentials = None
credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = discovery.build('sheets', 'v4', credentials=credentials)

spreadsheet_body = {
    # TODO: Add desired entries to the request body.
}


request = service.spreadsheets().create(body=spreadsheet_body)
response = request.execute()

# TODO: Change code below to process the `response` dict:
pprint(response)
google-sheets python
1 Answers

The issue is that the sheet is created with the serviceaccount as the owner, not under your personal Gmail account.

There's 2 options:

  1. The bad way would be to give your personal account access to the generated GSheet. Issue with this is that the serviceaccount will still be the owner. I'm not going to tell you how to do this as this is absolutely the wrong way.
  2. The right way would be to use proper credentials when creating the API client. This is explained in detail in this article.

Pay special attention to the piece of code that creates the credentials object.

    creds = None
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.json', 'w') as token:
            token.write(creds.to_json())

    service = build('sheets', 'v4', credentials=creds)

As you can see this code doesn't directly create a credentials object from the serviceacocunt. Instead it asks for permission to use your personal Gmail account to call the API.

Note that if you have a GSuite/Workspace account, you can use impersonation instead. This is actually the preferred way, but only works with said GSuite/Workspace accounts.

user_92672960
Related