1

I am running a single-threaded script which consumes data from the G Suite Sheets API.

After some time, the following 429 HTTP error pops up in the logs:

"Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer...

And my quota limits have been set by default as follows (Ilimitado means unlimited):

enter image description here

My client last log lines before the error:

[2020-05-14 02:21:37,114: INFO/ForkPoolWorker-1] URL being requested: GET https://sheets.googleapis.com/v4/spreadsheets/<SHEET_ID>?alt=json
[2020-05-14 02:21:37,517: WARNING/ForkPoolWorker-1] Sleeping 1.19 seconds before retry 1 of 5 for request: GET https://sheets.googleapis.com/v4/spreadsheets/<SHEET_ID>?alt=json, after 429
[2020-05-14 02:21:39,057: WARNING/ForkPoolWorker-1] Sleeping 2.68 seconds before retry 2 of 5 for request: GET https://sheets.googleapis.com/v4/spreadsheets/<SHEET_ID>?alt=json, after 429
[2020-05-14 02:21:42,089: WARNING/ForkPoolWorker-1] Sleeping 3.16 seconds before retry 3 of 5 for request: GET https://sheets.googleapis.com/v4/spreadsheets/<SHEET_ID>?alt=json, after 429
[2020-05-14 02:21:45,606: WARNING/ForkPoolWorker-1] Sleeping 0.77 seconds before retry 4 of 5 for request: GET https://sheets.googleapis.com/v4/spreadsheets/<SHEET_ID>?alt=json, after 429
[2020-05-14 02:21:46,722: WARNING/ForkPoolWorker-1] Sleeping 20.65 seconds before retry 5 of 5 for request: GET https://sheets.googleapis.com/v4/spreadsheets/<SHEET_ID>?alt=json, after 429
[2020-05-14 02:22:07,735: WARNING/ForkPoolWorker-1] sheets#sheet: HTTP error (code 429): Quota exceeded for quota group...

I can identify here some kind of exponential backoff retry algorithm in the googleapiclient library, but what I would like know is (specially after the 20.65 seconds pause) why the server is still unable to respond with a 2XX HTTP response.

  • Does it require to wait for the 100 seconds interval to end, once the first 100 requests have been received before consuming the next 100?
  • If so, how could I circumvect this without modifying the quota?

Thank you for your time.

Atenea_v10
  • 87
  • 1
  • 8
  • 1
    I think in this case if you're hitting the quota limits for Sheets API then you need to wrap the API calls themselves within an exponential backoff algorithm client side. Google doesn't seem to have published whether the 100 second interval is 'calls within the last 100 seconds' or if it's segregated 100 second segments. If you're making more than 100 requests as the same user in 100 seconds then why don't you do some further processing between the calls? – Rafa Guillermo May 15 '20 at 10:10
  • 1
    @RafaGuillermo, some Google API query methods include a `num_retries` parameter (which uses the backoff algorithm) so I can simply increase this value. My assumption is even with 5 retries (as in the example above), the 100 requests are consumed very fast in the 100 seconds interval and thus the retries terminate before the interval finishes. I'm increasing the `num_retries` parameter to a higher value in order to overcome the interval end. Your proposal of running other queries (to different endpoints) in the meantime is very useful. Thanks. – Atenea_v10 May 15 '20 at 10:29
  • 1
    Have you looked at the Sheets API read request quota graph on the GCP project? It gives a rundown of how fast the requests are made which may help you to diagnose the rate limits – Rafa Guillermo May 15 '20 at 11:26
  • @RafaGuillermo I am aware of that. Unfortunately that lies on the customer side which, for the moment, I am not authorized to look into. My objective is to make my scripts work autonomously and "blindfolded" independently of the quota values in the customer side. Thanks again. – Atenea_v10 May 15 '20 at 12:20

0 Answers0