gspread "SpreadsheetNotFound" on certain workbooks

Question

I have a google drive folder with hundreds of workbooks. I want to cycle through the list and update data. For some reason, gspread can only open certain workbooks but not others. I only recently had this problem.

It's not an access issue because everything is in the same folder.

I get raise SpreadsheetNotFound when I open_by_key(key). But then when I take the key and paste it into an URL, the sheet opens. Which means it's not the key.

What's going on here? I'm surprised other people are not encountering this error.

Have I hit my limit on the number of Google sheets I can have? I have about 2 thousand.

Update: I find that if I go into the workbook and poke around, the sheet is then recognized??!! What does this mean? It doesn't recognize the sheet if the sheet isn't recently active???

Also if I try using Google App Script SpreadsheetApp.openById, the key is recognized! So the sheet is there, I just can't open it with gspread. I have use Google script to write something to the sheet first before it is recognized by gspread.

I'm able to open the sheet using pygsheets but since it is new and so buggy, i can't use it. It looks like a APIv4 issue? Some sheets can't be opened with APIv3?

update: here is another observation. Once you open the workbook with APIv4, you can no longer open it with V3.

Togo

maybe individual workbooks in the folder are not shared with the google service account client? – Beatriz

@ Haleemur Ali. No the whole folder is shared with the client – Togo

Just an idea: have you tried open_by_url instead of open_by_key? – Transferor

You can also try to reproduce the problem on google-api-python-client (it's a v4 api client that seem to be recommended instead of gspread). – Transferor

Good suggestion, but open_by_url also gives me the same error. I pasted the exact url of the browser into the code. – Togo

Facing the same issue, please help! #54794152 – Morin

Answer 1

This sounds like you are hitting a limitation in gspread.

Even if you open a spreadsheet by key, gspread still first downloads a feed of all explicitely shared sheets using https://spreadsheets.google.com/feeds/spreadsheets/private/full and checks if the key you have provided is among them.

The problem is that this feed only lists 500 files. If your key is not among these 500, then you'll get SpreadsheetNotFound even if the spreadsheet is accessible.

I'd guess that the feed is ordered by most recently used, that explains why editing it makes it visible to gspread again.

The relevant issue is here.

Deidradeidre

Answer 2

I've run into this issue repeatedly. The only consistent fix I've found is to "re-share" the file with the api user. It already lists the api user as shared (since it's in the same shared folder as everything else), but after "re-sharing" I can connect with gspread no problem.

Based on this I believe it may actually be a permissions issue (Google failing to register the correct permission for the API user when accessing it through APIv3).

Cabrales

Answer 3

My guess is that you may be hitting the Google Drive API limit of "Queries per 100 seconds per user", which is 1000 and cannot be changed to a higher value.

Solution 1:

You can try to apply for a higher quota by going to you project page:
1 - DASHBOARD
2 - QUOTAS
3 - Click on the pencil after "Queries per 100 seconds per user"
4 - Then click "Apply for higher quota"
5 - You'll end-up on https://support.google.com/code/contact/drive_quota, but if you follow steps above, the form will have the project and user details already filled.

---

Solution 2:

I had the same issue with just a couple of spreadsheets on my account, the problem was solved by:

1 - Opening the json key file (projectname-ca997255dada.json)
2 - Find the value of client_email , i.e.:

"client_email": "[email protected]",

3 - Share your sheet(s) with that email

Note: You can try to change just one spreadsheet that you cannot open and check if the above solve the problem.

Attitudinarian

I don't think that is right, because I would wake up first thing in the morning and try to run a simple code to open just one sheet. Basically the first request of the day... and that still doesn't work. – Togo

Have you considered firewal or AV interference ? – Attitudinarian

Yeah, that's not the issue. – Togo

I don't think it's even possible to hit that API rate (at least with gspread). The API is too slow that you could be opening sheets in 0.1 sec. The answer by dorian makes the most sense to me, especially in the light of opening the file manually suddenly causing it to be found. – Attwood