The syntax is so archaic ... it really is anything
but a thing of beauty.
prev_balance = PREVIOUS(rec, order_by="Date").Running_Balance or 0
prev_balance + $Amount
It's such a strange mix-up of stuff. PREVIOUS() is a macro or looks
like one, right? But it is a function call assumingly. But then what's
with .Running_Balance? Guess that also ties into a function. But why
use different syntax for essentially the same thing? It seems rather
weird to write code like that. What would the python variant be like?
Or a DSL?
Does that really convey most easily what is wanted? It seems needlessly
verbose. Why would it require both $Person and $Date? Should that not
be easier to figure out in its own right? Is that efficient?
People need to remember that mentally, unless they use AI.
that’s why https://omasheet.org has its own DSL. that said it’s no doubt got some wrinkles so I’ll be reviewing all the comments on this hn post very carefully
E.g. UPPER($A) is just a function that calls $A.upper(). And $A is just syntactic sugar over rec.A.
PREVIOUS() is only special in that it automatically creates an index, which is important for efficiency. But generally you can look up a set of records and write multi-line Python code to do whatever you want with it and return a value. And it all runs in a sandbox with no access to anything except this document's data.
Oh, so this is something Grist specific rather than something inherited from Excel? (I remember reading sometime back that MS was replacing VBA with Python)
That's what makes it clunky. Returning a record is OK, but then a record is basically what spreadsheets are (ala, a table). Except records are named columns in a spreadsheet, and accessing it using a dot syntax looks out of place imho.
I reckon it makes more sense to return a table, and then have an index lookup in that table: e.g., index(PREVIOUS(blah_blah), column). Then you can also use an indirection to choose which column from another cell if desired.
This is how google finance in sheets returns some data (like exchange rates, iirc).
I think it's an push in Spreadsheets to become more "DB-alike" for less arbitrary results (like in the problem example the article starts out with) but kind-of misguided since it's complicated to do "easy things".
The root issue is that there's almost 50 years of spreadsheet syntax to consider and not colliding with.
I’m all in favour of making spreadsheets better, with code friendly formulas. Here’s one I’m working on - somewhat earlier in dev cycle than grist and while I did consider Python (and lua) for the formula DSL decided to cook up my own.
not really … I did play with pyspread some time ago tbh i was put off by the basic UI and rather childlike website (sorry I know that’s not really fair, but I am a human). I’ll definitely take a fresh look at it now you mentioned it. curious: what are the standout features of pyspread that you like?
oh and omasheet is Rust and uses Polars so I am working with that rather than NumPy SciPy
The main problem with spreadsheets at the business level is that they're the root cause of the Data Swamp failure mode.
People fool themselves into thinking "it's tabular, therefore it's structured" then go on to do assinine shit like putting bonus calculations in random cells, or adding extra tables on the same sheet.
The big value B2B is never going to be whether it has Python. The big value is being able to pull that data and the calculations out of Excel.
I agree 100% with that. And I suggest that LLMs make it much easier to “pull out” business calculations. Maybe into SQL with a custom Python script even :-).
For 41 years Excel has been the dominant way to mess with numbers in a grid. Google Sheets and Apple Numbers are really just me too players, albeit with their niches. LibreOffice struggles imo since its main claim is to be fully Excel compatible but there are so many ancient Excel sheets that fail in practice (VBScript?).
I think that there will continue to be a need for folks to mess with numbers in a grid … more between a super calculator and an interactive dataset. I could have chosen a DB vernacular for Omasheet - DuckDB does this for Polars - but that lacks some of the numeric purity of the sheet. So I have gone for the core Polars query model - closer to Arrow columnar data (parquet), streaming support, lowered calculations, lazy execution … but without the DB abstraction layer.
Yeah, it's definitely going to be around for a long time. For the average non-coder, it's probably the most logical way to structure a calculation.
Maybe a controversial take, but IMO Except is the most successful and most popular GUI framework in history. The Internet could die today, and tomorrow the world would go back to spreadsheet programs for business tasks.
"random business calculations in random cells" is what a lot of quick business stuff lives on, putting a few formulas instead of an entire application is what you need (not even AI's can keep up with that once you're used to it).
The danger as always, is when it becomes the entrenched business process, and as you say, being able to automatically pull out the logic when formalizing it is the win.
I kind-of like it, spontaneously taking tables from CSV files doesn't seem to be there (there's a mention of parquet via dataframes so perhaps it's supported implicitly, but maybe add that documentation?).
I see that import/export pipelines are there, so transform pipelines should be simple, and I guess that's a quick-n-dirty usecase it's there for (the spreadsheet part of "taco-bell programming" ala Dziuba ?).
Personally though for me often, the spreadsheet's interactive and quick recalcs is the important part, the exploratority quick checks with numbers updating, now if it can take arbitrary sheets and get out all "unstructured" calculations so one can edit them more properly later it's a win, or am I missing other major usecases?
Ironically, they kind of tried with New Math. Not SQL specific but the foundations of what it represents. I caught the tail end of it but it's been so long I have no memories of what I was actually taught at the time.
Regarding adding higher level functions to spreadsheets in general. For people using Excel (at least, a modern-ish version that has LAMBDA), LLMs are shockingly good at writing LAMBDA functions. Especially if you have the “Advanced Formula Environment”, which lets you write whole modules of multi-line functions in text files.
I’m personally allergic to ever using macros in Excel, and this is basically a better version of that for many (not all) purposes.
For a stupid example, I had Opus write a bunch of linear algebra functions, including stuff like computing eigenvectors, culminating in a single function that computes rotation matrices for point-cloud best-fitting (ie the procrustes problem). Yes, I’m fully aware it’s sort of cursed to do that, yes it works, and yes normal people can use the resulting spreadsheet fine, they just magically have an ALIGN3D(frompoints, topoints) function.
shevy-java | 5 days ago
The syntax strangeness continues:
Does that really convey most easily what is wanted? It seems needlessly verbose. Why would it require both $Person and $Date? Should that not be easier to figure out in its own right? Is that efficient?People need to remember that mentally, unless they use AI.
paulfitz | 5 days ago
PREVIOUS returns a record, then .Running_Balance pulls out one field of it.
whizzter | 5 days ago
librasteve | 5 days ago
whizzter | 4 days ago
dsagal | 5 days ago
E.g. UPPER($A) is just a function that calls $A.upper(). And $A is just syntactic sugar over rec.A.
PREVIOUS() is only special in that it automatically creates an index, which is important for efficiency. But generally you can look up a set of records and write multi-line Python code to do whatever you want with it and return a value. And it all runs in a sandbox with no access to anything except this document's data.
whizzter | 4 days ago
chii | 5 days ago
I reckon it makes more sense to return a table, and then have an index lookup in that table: e.g., index(PREVIOUS(blah_blah), column). Then you can also use an indirection to choose which column from another cell if desired.
This is how google finance in sheets returns some data (like exchange rates, iirc).
whizzter | 5 days ago
The root issue is that there's almost 50 years of spreadsheet syntax to consider and not colliding with.
librasteve | 5 days ago
librasteve | 5 days ago
All feedback good and bad v welcome.
https://omasheet.org
WillAdams | 5 days ago
librasteve | 5 days ago
oh and omasheet is Rust and uses Polars so I am working with that rather than NumPy SciPy
WillAdams | 5 days ago
StableAlkyne | 4 days ago
People fool themselves into thinking "it's tabular, therefore it's structured" then go on to do assinine shit like putting bonus calculations in random cells, or adding extra tables on the same sheet.
The big value B2B is never going to be whether it has Python. The big value is being able to pull that data and the calculations out of Excel.
librasteve | 4 days ago
For 41 years Excel has been the dominant way to mess with numbers in a grid. Google Sheets and Apple Numbers are really just me too players, albeit with their niches. LibreOffice struggles imo since its main claim is to be fully Excel compatible but there are so many ancient Excel sheets that fail in practice (VBScript?).
I think that there will continue to be a need for folks to mess with numbers in a grid … more between a super calculator and an interactive dataset. I could have chosen a DB vernacular for Omasheet - DuckDB does this for Polars - but that lacks some of the numeric purity of the sheet. So I have gone for the core Polars query model - closer to Arrow columnar data (parquet), streaming support, lowered calculations, lazy execution … but without the DB abstraction layer.
StableAlkyne | 4 days ago
Maybe a controversial take, but IMO Except is the most successful and most popular GUI framework in history. The Internet could die today, and tomorrow the world would go back to spreadsheet programs for business tasks.
whizzter | 4 days ago
The danger as always, is when it becomes the entrenched business process, and as you say, being able to automatically pull out the logic when formalizing it is the win.
whizzter | 4 days ago
I see that import/export pipelines are there, so transform pipelines should be simple, and I guess that's a quick-n-dirty usecase it's there for (the spreadsheet part of "taco-bell programming" ala Dziuba ?).
Personally though for me often, the spreadsheet's interactive and quick recalcs is the important part, the exploratority quick checks with numbers updating, now if it can take arbitrary sheets and get out all "unstructured" calculations so one can edit them more properly later it's a win, or am I missing other major usecases?
seemaze | 5 days ago
librasteve | 5 days ago
xeonmc | 5 days ago
pstuart | 5 days ago
halo | 5 days ago
mallahan | 5 days ago
xixixao | 4 days ago
quaintdev | 5 days ago
cluckindan | 4 days ago
nukanse | 4 days ago
nhatcher | 4 days ago
https://codcel.io/en
heisenzombie | 4 days ago
I’m personally allergic to ever using macros in Excel, and this is basically a better version of that for many (not all) purposes.
For a stupid example, I had Opus write a bunch of linear algebra functions, including stuff like computing eigenvectors, culminating in a single function that computes rotation matrices for point-cloud best-fitting (ie the procrustes problem). Yes, I’m fully aware it’s sort of cursed to do that, yes it works, and yes normal people can use the resulting spreadsheet fine, they just magically have an ALIGN3D(frompoints, topoints) function.