burnash
Track API call usage in the spreadsheet object
Is your feature request related to a problem? Please describe. I hit quota limits and am not sure why.
Describe the solution you'd like
I would like each gspread operation to account for the number of API hits it makes as a property of the spreadsheet object (i.e. what you get from gspread.oauth().open()). With that I can better account for which operations are using up quota and try to optimize my code to be more API-efficient.
Describe alternatives you've considered This one is probably a separate issue, but it would be nice if gspread could implement an exponential backoff-and-retry when hitting quota limits so that applications, while they might take longer to run, won't hit quota limit exceptions.
Additional context None that I can think of.
Ahhh. More light is shed.
I'm coming to realize that assuming gwb = gspread.oauth().open(…) every time you reference gwb.worksheet(), as a reference to get some property such as gwb.worksheet('Sheet1').id that just the gwb.worksheet('Sheet1') is an API call.
Could some caching be built into gspread to avoid this "hidden" amping up of API calls?
I mean, in my code, I probably need to do something like:
Sheet1 = gwb.worksheet('Sheet1')
and then use Sheet1.id instead of calling gwb.worksheet('Sheet1').id, so that the many places where I reference gwb.worksheet('Sheet1') I just reference Sheet1 saving a pile of API hits. But can this sort of thing not be baked right into gspread?
But then I wonder, how will this kind of caching (whether done by my own code or in gspread) affect things like fetching the row_count when any number of operations after Sheet1 is the cached value of gwb.worksheet('Sheet1') could add or delete rows?
Yeah, that caching does screw things up like row_count. For example, if you add a new row with add_rows(1) you need to refresh your cached sheet object (i.e. Sheet1 here). This is something that would be much better handled in gspread itself IMHO. It should know when it needs to refresh that cached object and when it doesn't need to. Or it can even just effect the changes being made in the object locally without pulling new metadata from the Sheets API. I.e. decrement row_count when deleting rows, incrementing when add_row()sing, etc.
FWIW, I have discovered https://github.com/burnash/gspread/blob/27f4804accd517c4623b8b1378b08c9ef26902de/gspread/http_client.py#L516
😄
I have even cribbed that whole class into my own source and added a debug print when the backoff is happening and funny enough, I am not seeing any happen yet.
But I know I badly need to add a batch_delete_rows method as I do a lot of deletes, of ranges of rows even and batching them would save me a lot of write quota.
hi @brianjmurrell, thanks for the issue !
please see these issues for further reading if you are interested. I believe they are relevant:
- gspread's attempt to update internal properties with a guess when API calls are made (e.g., when rows are added) -> https://github.com/burnash/gspread/issues/881 and https://github.com/burnash/gspread/pull/1211
- more info on back-off client -> https://github.com/burnash/gspread/pull/1415
as for API usage, you must be using it quite a lot...! I think it's some 60 or 500 requests per minute you are allowed by Google. I hope the Back-off client helped.
