At a company I worked for, we needed to develop an MVP (basically a web page) and apply certain business logic to a Google Drive spreadsheet that was frequently updated by the Sales team.
In this case, we had two options:
Develop a backend to replace the current spreadsheet and have the sales team use it as a new "backoffice" - This would take a very long time, and if the hypothesis we were testing was wrong, it would be time wasted.
Create the web page and use Google's SDK to extract data from the spreadsheet.
We chose to go with the second option because it was quicker. Indeed, it was much faster than creating a new backoffice. But not as quick as we imagined. Integrating with Google's SDK requires some effort, especially to handle the OAuth logic, configure it in the console, and understand the documentation (which is quite shallow, by the way).
Anyway! We did the project and I realized that maybe other devs might have encountered similar issues. Therefore, I developed a tool that transforms Google spreadsheets into "realtime APIs" with PATCH, GET, POST, and DELETE methods.
Since it's a product for devs, I think it would be cool to hear your opinions. It's still quite primitive, but the basic features already work.
Do you have a sense for how much usage Google will allow on a given sheet or user? I.e. will Google shut down the API after a certain usage level?
(Side-note, your SSL cert seems to be having trouble, i.e. www.zerosheets.com is correctly encrypted, zerosheets.com is not.)
300 Read requests per minute 300 Write request per minute
Since Ive just launched it, that number is far far away from being reach. (If it happens, the number can be increased).
Regarding the SSL cert, some other users pointed the problem but I can`t replicated. Tried with many different browsers. Im not sure about whats happening, it`s always working for me but I will keep digging.
Thank you for your comment bro :)
To others seeing this - has anyone else ever tried this approach? Are there players out there who do this and have SOC II etc. data security measures?
Triggers/AppScript - not that I am aware of right now...
Everyone I know doing stuff with GSheets (including Glide) is using a full-sync approach with an optimistic write layer of some sort.
GSheets API frequently reports being unable to do things, sometimes because it's calculating (common for huge sheets), sometimes just because it's down, other-times because rate limits
My impression is it's not a great API to build on directly.
A trigger based system that captured writes and wrote them to an outbox sheet could drastically speed up the sync process however.
The reason why we don't use that approach is there isn't APIs sufficient to setup this sort of thing automatically (last time I checked at least) and it makes changes to the users sheets which isn't impossible to live with but is distasteful.