Formulas is a big one for me. As a programmer I often use Excel to generate insert/update SQL statements for example. So I'd need formulas with not just numerical but also string manipulation functions.

Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.

> Also filtering is essential. So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.

This is not "filtering", this is faceted search.

I am also a big fan of faceted search, and I found the occasional writing of Simon Willison’s on the topic very informative.

https://simonwillison.net/2018/Oct/4/datasette-ideas/#Facet_...

I was sloppy and meant "filtering as implemented in Excel".

But yeah great feature, and thanks for the reference.

How does this sound? Formulae are supported, but the cell (not column) must be set as the Formulae datatype. The first formula that I am supporting is SUM, as it is easy to code in SQL. What are the other most common formulae that you use? I'll only implement the integer / float formulae at first, but I'll add string manipulation later.

  > Also filtering is essential.

Filtering, of could. I've already got a hidden column for all rows isDisplay.

  > So nice to be able to see all the values in the filter dropdown, easy to quickly spot weird values.

Perhaps instead of filtering, you'd like to see outliers or the range of values?

> Formulae are supported, but the cell (not column) must be set as the Formulae datatype

As long as it's not tedious to set a few thousand that sounds great.

> What are the other most common formulae that you use?

I mostly use SUM, COUNT and IF (if-then), along with functions to check if a string value is a valid number. Also string formatting of numbers and dates (for concatenating with text).

I've also used lookups, ie find the row matching this and extract the value from the given column in that row. Though I'm not super happy with the way Excel does that, surely some room for improvement.

> Perhaps instead of filtering, you'd like to see outliers or the range of values?

In addition. Sometimes I just want to view all the values matching X, other times I want to quickly see any outliers. Definitely ranges, especially for numbers (just larger than 0 for example, or between 5 and 10).

Sounds like a very interesting project, if you got a link I'd be interested in tracking progress. If not, I'd be happy if you did a Show HN when you're ready :)

  > As long as it's not tedious to set a few thousand that sounds great.

How are you setting them? Grab and drag? I can support that.

  > SUM, COUNT and IF (if-then)

Perfect, thank you.

Right now I've started a Git repo with a roadmap but I've not uploaded any code yet as I'm still deciding on the architecture:

https://github.com/dotancohen/structuresheet

> How are you setting them?

I'm old school, so ctrl+c, shift+end (maybe tweak selection area with cursors if Excel is being dumb) and ctrl+v.

Terrific, thanks.

While I'm at it... One thing I find annoying in Excel is that it's rather tedious to reference an absolute cell ($B$2) vs a relative cell (C3), especially when I need to reference a handful of each.

The way I work, it would have been better to mark a cell itself as being a "constant" or "input parameter", so that when I reference the cell it is treated as an absolute reference when pasting.

That is, if I reference cell B2 in a formula and paste that formula in a different cell, it is B2 itself that determines if it's a relative or absolute reference, not the way I typed it in the formula.

It's not a huge deal, but it would be one of those nice things that makes life more enjoyable.

Would it not be tedious to mark each cell as constant or input parameter? Perhaps it would be preferable to just ask the fine user when copying a cell with a formula?

I almost always have <5 such cells in a sheet, typically just 2-3, so I don't think it would be that tedious given you only have to set that once.

In comparison, I often have formulas with over 20 cell references and/or multiple formulas referencing the "constants", so getting asked for each would indeed be tedious.

That’s awesome! Where can we subscribe for any announcement? ;)

To me, a critical one is easy plots. Checking how an arbitrary column changes as a function of an arbitrary other column by adding a scatter plot in less than 5 seconds is fantastic.

Formulae are also very useful. Adding numerical derivatives or integrals by just putting a formula in a new column is very useful as well. The point is not to have publication-quality, highly accurate numbers, but just quick and dirty operations to see if it warrants further investigation.

I am happy to discuss my use cases if you are interested (it might be going a bit out of topic for this thread).

For right now I've started a Git repo but not uploaded any code until I decide on an architecture.

https://github.com/dotancohen/structuresheet

I'll see what cross-platform plotting libraries I can include. Thank you for the idea, I agree it is important. Yes, I am very interested in your use cases! My Gmail username is the same as my HN username.

I'm curious, how do you achieve a strongly-typed database with sqlite?

sqlite's lack of verifying that your data fits in your defined schema is by far my biggest problem with sqlite.

SQLite has a powerful data-validation function for each column, look for CHECK Constraints: https://www.sqlite.org/lang_createtable.html

But the real nice thing is that SQLite will allow spreadsheet-like behaviour by allowing one to enter an invalid data type. I could check that in Python or I can store it and warn "Invalid type blah blah blah". This will make life easier for those coming from a spreadsheet, or importing data. As the program matures, I can reevaluate what to do with invalid data as a default and what options to give the user.

Additionally, because SQLite allows arbitrary data in any column, adding support for e.g. formulae is greatly simplified.

SQLite fixed that a few releases ago with STRICT tables: https://www.sqlite.org/stricttables.html

check (typeof(column) = 'integer')

> strongly-typed database

Python / Qt / SQLite

I don't see any part that gives a strongly-typed properties.

Do I miss something?