Search code examples
pythongoogle-sheetsgspread

In python, is it possible to edit a publicy shared google sheet, without an auth code?

I have a google sheet which I set so that anyone can edit.

I noticed that in the gspread package, you can get a spreadsheet from the link alone. Example: 

sht1 = gc.open_by_key('1wj9L7Hn779GKP0s4MblkU1wHcqaVcG_E2YKAo1vdof0')

Since anyone can edit, providing an auth code for access is not necessary. However, it seems that you always need to initialize it using an auth code.

Is there a way to edit a google sheet without any auth code?

This may get confused as a duplicate of this post Using gspread to read from a Google Drive Spreadsheet without logging in however, the solutions are to either have a client provide their auth code, or download the file directly, but no option to edit the file.

Solution
    • You want to edit Google Spreadsheet without logging in the Google for retrieving the authorization code.
    • You want to achieve this using gspread with python.
    • You have already been able to get and put values for Google Spreadsheet with Sheets API.

    If my understanding is correct, how about this workaround? Please think of this as just one of several workarounds.

    Workaround:

    In this workaround, the service account is used. When the service account is used, you can get and put values for Google Spreadsheet without logging in Google account using the gspread.

    In order to access to Google Spreadsheet with the service account, please do the following flow.

    1. Create the service account and download the JSON file for using the service account.
    2. Share the email address of the service account to the Google Spreadsheet you want to use.
      • You can see the email address at the downloaded JSON file.

    Sample script:

    The sample script for using the script of sht1 = gc.open_by_key('1wj9L7Hn779GKP0s4MblkU1wHcqaVcG_E2YKAo1vdof0') with the service account is as follows.

    import gspread
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://www.googleapis.com/auth/spreadsheets']
credentials = ServiceAccountCredentials.from_json_keyfile_name('### downloaded JSON file ###', scope)
gc = gspread.authorize(credentials)

sht1 = gc.open_by_key('1wj9L7Hn779GKP0s4MblkU1wHcqaVcG_E2YKAo1vdof0')
    • Please set ### downloaded JSON file ###.

    References: