gspread “SpreadsheetNotFound” on certain workbooks

问题:

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.

回答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.

回答2:

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": "[e-mail]",

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.

回答3:

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).