Search code examples
google-sheetsgoogle-drive-apigoogle-oauthgspread

How to make a a google sheets spreadsheet available to gspread

enter image description here

I'm working through the documentation to gspread (http://gspread.readthedocs.io/en/latest/oauth2.html ), and have been able to get the introductory code block working:

import gspread
gc = gspread.authorize(credentials)

However when I run the following script :

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('My Project-******97.json', scope)
gc = gspread.authorize(credentials)
wks = gc.open("rules_test_1.csv").sheet1

I get:

gspread.exceptions.SpreadsheetNotFound

How can I get access to this sheet?

Solution

  • OK, you're making a common mistake. If you search for "service account [google-drive-sdk]" in StackOverflow, you'll find many people asking the same question.

    Basically a Service Account IS NOT your Google account, which is why your file isn't found. The authors of gspread should have explained that in their example code. You have two options:-

    1. Share your spreadsheet, or the folder it's in, with the Service Account user. This is the easiest approach since all you need to do is make the share from GDrive and then your existing code will work. The downside is it can be clunky to keep sharing files. My suggestion would be to make a dedicated folder called say "shared with my service account" and make all files children of that folder. Remember that files can have multiple parents, so you can still preserve your folder structure. See the answer to How do I search sub-folders and sub-sub-folders in Google Drive? for a refresher on how Drive folders work.

    2. Replace the Service Account auth with Standard Account auth. This requires some minor changes to your code. The result will be that your app is talking directly to your Drive Account, without having a Service Account acting as a proxy. Thus it's more elegant provided the security implications are acceptable. You will be using a stored Refresh Token. Some information on how to do this is at How do I authorise an app (web or installed) without user intervention? (canonical ?)

    Neither way is right or wrong. It depends on your specific requirements.