7 ms·

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 t

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.

pbhjpbhj 3y ago
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.
- alexmojaki 3y ago
  You can embed an integrated Jupyter notebook into a Grist spreadsheet: https://github.com/gristlabs/jupyterlite-widget/blob/main/USAGE.md https://github.com/gristlabs/jupyterlite-widget/blob/main/US...
- FredPret 3y ago
  I think Apple Numbers work like this
twelvechairs 3y ago
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
- laingc 3y ago
  Doesn’t Airtable more or less do this?
RyEgswuCsn 3y ago
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`) Very useful!
- klysm 3y ago
  But I still can’t write joins as formulas
  - smokefoot 3y ago
    Can you do this in grist or Airtable? Building join tables with calculated fields is a missing feature in all spreadsheet-like solutions as far as I know. Also I concur with the parent that I often get frustrated by my inability to do spreadsheety things in these tools.
    - paulfitz 3y ago
      In Grist, reference columns let you do a lot of what you can do with a join https://support.getgrist.com/col-refs/ https://support.getgrist.com/col-refs/ while still having spreadsheet-style immediate updates when underlying data changes. Also, if you just want to do queries and don't care about instant updates, you can do any SQL you like including joins with a SQL endpoint or a custom widget https://twitter.com/getgrist/status/1710018836836077967 https://twitter.com/getgrist/status/1710018836836077967 [Grist employee]
      - klysm 3y ago
        I've never used grist and know nothing, but if we have foreign keys why can't we write join expressions?
    - 3y ago
harinijan 3y ago
You can checkout opensource project Rowyhttps://github.com/rowyio/rowy https://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. Disclaimer, I am one of the makers :)
- spaniard89277 3y ago
  This one is very interesting, thanks for sharing.
- samanator 3y ago
  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.
  - mgummelt 3y ago
    Shameless plug, but Plato (https://plato.io https://plato.io) is a PG frontend. Email me if you don't find what you need: mgummelt@plato.io.
PurpleRamen 3y ago
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.
- paulfitz 3y ago
  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]
  - _v7gu 3y ago
    You might want to look into calculated columns
  - PurpleRamen 3y ago
    This is normally called a database-app.
lovasoa 3y ago
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. [1] https://sql.ophir.dev/ https://sql.ophir.dev/
coldcode 3y ago
I built https://en.wikipedia.org/wiki/Trapeze_(spreadsheet_program) 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.
- freedomben 3y ago
  I've long suspected that excels early dominance killed a lot of innovation. Glad it's finally happening now, but a bit sad on what could have been. Still have the code? You should open source it! Probably nothing comes off it, but that's guaranteed to be the outcome of keeping it closed
  - CWuestefeld 3y ago
    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.
  - coldcode 3y ago
    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 https://thecodist.com). I also have the original manual, buried somewhere as well, which would be enough for someone to try recreating the engine).
khobragade 3y ago
https://ultorg.com https://ultorg.com might be useful to you!
hansonw 3y ago
Our startup is building https://arcwise.app 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.
- nusl 3y ago
  Being tied to GSheets immediately disqualifies this for me, though there are millions of others happily using it
prometheus76 3y ago
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.
zem 3y ago
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 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 https://github.com/gristlabs/grist-core/issues/422
- kgodey 3y ago
  This is what we're trying to do with Mathesar (https://github.com/centerofci/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.
  - zem 3y ago
    ooh nice, i'll star it and follow the progress with anticipation :)
- ar-jan 3y ago
  Have you seen https://github.com/nocodb/nocodb https://github.com/nocodb/nocodb? I'm not sure it supports concurrent editing, but seems like it might.
  - zem 3y ago
    I have, and it does not. I filed an issue asking if it was on their roadmap, but they said they didn't have plans for it.
deleted 3y ago
[deleted]

pbhjpbhj 3y ago

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.

alexmojaki 3y ago
You can embed an integrated Jupyter notebook into a Grist spreadsheet: https://github.com/gristlabs/jupyterlite-widget/blob/main/USAGE.md https://github.com/gristlabs/jupyterlite-widget/blob/main/US...
FredPret 3y ago
I think Apple Numbers work like this

twelvechairs 3y ago

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

laingc 3y ago
Doesn’t Airtable more or less do this?

RyEgswuCsn 3y ago

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`) Very useful!

klysm 3y ago
But I still can’t write joins as formulas
- smokefoot 3y ago
  Can you do this in grist or Airtable? Building join tables with calculated fields is a missing feature in all spreadsheet-like solutions as far as I know. Also I concur with the parent that I often get frustrated by my inability to do spreadsheety things in these tools.
  - paulfitz 3y ago
    In Grist, reference columns let you do a lot of what you can do with a join https://support.getgrist.com/col-refs/ https://support.getgrist.com/col-refs/ while still having spreadsheet-style immediate updates when underlying data changes. Also, if you just want to do queries and don't care about instant updates, you can do any SQL you like including joins with a SQL endpoint or a custom widget https://twitter.com/getgrist/status/1710018836836077967 https://twitter.com/getgrist/status/1710018836836077967 [Grist employee]
    - klysm 3y ago
      I've never used grist and know nothing, but if we have foreign keys why can't we write join expressions?
  - 3y ago

harinijan 3y ago

You can checkout opensource project Rowyhttps://github.com/rowyio/rowy https://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. Disclaimer, I am one of the makers :)

spaniard89277 3y ago
This one is very interesting, thanks for sharing.
samanator 3y ago
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.
- mgummelt 3y ago
  Shameless plug, but Plato (https://plato.io https://plato.io) is a PG frontend. Email me if you don't find what you need: mgummelt@plato.io.

PurpleRamen 3y ago

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.

paulfitz 3y ago
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]
- _v7gu 3y ago
  You might want to look into calculated columns
- PurpleRamen 3y ago
  This is normally called a database-app.

lovasoa 3y ago

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. [1] https://sql.ophir.dev/ https://sql.ophir.dev/

coldcode 3y ago

I built https://en.wikipedia.org/wiki/Trapeze_(spreadsheet_program) 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.

freedomben 3y ago
I've long suspected that excels early dominance killed a lot of innovation. Glad it's finally happening now, but a bit sad on what could have been. Still have the code? You should open source it! Probably nothing comes off it, but that's guaranteed to be the outcome of keeping it closed
- CWuestefeld 3y ago
  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.
- coldcode 3y ago
  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 https://thecodist.com). I also have the original manual, buried somewhere as well, which would be enough for someone to try recreating the engine).

khobragade 3y ago

https://ultorg.com https://ultorg.com might be useful to you!

hansonw 3y ago

Our startup is building https://arcwise.app 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.

nusl 3y ago
Being tied to GSheets immediately disqualifies this for me, though there are millions of others happily using it

prometheus76 3y ago

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.

zem 3y ago

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 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 https://github.com/gristlabs/grist-core/issues/422

kgodey 3y ago
This is what we're trying to do with Mathesar (https://github.com/centerofci/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.
- zem 3y ago
  ooh nice, i'll star it and follow the progress with anticipation :)
ar-jan 3y ago
Have you seen https://github.com/nocodb/nocodb https://github.com/nocodb/nocodb? I'm not sure it supports concurrent editing, but seems like it might.
- zem 3y ago
  I have, and it does not. I filed an issue asking if it was on their roadmap, but they said they didn't have plans for it.

deleted 3y ago

[deleted]