1

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.

SantoshGupta7
  • 5,607
  • 14
  • 58
  • 116
  • Although I'm not sure whether I could correctly understand about your actual situation, when you want to edit Google Spreadsheet without logging in the Google for retrieving the authorization code, as one of several workarounds, how about using the service account? When the service account is used, the Spreadsheet can be edit without logging in. But if this was not the direction you want, I apologize. – Tanaike Jan 02 '20 at 23:20
  • I am very interested in this. I looked up what a service account is, but I'm not sure how to implement it with gsheets – SantoshGupta7 Jan 03 '20 at 08:35
  • Thank you for replying. I posted the sample script for using the service account as an answer. Could you please confirm it? If that was not the direction you want, I apologize. – Tanaike Jan 03 '20 at 23:21

2 Answers2

1

Unfortunately you can't what you want without authentication. Is a mandatory thing.

In the end if you take a look at Gspread you could see that is actually using the Google Sheets API. And through the API you need to authenticate even when accessing (like in your case) public data.

There are other ways to authenticate with the API but sincerely I don't know if they are supported by Gspread (I believe that they are not).

So you could use some workaround proposed in the question that you mentioned.

Raserhin
  • 2,516
  • 1
  • 10
  • 14
1
  • 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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • I am able to access a google sheet with a service account, but now I am getting 'Permission Denied' when trying to edit a google sheet, even when the service account email is the same email as my google drive/google sheets email. More details https://stackoverflow.com/questions/59707697/google-service-account-not-able-to-edit-update-a-google-sheet-using-gspread – SantoshGupta7 Jan 12 '20 at 20:12