pygsheets
pygsheets copied to clipboard
nithinmurali
solutions to USER-100s
I have 2 approx. 525K cell DFs I'm writing to 2 different sheets. The 2 DFs are written one after the other. I was getting the USER-100s quota limit error. I manually reviewed the log to count up the requests and inserted a delay (40seconds) between the writes which eliminated the USER-100s error.
Is there a better way to calculate the delay required between back to back large DF writes? Is there any way to retrieve the number of requests required for a given DF write?
TIA
You can make 100 requests per 100 seconds.
Keep track of the time and every 100 seconds reset. If during this time you make 100 requests put the application to sleep until the 100 seconds pass. I am working on implementing this as baseline behaviour for pygsheets.
As an alternative:
Just keep sending requests and put your code into a try-block. The exception to catch is googleapiclient.exceptions.HttpError with response code 429.
try:
request()
except HttpError as error:
if error.resp == 429:
time.sleep(100) # or if you keep track of time since the first request only sleep as long.
request()
else:
raise
Can the number of requests be estimated based on the number of cells associated with the df to be written? Btw, your implementation will be a nice addition to pygsheets.
Basically I have several asynchronous threads running and each writes +/- ~500K cell sized DFs to google sheets at different rates. Race conditions occur and I get a handful of these "WriteGroup' and limit 'USER-100s" errors.
Perhaps a dedicated thread for writing to google sheets with a queue for job requests might be required to address this situation. But still would like to understand how to determine the number of requests required based on either the number of bytes or number of cells associated with the data frame...or maybe the block of code you offer above?
Any new update or comments on this issue?
I'm getting the USER-100s exception when trying to write a very large DF to Google Sheets. Is there an option available for the pygsheets user to control the delay between adjacent write requests?
User could keep the delay to default for most writes but set it to 1 sec for very large DF write. There could be situations where the user may want fast back to back writes for smaller DFs while for a very large DF delay could be restricted to 1 sec between writes.
Thoughts or comments?
Btw, I just tested writing the same (very large) DF using most recent version of pygsheets v2.0.1 and no USER-100 exception observed.
In 2.x a check is added to retry after a sleep if this error is encountered.
If you hit a Google Sheets limit, I'd suggest a more reactive than proactive approach.
Catch the HTTP error, create a new client instance with a new service account, and pass all additional calls to the new client. You can even create multiple projects with the same permissions to have nearly no call limits.
100 requests per service account, 500 requests per project. Handoff requests to the next service account when the limit is hit until the 5th account, then use an account from a different project. You could even stay within the same project assuming that you aren't consuming so much that the first service account wouldn't have its quota refreshed by the time the fifth ran out.
