I always get excited about these databases-that-look-like-spreadsheets and then get frustrated when they stop me doing spreadsheety things.
I want it to work the other way around: a spreadsheet with more databasy features I can choose to use:
In the same way that the chart wizard can do a good job of spotting the “table” of data that the current cell is in, the spreadsheet could auto-detect natural tables and make it easy to create and manage named ranges etc. And then allow formulas to offer better ergonomics than vlookup etc and even allow sql etc?
Things like allowing column widths to change for different rows in the same column etc would also be awesome. And offering an insert-column that automatically only inserts for the current table etc.
I built https://en.wikipedia.org/wiki/Trapeze_(spreadsheet_program) in 1987 which is the spritual ancestor to a lot of alternative spreadsheets. We were killed by having to compete with Excel with something not a row and column spreadsheet (it's named block-oriented). If it hadn't died in 1989, a lot of what people are doing today would have been possible over time, but decades ago. Some of the recent attempts were aware of Trapeze, but it's been so far in the past I have no idea about these folks.
Back then, I worked on an office suite called Enable, specifically on the spreadsheet module. I can confirm that this is what killed us.
And for the time, Enable's ss had some interesting innovation. One was the integration through the whole suite. But most interesting to me, on the ss team, was the 3D nature of the app.
Excel liked to bill itself as 3D, but it's not really: their tabs aren't a true third dimension. But Enable's was, so any range could be specified with a Z-dimension if you chose. This allowed interesting patterns like a report with an XY plane for each month, with the top one being a summary, implemented by summing (or whatever operation is appropriate) that 1x1 cell down through the Z range.
Another contemporary one I liked to play with also claimed to be 3D and wasn't but in an interesting way. Lucid 3D was actually hierarchical, such that any sheet could expose a single value to be consumed by a single cell in a parent container sheet, recursively.
At one point I found it, but since its gotten lost in various Mac transfers so I am not sure it still exists. I do remember it was uncompilable (ThinkC I believe from 1988). If I find it I will put it somewhere and mention it on my blog (https://thecodist.com). I also have the original manual, buried somewhere as well, which would be enough for someone to try recreating the engine).
Wow, Trapeze is cool! Never heard of it until now. And it basically is a spiritual ancestor to what I've been working on. A "block-based" spreadsheet that also preserves row/col style referencing where needed.
And we're still competing with Excel almost 40 years later...
I still love this concept in Numbers.app, and usually develop new spreadsheet models there before moving it to Google Sheets to share with a wider audience, usually by replicating tons of sheets for the various data table.
So, thank you very much for your invention, and I'm glad it lives on!
I would prefer RShiny, but the intended audience can't modify that...
You can checkout opensource project Rowyhttps://github.com/rowyio/rowy - it a spreadsheet UI for database and can do databasy things like you said!
formulas but in JS/TS,
on data change trigger of serverless cloud functions,
assign default values for database columns,
granular permission controls,
store files/assets images to cloud storage,
bulk deletes and more.
I remember rowy from a while ago. It said then that other databases (aside from Firestore) are coming soon. Any news on that? I'm looking for a Postgres frontend.
Excel already does this except for the variable column width.
One can select a cell inside a table-like range and click "Format as a Table" to have Excel automatically infer the range of the table (including the headers) and enhance it with database-like features. One can then refer to columns in formulas using references like so: `=[@[first column]] + [@second_column]`. Modifying the formula in one cell will also update all the cells in the same columns. The table object becomes accessible using labels (e.g. `Table1`)
Interesting. I just tried. This part is not working for me as yet -- "Modifying the formula in one cell will also update all the cells in the same columns."
In Grist, reference columns let you do a lot of what you can do with a join https://support.getgrist.com/col-refs/ while still having spreadsheet-style immediate updates when underlying data changes.
Yes, it's kinda annoying when people call something spreadsheet, when it's really just a richful WYSIWYG-interface for a relational database. Not that it's bad to have a good Database-Interface, but the flawed communication will make a poor impression with those who really want a real excel-like spreadsheet, and then got something else. This will probably more harmful than beneficial in the long run.
The motivation for calling Grist a spreadsheet is that it has formulas, and cell values get updated automatically when something they depend on gets updated. Agree there is scope for misunderstanding here, maybe there's a better word?
[Grist employee]
Yes, what I'd want is like a Jupyter notebook for spreadsheets.
Drag a box to define a new table, intersperse whitespace with text/images/graphs/formulae (two cells) that aren't locked to a grid based on a upper-left table.
Auto-recognise the first line in a table as a title, second line as column headings.
LibreOffice already allows SQL on spreadsheets; but SQL formulae would be cool.
Yes ! Maybe I'm old-fashioned, but I do still think that SQL is a better interface to databases than excel formulas, and classical spreadsheet UIs are a better interface for, well, spreadsheets.
I am building an open-source tool that allows quickly building web user interfaces on top of relational databases [1]. Among users are many people without a CS background, and without prior knowledge of SQL. And from the feedback I get, the pain point for getting started is very rarely learning SQL. Spreadsheet formulas are often much more complicated than the basic SQL queries that cover 90% of what everyone needs.
Yes the joy of a spreadsheet is the blank slate - you can just start writing random things in random boxes.
What I'd like is something you can do both. Start writing random things but then slowly formalise - define columns as specific types for consistency etc. And if you do this enough end up with a structured database. But not force only one or the other
i want exactly the opposite - something that does not try to be a spreadsheet, but gives me a grid view of a database table, with concurrent edits a la google sheets, and lets me access the same data from my webapp backend.
i have been searching for this for literally years, all the time maintaining an app as a google sheets script, because much as i would prefer something self-hosted and customisable, that collaborative grid view is the ideal user interface from my users' point of view. so far nothing has fit the bill - basetool (https://github.com/basetool-io/basetool) might have but it's discontinued and underdocumented, and i'm not really a web developer so i don't feel up to the challenge of getting it running and integrated into an app.
grist actually came really close from a ui perspective, but it was too focused on being a spreadsheet and doing computation in the frontend. i filed an issue that explains my use case in more detail: https://github.com/gristlabs/grist-core/issues/422
We support concurrent editing, so you see updates from your teammates in realtime, but we just haven't built a frontend for it yet, so you don't get the visual indicators a la Figma of where your teammates are and what they're doing.
Email me if you want to chat more: mgummelt@plato.io
hi michael, plato does look interesting, but probably won't fit my needs. i'm replying here rather than over email because the reason why brings up another interesting point - pretty much all the datagrid tools in the low/no-code space seem to be focused on the "company's internal tools" use case, where you have a few admins who need access to the raw data in the database and want a nice UI to edit it with.
what i'm trying to build is a multi-user version of a desktop app, so while i'm not really opposed to paying for a good tool (albeit at a nonprofit-friendly tier :)), per-user pricing is a non-starter, as is the high cost of self-hosting.
by way of comparison, the current "product" is a google sheet with an attached script file, and when someone wants to use it, i just send them a link to clone the sheet, and they have their own instance that they can share with whoever they like, setting read/edit permissions entirely by themselves. even if the non-google-sheets version ends up being something i need to host myself, i would like to replicate that level of ownership, where a group of users can create their own isolated instance and control its sharing and lifetime themselves.
what i'm looking for is not so much low-code, but a collaborative grid component that i can use as a primary user interface, either to drop into my app (if sufficiently modular) or build the rest of my app around (if sufficiently extensible). i am just a bit astonished that no one else, open source or otherwise, seems to have had the same need.
Or, alternatively, I want a spreadsheet that can be driven by an external program.
I finally sat down and wrote a simple terminal program to manipulate tabular data just so I could drive it from Python, Zig, Rust, etc.
At various meetups, I have discovered that quite a few of us have written a "shitty limited spreadsheet" because you can't drive the spreadsheets programatically.
in my case the killer feature is the collaborative editing, but yes, it would be great to have the spreadsheet just running in its own server and supporting web connections but also rpc calls from another process running on the same box. if grist supported that it would likely satisfy my use case completely.
This is what we're trying to do with Mathesar (https://github.com/centerofci/mathesar). We probably don't meet your needs yet because we don't support real-time concurrent editing, but we're actively working on the project and it is early days.
I'm pretty much looking for the same thing.
It's sad how most of these project try to add their own layer ("vocabulary") on top of the relational semantics without first trying to surface as much of it as possible.
What I'd like to see is something that can connect to a pg server (or sqlite, or mariadb, but I think settling on one is more feasible) and tries to surface as much as possible of the underlying data representation, and add features that are translatable to how pg works, instead of just using pg (and in grist's case, sqlite) as a dumb backend.
Our startup is building https://arcwise.app, which allows you to embed full-fledged SQL tables inside Google Sheets! We’re in the process of building out support for joins & subqueries, would be curious what people think.
Powerpivot within Excel and Power BI itself allows one to use SQL to pull data from an ERP or from a data warehouse, allows one to build a model with relationships between tables (instead of vlookup or index/match) and allows you to work with much larger tables than native Excel.
I want it to work the other way around: a spreadsheet with more databasy features I can choose to use:
In the same way that the chart wizard can do a good job of spotting the “table” of data that the current cell is in, the spreadsheet could auto-detect natural tables and make it easy to create and manage named ranges etc. And then allow formulas to offer better ergonomics than vlookup etc and even allow sql etc?
Things like allowing column widths to change for different rows in the same column etc would also be awesome. And offering an insert-column that automatically only inserts for the current table etc.