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.
With a few plugins and extensions you can really do amazing things with this type of setup. The problem is that your spreadsheets basically become an unmaintainable and impenetrable nightmare and all your business logic is held hostage in various people's personal sheets. Making wide changes becomes hard/impossible and doing something that in a traditional software framework would be easy ("Let's change the frobnicator to twizzle first and then twozzle afterwards, instead of the other way round as it does it now") become very difficult requiring many many changes in many many sheets with a lot of risk, and a lot of dilligence to be sure you have actually made a particular critical fix everywhere.
Personally, I think good refactoring support would be the way to go - make it easier for a programmer to go in and "extract method" or similar to clean things up if they have gotten out of hand, without necessarily having to rewrite the whole thing from scratch.
[1] https://blueprintsfromhell.tumblr.com/
Not a full IDE or god forbid pip modules - just hello world. And that's one of the better experiences thus far. Some finance shops simple don't give you any options.
Its par for the course to give office workers access to only Excel and then people are surprised when they build abominations in excel...
Now you have effectively created bottlenecks for all critical processes because simultaneously working on spreadsheets is about the most dangerous thing you can do. Data integration becomes a perilous and time consuming affair; there’s no easy way to guarantee conformity between spreadsheets.
You paper over these problems with more code than you ever would have had if you switched to the proper tools earlier.
To add to this - no revision control.
OK, maybe you can store your spreadsheets in a RCS, but can you get diffs of the changes, so you can confirm the revision you're about to push is what you meant to do? Can you review the history of diffs, so you can see how the system has changed over time? If multiple people make changes, can you do a merge? Do you even have a separate "working copy" where you can try stuff out, or are you just editing the production copy with no safety harness?
I'm not claiming this is a sufficient solution, but Google Sheets does keep a version history and you can see changes. Doesn't solve the problem of working copies and merging though.
I've used it for a lot of system data that needs to be modified by a few people at most. With a little bit of careful code and caching (I like validating and syncing to S3) you can easily use it as a crud frontend for important system data.
It also can make great adhoc dashboards - plug into APIs (even private ones if you add in custom Google Scripts code) or push data updates to the sheets. I've had rather large reports auto updated on a schedule with multiple views of the data (pivot tables, queries, lookups, etc). Fully focused customized views into the data needed to solve specific internal problems with really quick delivery times.
Sure, a custom developed option should be substantially better than Google Sheets, but you won't develop it quicker. By the time you actually need something bigger/better, you should be at a place where your needs are better defined and you can afford the resources to develop something better.
Deleted Comment
Deleted Comment
Deleted Comment
It's a free Google Sheets / Apps Script you just paste to your sheet, and it turns the sheet into a full CRUD. It's kind of rate limited though but completely free!
Edit: I've thought about creating a company around Sheets before, and the problem is that once you get to the "willing to pay" stage, you also kind of outgrow Sheets. I'd rather migrate to Turso, Cloudflare D1, or Pocketbase instead of staying with Sheets or SpreadAPI, because of the limitations.
Please keep in mind that you can always post your improvement ideas or PR's here https://github.com/ziolko/spreadapi.
See https://github.com/ziolko/spreadapi/blob/master/spread-api.j...
As an example:
A single curl request, that POSTs a user, and then GETs the data. So you can do multiple POSTs to add multiple rows too.Last week I was looking for a place to store random data with API access, and was looking at making a Google Sheets backend, but PocketBase was easy and didn't have a 60 rpm quota [1].
Deploying to a cheap VPS was very easy with CapRover.
[0] https://pocketbase.io/
[1] https://developers.google.com/sheets/api/limits
super easy for prototyping and getting the job done, while google sheet as a backend is nice too but I need authentication etc etc
My infrastructure is 100% focused in scale so I think we can work together, we just need to share the costs.
Please send me a message here https://www.zerosheets.com/contact so I can get your contact.
- [0] https://thetechenabler.substack.com/i/142898781/making-a-sim...
- [1] https://github.com/eeue56/simple-link-aggregator
Isn't this one of the use cases for Airtable?
Dead Comment
I haven't started yet because (1) I still haven't finished writing up my last user script experience blog post, and (2) I'm terrified of the auth nightmare. It might be easier or harder in the user script context—I am in the context of a web page, so maybe that means I can do a normal oauth flow from there or something?
1. https://github.com/nprapps/dailygraphics-next