Google Sheets Resource Exhausted Error

[alexandrammuir]

Main Problem When submitting many ROIs / sites in parallel or succession, we commonly get the following error:

Looking further into the error at google.rpc.QuotaFailure we see the following:

From what I can tell, Google has implemented a restriction of 500 reads/writes per account per 100 seconds, and 100 read/writes per key per 100 seconds. When we try to access the same google sheet many times quickly, we quickly fill our quota.

We would love to apply for an unlimited API, however, I am not sure how to do so. Additionally, we need to ensure that the API is accessible from many different users. I am not sure what user it currently pings Google through. I will use this thread to update how I am trying to solve the problem.

[alexandrammuir]

As an update, here are some resources that explain a bit of this issue / how we can try and get around it.

https://github.com/r-lib/gargle/issues/146 https://github.com/r-lib/gargle/issues/159 https://gargle.r-lib.org/articles/get-api-credentials.html#oauth-client-id-and-secret-1

It looks like when you use the googlesheets4 package everyone is using the same authorization key....meaning that you are sharing resources with the rest of the R users in the world....

As such, we should try and get our own OAuth app (client ID and secret).

[Kevin-michael-angel-Low]

Here are some possible resources we can use to debug this: Gs4 edit/view authorization configurations Authorize gs4 to view/manage google sheets Github Instructions on how to use gs4? May or may not be useful

[Kevin-michael-angel-Low]

Instead of using gs4's deauth command, use gs4_auth() to configure a json key from a Service Account from Google Cloud's Console. This bypasses the need to use a de-authorized state to access the sheet. However, this still causes a resource exhausted error. More info about deauth here