gspread.exceptions.SpreadsheetNotFound

Question

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:

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

Pelting

Answer 1

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.

Arbuckle

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 – Pelting

same code ran for test(google sheet doc) worked fine. – Pelting

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. – Arbuckle

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? – Pelting

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. – Arbuckle

Answer 2

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.

Sublieutenant

Thanks, this helped me with my problem :) – Caphaitien

Answer 3

A Very Important Note from here: gspread docs

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": "[email protected]",
  "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 [email protected] and then share your sheets to this email id.

Butterworth

Thank you , this one is worked for me. – Sabadell