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.
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.
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.
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).
All feedback good and back v welcome.
https://omasheet.org
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.
The root issue is that there's almost 50 years of spreadsheet syntax to consider and not colliding with.
PREVIOUS returns a record, then .Running_Balance pulls out one field of it.
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).