9

I am writing a python(ver 3) script to access google doc using gspread. 

  1)  import gspread
  2)  from oauth2client.service_account import ServiceAccountCredentials
  3)  scope = ['https://spreadsheets.google.com/feeds']
  4)  credentials = ServiceAccountCredentials.from_json_keyfile_name(r'/path/to/jason/file/xxxxxx.json',scope)
  5)  gc = gspread.authorize(credentials)
  6)  wks = gc.open("test").sheet1

test is a google sheet which seems to be opened and read fine but if I try to read from a Office excel file it gives me error.here is what they look: enter image description here

The folder which test and mtg are under is shared with the email I got in json file.Also both files were shared with that email.

Tried:

wks = gc.open("mtg.xls").sheet1

and

wks = gc.open("mtg.xls").<NameOfFirstSheet>

and 

wks = gc.open("mtg").<NameOfFirstSheet>

error:

/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/gspread/client.py", line 152, in open raise SpreadsheetNotFound gspread.exceptions.SpreadsheetNotFound

TestIsFun
  • 145
  • 2
  • 2
  • 9

3 Answers3

19

I was getting this as I had missed step 4 here

Go to your spreadsheet and share it with a client_email from the step above. Otherwise you’ll get a SpreadsheetNotFound exception when trying to access this spreadsheet with gspread.

ishandutta2007
  • 16,676
  • 16
  • 93
  • 129
5

There is no .xls to be added at the end of the file name, the data is saved in a different format (and can later be exported as .xls).

Try to break your code into: 

ss = open("MTG_Collection_5_14_16") 
ws = ss.worksheet("<NameOfFirstSheet>")

and post the error message if any.

Spreadsheet instances have an attribute sheet1 because it is the default name for the first worksheet. ss.sheet1 actually returns the worksheet with index 0, no matter what its name is.

If you want to access another worksheet, you need to use one of ss.worsheet("<title>") or ss.get_worksheet(<index>). ss.<NameOfFirstSheet> will not work.

Jacques Gaudin
  • 15,779
  • 10
  • 54
  • 75
  • thanks ...Lines 1 to 5 then added these : ss=gc.open("mgt") ws=ss.worksheet(<"nameOfFirstSheet">) values = ws.row_values(1) print(values) ---> failed with error: ss=gc.open("mgt") File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/gspread/client.py", line 152, in open raise SpreadsheetNotFound gspread.exceptions.SpreadsheetNotFound – TestIsFun Jun 05 '16 at 22:38
  • same code ran for test(google sheet doc) worked fine. – TestIsFun Jun 05 '16 at 22:42
  • 1
    If you want to edit an `xls` file with gspread, you need to convert it to the google doc format first. If you try to open `mgt.xls` manually from the spreadsheet app interface, you will be asked if you want to open it as a view or create a copy with the same file name. Do the second and share the copy with the email address in the credential json file and then it should work. – Jacques Gaudin Jun 05 '16 at 23:54
  • Looks like I was not fully understood my need till now. I need to "access" to excel file "located under google drive", then open and read from it using xlrd library. gspread is not a way to achieve this correct? – TestIsFun Jun 06 '16 at 15:29
  • You are correct. The way `gspread` is communicating with the spreadsheet is by forming URLs to request specific cells. This wouldn't work with an xls document on your drive. It should be quite easy to download a copy through the drive API though. – Jacques Gaudin Jun 06 '16 at 16:08
5

A Very Important Note from here : [https://docs.gspread.org/en/latest/oauth2.html#enable-api-access]

Very important! Go to your spreadsheet and share it with a client_email from the step above. Just like you do with any other Google account.
If you don’t do this, you’ll get a gspread.exceptions.SpreadsheetNotFound exception when trying to access this spreadsheet from your application or a script.

This means that you need to share the spreadsheet (by opening the spreadsheet and then clicking on the share button and then just paste the email id) with the "client_email" mail id which is provided in the JSON file that you got from google developer console

eg:

{
  "type": "service_account",
  "project_id": "speeqedy-octane-317516",
  "private_key_id": "8ct0ad00e3e59d49a576012cb515ea89e4e49cc4",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCOlC07MwSYGr54\nMx1+XAXC2k8HrBA/W6I3PZxnG5CUTPL8rJy8Ne2kuXW2k3qN/A0CKf2yUKc0DbIP\nn+LsCDYt6ikTOpb5VeToRMFPlg3dwcghsVFlO2RKVZc1NiWYDiUu5jqjy/WOpqNH\nMZbPDJchhApiyEb+brSpgag0Smpn8oWNBFNUqyN7n+dQMDxlwe9CniM6p19rhwyX\nPPwYcONb/FQffIRrjUlSbxAV+uTwoN/BeTN/jDvjDSdbF2jfBzis/sBejZAt3wsA\nzM2KcNu/bGFck+Slg0rXbHf8qPW4C+kuGe4x5KI4y/SG77TuuflNfeIIadwVblAH\nruhlzGRvAgMBAAECggEAQ7+rIncilrgWi30WZaWKY0Viyi17Zu++y+rtt5zEfYN2\n7pOCwngJnP03LvMzB+ads8qKL0HdwYFEe+IobKmWPQkITYfIl1+2LpwFEzElE5tx\n4nikSasIA75Z5EH/aRpHltp1QMG4w/jwmvK4S1gbNCTO3pN2EI5AME9YwsgdeZhx\nFPbTlZ7CstqrvJwrejInuX7b7+YLCRUiLz09n5xDVLCpgzqcUxRUIlX6qnQ5O5fQ\nUmVI4qeeIMb3TE2X6JN2Hon5RCqA1j6kNj3auIgDgrqCarcjX4MU9hDlBfj+MRKM\nZz19lNCYBL7D2p9o+QJMzBakmUawdEdm0PNnzouxGQKBgQDIHE6H3u84YzqWsyAm\nJoON9jWq3Z0Dp9Woxd1c12sJGaqpCjvejAHsUklRDLSnj/9TxySxCBtHzOkpWZ27\nC1+ZQVkHgVkJaBRJ2dxwsNODDyvn12gN2OE00e0IATx6hIuMrZBiisrOldlzkfbe\nbrzdpcverz/j8O+DU+xlpkd+bQKBgQC2ZmkO+P2fofc4AbnLNKqr8e9NVqMAHovi\nj6dqE1/YQwarXyORHlpqZy0cqG1yXHiTXdQCkRf2oaLtUMoNARywCNNsJcmTUccm\nbG1/g3KdcsTu5r7/IgJjJ6Oytd5WE3nOOG0pN0PxTVjhC5GIMdDqT7bO7PxYxt8J\ny5stQwE0ywKBgEjUZJB2xsHS+4PVdaHs6nxEW4vwVDD8aHqgaMxdaotJWB9lphx9\nAEYuPJjO8wD5G4g2NlhoBuW+4i02fH2SyhxQ+EIPBYSy6I9oRx6MfK56WxRqBWjk\nwjIjJQ2Axa4hh1iP4ri6os1FJHs3aI3T59ETgnttdOdUzRcS+DqWpfaRAoGADnjx\nvQ5kkVX4l6kLZOQ45ou7wRcut8O0tbVQQ0pmQHKRBOf3mnweEHM/URbMGFbd+lNn\njwyUKi2JD+tpHL8J163bmKkm5VmHw7S2u3dikLuahJoqbXP1XIi4pGVpzsqcz5AK\nmUqZSXwpoTGZyoSpXu4UQzbqjuC4sb5GAcFfRyECgYEAqPEqRTfjU48q8X5ZNx/D\nGze5ZU8PpkOr9viffcptt7dF9f/SMyqY6a8oDzm70tN8IHsctoadu/9ZqUAC56XO\nZtc96uppNBHM/ZZr2eXR6iU8fXTkHgA009xYeOIciS/exvlntTn7qeBiLb5DyA2s\nHB/iyGpo6KQBl/rRtcNxQXw=\n-----END PRIVATE KEY-----\n",
  "client_email": "cybor@speedy-octane-313216.iam.gserviceaccount.com",
  "client_id": "102481923285078876543",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/cybor%40speedy-octane-317516.iam.gserviceaccount.com"
}

if this is your JSON file then you need to copy the client_email that is cybor@speedy-octane-313216.iam.gserviceaccount.com and then share your sheets to this email id.

CreatorGhost
  • 327
  • 4
  • 7