1

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?

pinoyyid
  • 21,499
  • 14
  • 64
  • 115
user1592380
  • 34,265
  • 92
  • 284
  • 515
  • Can you explain what you mean by "I have no sheets in Google drive"? Sheets is the application and Drive is where Sheets saves its sheets. If you followed the steps in the link you posted, you'll end up authing to a service account. Make sure you understand that a service account is not your own account. – pinoyyid Feb 15 '17 at 01:54
  • I've made some edits to the question.. – user1592380 Feb 15 '17 at 18:22

1 Answers1

3

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.

Community
  • 1
  • 1
pinoyyid
  • 21,499
  • 14
  • 64
  • 115
  • Thank you, that helps a lot. – user1592380 Feb 15 '17 at 22:18
  • I have a follow up question for clarification. Ideally I would like to use the second method but have been slowed down by by understanding and trying to code the oauth2 auth step. I'm trying to build an app that can interact with google sheets. I think what http://stackoverflow.com/questions/19766912/how-do-i-authorise-an-app-web-or-installed-without-user-intervention-canonic is saying is that if you go through the steps and get a long lived token , your app can then use this to log in without human help. Is this correct? – user1592380 Feb 15 '17 at 22:31
  • yep you've got it. All of the examples show how to code auth for multiple users against their own accounts. In your case you want a single user (you) against a single account (yours), so the steps you've linked to will bypass all of that, and leave you with a long lived token which you can use in the same way that you would once have used an embedded username/password. – pinoyyid Feb 15 '17 at 22:35
  • Once again thank you. After reading your comment I came across https://github.com/nithinmurali/pygsheets which looks very promising. I was able to get it working partially in about 30 minutes. It uses the sheets api v4. – user1592380 Feb 16 '17 at 13:47
  • Cool. fwiw, my personal preference is to avoid libraries for the Google APIs. The APIs are well formed REST/JSON APIs and are quite easy to work with directly. – pinoyyid Feb 16 '17 at 14:00