Hierarchial spreadsheets and you
June 19, 2021.The idea of viewing your possessions like a video game inventory is really amusing:
- 1x laptop
- 1x charging cable
- 1x face mask
- 50x alcohol wipes
- 1x large cup of jasmine green pearl milk tea of power <+7>
Long story short, I had a “wait but actually though” moment in a flash of shower thought brilliance.
It’s not like I lacked motivation for it either. As a minimalist, I’ve always wanted to do this thing where (1) you take inventory of all your things, (2) you cross out the things you don’t need, (3) you get rid of those things. The idea is to intentionally get rid of things, like that hairbrush you never ever ever ever ever use, but keep anyways “just in case”.
Now here was the hard part:
Q: Am I really going to be writing hundreds of items in a bulleted list?
A: sure why not
Q: Some things are in boxes, should I write down the things inside boxes?
A: yes, just indent the things inside boxes
Q: What about boxes in boxes? On a shelf, in a closet?
A: yea, wait fuck
There was no way I was writing a deeply nested bulleted list of hundreds of items – I’ve worked with enough big JSON/YAML behemoths to know that you lose sight of the nesting about 2 levels in. A spreadsheet is only slightly better, but again if you do nesting you either have to have huge cells or multiple spreadsheets. That’s where the second flash of shower thought brilliance kicked in – what if I do this in TreeSheets?
TreeSheets
Some spreadsheet software will let you have all kinds of things in a cell – formulae, images, sparklines, stock quotes. TreeSheets (written by the wonderful Wouter van Oortmerssen) lets you have spreadsheets in cells. I don’t know how old or new this concept is but it’s really cool, except for the outdated looking GUI, which is built on wxWidgets.
For me, it just means my nested lists don’t suck, and I can use a second column in each list to add “delete” tags to my things.
(an image should go here but I'm too lazy rn)After all was said and done, I had a huge spreadsheet of all my things. TreeSheets has a feature where you can copy your whole spreadsheet and immediately paste it somewhere and it comes out as a nested list. The output was about 500 lines long – turns out I have like 500 things. Which mentally feels like a big number, but according to my friend, “it’s one of those things that seem like a lot only if you count it, like calories.”
I had been using TreeSheets to serve as an organizational tool. By the end of the week-long journey of cataloguing all my things, we witness our third flash of brilliance: spreadsheets have formulas.
The plan was obvious. Set up a programmatic dashboard in TreeSheets using lookup formulas to locate items at will, or at least list all the items I tagged with “delete”. Kind of like a shitty query language! But TreeSheets doesn’t really support that – formulas are super limited, there’s not a lot of functions, and formula cells are more like Jupyter cells in that you can individually run them or hit Run All.
(Also, I could just use ctrl-F in TreeSheets to locate things. I need to stop overengineering.)
Anyways, formulas seem kind of hard in hierarchial spreadsheets. As for who’s solving this particular pickle, the only other hierarchial spreadsheet software I know is:
Inflex
Inflex (currently in beta as of Jun 2021) has the same idea – cells can contain spreadsheets. My impression of Inflex’s ambition is something like “take the most popular declarative language in the world – spreadsheets – and make it really really damn functional”.
To this end, they’ve built support for lists and records as special spreadsheets. I guess the idea is that you can map() and filter() them and it’ll come out the other side as another list or record cell and that’s kind of pretty. There’s also functional debugging support (static typechecking, holes _) which is also kind of pretty.
Pretty but not super polished yet UX-wise – sure, you can paste {a:2,b:[1,2,3]} as text and it’ll be represented as a spreadsheet. But right now, you can’t use cell references (e.g. map(x:x+5, the cell to the left)), so you have to copy the original data into the formula to use it. I honestly would use this if the UX were at the level of TreeSheets, so yeah let’s check back in a year or something.
Future thoughts
Honestly I don’t think any hierarchial spreadsheets support good formulaic lookups which kind of sucks. I mean, I definitely don’t want to work with nested indices like F1A1A1:F1A1A50. Honestly indexing in spreadsheets should be behind-the-scenes details, stuff the user shouldn’t have to deal with at all. I just want to select a range and run lookup or whatever!
I’d love to think about hierarchial spreadsheets of the future, but I’m tired now so I’ll end this post here bye
Hierarchial spreadsheets and you (permalink) (tweet)Thoughts on readable data processing