How to increase Google Sheets v4 API quota limitations

google-sheets-api
Peter Jansen picture Peter Jansen · Jul 20, 2017 · Viewed 14.9k times · Source

The new Google Sheets API v4 currently has an unlimited read/write quota per day (which is fantastic), but restricted to 500 reads/writes per account per 100 seconds, and 100 read/writes per key per 100 seconds (or, I have found, multiple keys coming from the same IP). This is probably plenty for most use cases, but I have an edge case that requires bringing a frequently-updated Google Sheet with 70 tabs down to a node.js server that distributes these to user's clients every ~30-60 seconds or so (users are data annotators who are student research assistants). This wasn't so bad early in the project when there were only 20-30 tabs, but now that the data is large the server is blowing through the 100 quota and returning errors every 10-15 minutes.

The problem is such that:

  1. Frequent data updates: Only data on 1-5 of the 70 tabs is likely to be updated on any given minute, but which tabs have new data is random (so I am pulling down the whole sheet of 70 = 70 reads).
  2. Update interval: The need for updates happens randomly at about 30 second to 5-minute intervals (so some within the quota, some about 3-5x the quota).
  3. Throttling: I have tried throttling the update to be within the 100 calls/100 seconds (my previous solution), but this introduces large usability issues, significantly decreasing usability/productivity/work quality.
  4. Quota increase: The sheets API does not currently appear to include a way to pay to increase the quota. It does allow filling out a form to request an increase in the quota, but I'm not sure what the mean response time is on this (my request is only a few days old).
  5. Multiple service accounts: I have tried using multiple service accounts to get the full 500 requests/100 seconds quota (rather than the per-user quota), since this is a server, but Google Sheets looks to rate-limit to 100 requests/100 seconds from a given IP
  6. Alternatives: I have considered that this project may have just grown beyond the size that Sheets is easily able to handle, but there do not appear to be any good, usable, self-hosted, collaborative spreadsheets with easy-to-interface-to APIs out there.

Are there settings/methods suggested to achieve the full 500 calls/100 seconds for a server?

Answer

Alex picture Alex · Jan 11, 2018

You can request quota update in Google Cloud Platform and it will be increased to 2500 per account an 500 per user. (about your #4) enter image description here

Related questions

How can I access Google Sheet spreadsheets only with Javascript?

I want to access Google Spreadsheets using JavaScript only (no .NET, C#, Java, etc.) I came here and was shocked to know that there is NO API for JavaScript to access Google Sheets. Please tell me how to access (CREATE/…

Read More
Accessing Google Spreadsheets with C# using Google Data API

I'm having some information in Google Spreadsheets as a single sheet. Is there any way by which I can read this information from .NET by providing the google credentials and spreadsheet address. Is it possible using Google Data APIs. Ultimately …

Read More
How do I access (read, write) Google Sheets spreadsheets with Python?

I am wondering if you can point me to an example of reading/writing to/from a google doc/spreadsheet using python. I did look at google docs API here https://developers.google.com/google-apps/spreadsheets/ but not sure if …

Read More