Yet another Hacker News

Adding Window Functions to a Spreadsheet

29 points reedciccio 2 hours ago 19 comments

shevy-java 2 hours ago | parent

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.

paulfitz 2 hours ago | parent

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.

whizzter 2 hours ago | parent

What parts are Python and how does one know where the classic Spreadsheet functionality ends and Python starts ?

librasteve 1 hour ago | parent

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

dsagal 23 minutes ago | parent

"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.

chii 2 hours ago | parent

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 2 hours ago | parent

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.

librasteve 1 hour ago | parent

hmmm sigils, eh?

librasteve 1 hour ago | parent

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.

All feedback good and bad v welcome.

https://omasheet.org

WillAdams 1 hour ago | parent

Did you consider trying pyspread?

librasteve 1 hour ago | parent

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

WillAdams 38 minutes ago | parent

It supports Python and is Pythonic and being free, matches my budget.

seemaze 1 hour ago | parent

Imagine a world where we starting teaching SQL in primary school..

librasteve 1 hour ago | parent

I imagine a world where LLM makes languages like SQL akin to middle English (ie a specialist skill taught to graduates and up)

xeonmc 1 hour ago | parent

or better, a Tolkienian incantation

pstuart 38 minutes ago | parent

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.

halo 1 hour ago | parent

I sometimes feel the world is slowly reinventing BusinessObjects

mallahan 27 minutes ago | parent

The way Excel formulas so easily break drove me to create https://www.treesum.app/.

quaintdev 26 minutes ago | parent

Checkout ironcalc if you are looking for spreadsheet alternatives https://www.ironcalc.com/index.html