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.

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.