Check API status / Automated retry logic

[Romans96]

Hello! Is it possible to check the API status? How?

For example how many request are made, if i have other available requests, the rate limit per hour, etc?

Thank you!

[Romans96]

SO nobody knows? When it ends the usage limit it stops and the app crashes without no errors no returns and i cannot find any way to handle the "Usgae limit reach"

[theoephraim]

It should be throwing an error of some kind - so either you need to wrap your calls in try catches, or have a global uncaught error handler registered.

I am not aware of any way to check it programatically, but we could potentially build in handling that would retry the request after some delay.

[theoephraim]

I'll take a look at getting something like this merged in soon.

[agladysh]

It would be very nice to have the retry logic. I have a large spreadsheet with many worksheets, and cannot read it without hitting a rate limit.

Here is my workaround, in case it helps someone while there is no native support for rate limits in the module:

      //  Very rough ad-hoc implementation of https://developers.google.com/sheets/api/limits
      const maxBackoff = 32000;
      const maxRetries = 16;
      let retryNumber = 0;

      async function tryDownloadAsCSV(): Promise<ArrayBuffer> {
        return await sheet.downloadAsCSV().catch(async (e: AxiosError) => {
          if (e.response?.status !== 429) {
            throw e;
          }

          if (++retryNumber > maxRetries) {
            throw e;
          }

          const delay = Math.ceil(Math.min(maxBackoff, 100 + Math.pow(2, retryNumber) * 100) + Math.random() * 100);

          console.log(`[${retryNumber} / ${maxRetries}] got 429 from Google Sheets, waiting for ${delay} ms`);

          return new Promise(resolve => setTimeout(() => {
            resolve(tryDownloadAsCSV());
          }, delay));
        });
      }

      const buffer = await tryDownloadAsCSV();

See also: #326 #525

[charlesgardyn]

I'm using this https://github.com/theoephraim/node-google-spreadsheet/pull/525#issuecomment-1861766946 as a workaround while the PR is not merged