Show HN: Using Google Sheets as the back end/APIs of your app

Just beware of the modern version of the "Excel noob trap" which lots of investment banks fell into in the 80s and 90s. Spreadsheets are amazing generic calculating frameworks and you can do a ton in them, so a lot of banks ended up building a huge ediface of excel spreadsheets that ran a wide variety of risk, pricing and operational functions.

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.

Sadly many companies WANT to be in the excel trap...

Totally agree. I’m working for a company right now that put off ‘refactoring’ their tangle of spreadsheets and database query export ‘pipelines’ for over a decade. We are hemorrhaging money right now because it does not scale. And you won’t notice that until it all breaks down.

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.

But it would be ok maybe to use sheets just for storage. I wonder what size limitation do they have.

Can you elaborate? I understand the reasoning "we need to grow no matter the cost, will deal with problems when they arise", but deliberately sabotaging yourself doesn't sound reasonable.

> Making wide changes becomes hard/impossible

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?

not op but probably because some people are so used to using spreadsheets that even if you built them a database with custom visualizations, they'd ask for spreadsheet export.

Only thinking of this now, but a flat OpenDocument shreadsheet (“FODS”, a single XML file, unlike the usual XMLs-in-ZIP flavor) should actually be amenable to text-based version control.

For a number of reasons (some of them actually fairly substantial), spreadsheets are the only end-user programming tool that caught on and endured, so users will often choose them. It’s not even that they won’t need to find/hire/allocate programmers, it’s that using them doesn’t seem like a big deal at all.

Of course, just because you told yourself you aren’t doing a software project doesn’t mean you’re not prone to the standard problems of managing those, but the incubation period for many of them is long enough to catch a lot of non-practicioners unaware. I don’t think telling people to leave it to the professionals is the answer, for what it’s worth,—I just don’t see how we get from here to a world where one could interpolate between systems and end-user software more gracefully.

> no revision control

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.

Try to compare version histories on hundred thousands row of google sheet. It's not a pleasant experience.

Absolutely. I'm just mentioning it because sometimes I forget that it even exists and it is better than nothing at all.

It's not always by choice. It took me around 4 hours of red tape at work to get into a place where I could run a policy compliant python hello world.

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...

Not op but that's one of a milder example. Some management people prefer the look and feel administration to be excel-like, even able to do data modification like excel.

Some even worse that they require the data to be in excel since that's all they know.

This is remarkably similar to the situation you get with visual scripting in game development - it's intended as a way for designers and artists to do small coding tasks on their own without needing to bother a programmer (e.g. set up a pressure plate that opens a door when you step on it), but their creations also tend to spiral out of control. There's a whole website dedicated to examples of this from Unreal Engine[1]

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/

> It’s not even that they won’t need to find/hire/allocate programmers, it’s that using them doesn’t seem like a big deal at all.

I don't understand why it doesn't seem like a big deal, knowing the limitations of spreadsheets. The spreadsheet experts have to know that it will be spaghetti in a decade, right?

I still think the solution for unifying those is to use a database as a backend, with the spreadsheets merely being a frontend for that backend. It does require the people to come somewhat closer to the software, though. I don't know that software can reasonably work with the ill-defined schema of spreadsheets.