The new Google Sheets API v4 currently has an unlimited read / write quota per day (which is fantastic), but is limited to 500 reads / writes per account in 100 seconds and 100 read / write operations per key in 100 seconds (or, I found several keys coming from the same IP address). This is probably enough for most use cases, but I have an extreme case that requires bringing the updated Google sheet with 70 tabs to the node.js server, which distributes them to user clients every ~ 30-60 seconds or so (users are annotators data that are student assistants). This was not so bad at the beginning of the project, when there were only 20-30 tabs, but now that the data is large, the server blows 100 quotas and returns errors every 10-15 minutes.
The problem is that:
- Frequent data updates: only data on 1-5 of 70 tabs is likely to be updated at any minute, but which tabs have new data randomly (so I reset the entire sheet from 70 = 70 views).
- Update Interval: The need for updates occurs randomly at intervals of 30 seconds to 5 minutes (so some of them are within the quota, about 3-5x of the quota).
- Throttling: I tried setting the update to 100 calls / 100 seconds (my previous solution), but this leads to big usage problems, significantly reducing usability / performance / quality of work.
- Increase quota: The sheet API does not currently include a payment method for increasing quota. This allows me to fill out a form for a request for increasing the quota, but I'm not sure what the average response time to this is (my request is only a few days).
- Multiple service accounts. I tried using multiple service accounts to get the full 500 requests / 100 second quota (not the quota for each user), since this is a server, but Google Sheets looks at a speed limit of up to 100 requests / 100 seconds from a given IP
- Alternatives: I thought that this project may have only grown beyond the size that Sheets can handle easily, but there seem to be no good, usable, self-organizing, shared tables with a simple interface - to APIs.
/ 500 /100 ?