Ask HN: What are your most common tasks and painpoints in spreadsheet software? | Hacker News Reader

Ask HN: What are your most common tasks and painpoints in spreadsheet software?

26 pointsmarttilaine33 comments

Through a hobby project I've ended up implementing a spreadsheet app with support for lots of file formats (csv, tsv, json, ndjson, sqlite). The formula engine uses JS (e.g. the formula `max($('A1:A9'))` equals to excel formula `MAX(A1:A9)`. I also have a CLI which is capable of executing those JS formulas on files without the GUI.

It has been fun to code but I don't know where to take this project (if anywhere). Turning it into a polished app requires a significant time investment still.

Therefore I was wondering if there's some specific painpoints in the spreadsheet world that I could fix and provide a solution to the world via this app, since I have a solid starting point with it.

View on HN

Turning it into a polished app requires a significant time investment still.

Excel is the standard for a "polished" spreadsheet. It has been in development for about 40 years (since it was released in 1985) and has had the resources the most valuable company in the world can put into a flagship cash cow application.

It's-not-Excel is the primary pain point for all spreadsheet applications.

Documentation, training, third-party how-to's, etc. are one of the important ways that It's-not-Excel spreadsheets cause pain.

OK, sure the price of Excel is also a pain point and proprietary licenses are also one. But there might be very reasonable reasons for not focusing on users who prioritize those. Or not.

And Google Sheets and the Apple office suite already cost zero dollars, so there's that too.

Good luck.

You nailed it. The only thing Excel doesn't have is the well-designed UI and collaborative functionality of Google Sheets. Named tables, VBA, Power Query? I'm never leaving.

It has the collaborative functionality in Excel Online and in OneDrive-hosted Office-365-subscription desktop Excel.

Yes, but it is bolted on an existing desktop application.

I feel it always lags. I also tried to use their API once to e.g. upload data to a sheet but it was really messy.

In google sheets all this worked just fine.

Also the price of Excel rounds to zero when added to a professional salary for essentially any profession.

libreoffice calc is foss, reads excel files and has been around for a long time. i used it professionally since 2011 (used open office before that).

If I add up all the costs required that "effectively round to zero" the remainder from a normal salary rounds to zero.

Nonsense, or surely you'll be able to list these costs that add up to, let's say, over 50% of a salary or $30k plus.

(The Netherlands) Pension contributions + employer taxes + office space. First two add up to about 70% on top of gross wages paid. Office space I’d estimate at € 2k per office worker. But indeed, laptops and MS software is cheap in comparison. Bloomberg Terminal comes to mind as a not so cheap software package.

We recently build spreadsheet support in our app.

Our pain-points are almost entierly mundane:

* Macro-enabled Excel files, that can only be run on or by Windows.

* A endless log of Excel files that have errors. Most of these are files that are not compliant to the spec, but have been supported by various software over the years.

* User creating formats and overly complicated/brittle spreadsheet.

A VBA interpreter in WASM would be great!

I’d like to be able to have custom functions that use cells as parameters.

It’s often difficult to understand the state of a cell (what is it’s formatting and other rules)

It’s difficult to reason about what the underlying value of a cell is and it’s representation (e.g, 12-nov-22 == 12/11/2022) and what will happen when you change the value type of a cell

At least for excel, scrolling doesn’t follow curser. Cells are deselected when focus leaves a window.

It’s not like any of these are going to revolutionize the spreadsheet.

I think old versions of access had a front-end builder. Now MS have power apps, but it’s not very nice and the backend data store is also not great.

A spreadsheet backed app builder would be nice.

I wish I could name cells, such that my

Having coding/logic more decoupled from visuals/styling would be interesting.

E.g. allowing the application to be more visually clear when describing states of cells and the relationships between them, while giving one more freedom in the way those cells are then displayed.

Spreadsheets I create often have some 'global' variables, some sheet-specific ones, and many column-level formulas. It would be great to see first-class support for these. In Excel, need to copy over formulas to all cells in a column, albeit there's a short cut for that. Global abd sheet-level variables need to be put on the sides of columnar data or in separate sheets.

Getting Excel to interact directly with MySQL is ugly. Querying freezes up Excel because it's single threaded? I don't know how to overcome that problem.

Also I wish there was a spreadsheet software that was more closely connected with something modern like Python. I want to be able to lean on Python to perform calculation while I have a particular spreadsheet open. Maybe even insert Python code into cells. That's difficult to do with Excel. I have experimented with LibreOffice Calc, and it may be possible there.

VBA is okay for writing simple functions & macros. But I don't like using VBA for anything more complicated than that. Maybe I haven't taken enough time to learn it. But at the same time I feel like I'm creating hacks for a language which should already have the features I want. Somewhat related: you'll see people online recommending that you set your objects to Nothing in order for the garbage collection to work. I have no way of knowing if I actually have to do that and I don't like doing things for no reason.

Have you tried PYXLL (https://www.pyxll.com/)?

Is a commercial product to use Python inside Excel.

I think you have to pay for it and I work for a small business.

Do you have access to the Beta channel? https://support.microsoft.com/en-us/office/get-started-with-...

Depending on your other requirements, you may get lucky with visidata to use Python in a spreadsheet.

I would want a "zoned spreadsheet", where you can define a zone grid on each page; each row and column of the zone grid defines the number of rows and columns of data in that zone row/column as well as names for rows and columns, and each cell of the zone grid (which is named by the combination of the row zone name and column zone name) has properties such as formatting, protection, default values, etc. Formulas can go in zone cells and in data cells (although if a zone cell contains a formula, then the data cells in that zone will have the data automatically filled in and you cannot change it without removing the formula from the zone).

Another thing I wanted is to be able to write extensions in C, and to work with non-Unicode text.

Excel "tables" feature is almost what you describe as zones.

W.r.t non-unicode text: what's your use case?

I am not familiar with Excel "tables" feature, so I do not know how similar than what I intended. Is there a better explanation, available, and do other programs (hopefully, which are FOSS) implement any similar features?

Non-Unicode text, I want to be able to deal with text regardless of the encoding and character sets (although appropriate fonts will be needed in order to display it), without needing to conversion, so that any byte sequences can be used even if it is not a subset of Unicode.

extensions in WASM might probably be easier for portability

I would want to be able to use native code though (with all of the access that is possible with native code); of course such native code extensions would not be possible to add to a spreadsheet file and you must add them to a configuration file instead (both for security and to avoid unportability).

(It does not necessarily mean that WASM is not also possible. If it is, then some kinds of native extensions might be possible to convert to WASM if you want the portability and security that it involves.)

My biggest pain point is inability (as in refusal, not failure) to open files with more than N rows even if plenty of memory is available. (I forget what the exact value of N is.) Especially annoyingly, this limitation didn’t seem to exist 20 years ago.

Power Pivot Excel add-inshould be able to manage million of rows.

See https://support.microsoft.com/en-us/office/data-model-specif... and https://support.microsoft.com/en-us/office/create-a-memory-e...

First off, that article seems to be specific to web versions and pivot tables. Secondly, yeah, a few million rows isn’t that much for some use cases. I have at times needed support for mid 9 figures.

> Javascript

A quick note: your spreadsheet had better support arbitrary-precision currency math, if it doesn't already. That's table stakes for a spreadsheet.

To answer your real question, I'd suggest doubling down on the sqlite side and taking things in a relational direction. Spreadsheets are good at tracking 1:1 or 1:many relationships, but my spreadsheets inevitably grow in a many:many direction and I find myself wishing for queries and junction tables.

All major spreadsheets use double-precision floating-point throughout. I'm not sure where you got the impression they "support arbitrary-precision currency math".

In fact they even typically truncate or round the last few bits.

If you haven't already I'd recommend this blog post that was shared awhile ago by a devs experience building "Excel for Uber"[1]. Not sure if there's overlap here with their experience, but its an interesting read nonetheless.

[1]https://basta.substack.com/p/no-sacred-masterpieces

Discussed here:

I built Excel for Uber and they ditched it - https://news.ycombinator.com/item?id=37527720 - Sept 2023 (535 comments)

When exporting an Excel doc, for example to CSV, the app makes the new file (CSV) the active document.

This has serious consequences if I continue to make changes in the new doc when I think I'm still in the native doc (xlsx).

I find myself exporting to CSV, and then closing this CSV file, and finally reopening the original Excel doc.

all I've wanted for years now is to subdivide a cell and still be able to reference and style the resultant subsheet without weird embed things, just using the standard formatting stuff. merging cells is a poor substitute and breaks tabular data naming in excel at least

Reply on news.ycombinator.com