Hacker News
points by shevy-java 4 days ago | 0 comments
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?

The syntax strangeness continues:

    rates = Rates.lookupRecords(Person=$Person, order_by="Rate_Start")
    rates.find.le($Date).Hourly_Rate
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.

[–] whizzter 4 days ago
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.

[–] paulfitz 4 days ago
It is straight Python. Yes the spreadsheet habit of making functions SHOUT does look weird mixed in with Python syntax.

PREVIOUS returns a record, then .Running_Balance pulls out one field of it.

[–] chii 4 days ago
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).

[–] whizzter 4 days ago
What parts are Python and how does one know where the classic Spreadsheet functionality ends and Python starts ?
[–] dsagal 4 days ago
"classic Spreadsheet functionality" is just familiar Excel functions with uppercase names implemented in Python, right here: https://github.com/gristlabs/grist-core/tree/main/sandbox/gr...

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 3 days ago
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)
[–] librasteve 4 days ago
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
[–] whizzter 3 days ago
Not entirely uninteresting, wrote some thoughts in your main comment thread.
[–] librasteve 4 days ago
hmmm sigils, eh?