4 ms·

What about strongly-typed spreadsheets? I’d kill for a strongly-typed spreadsheet. So many literal million-dollar mistakes are caused by typing (not “typing”) e

What about strongly-typed spreadsheets? I’d kill for a strongly-typed spreadsheet. So many literal million-dollar mistakes are caused by typing (not “typing”) errors in spreadsheets, and Excel makes it easy to do the wrong thing. Ad-hoc SQL workday be nice too. PowerQuery is nice (great, even) but it doesn’t feel like it’s a part of Excel - and while it’s language is better than SQL, I really don’t want to have to learn a whole new syntax to express the same simple ideas.

prepend 6y ago
I try to make my analyses in something reproducible and then have it “still work” in Excel. So the strongly typed stuff is done elsewhere. It’s funny though because so many people who crank on excel don’t know what types are. And don’t know what variables are. And don’t have the layers of mental abstraction that programmers who want types have and need. So the expected protections don’t really happen. So while it’s nice to not convert “01” to “1” automatically, lots of errors will probably be caused by people trying to add “01” and “02” and getting “0102” and being super confused.
- gugagore 6y ago
  I didn't realize you can do string concatenation with + .
  - prepend 6y ago
    You can’t in excel, but I was referring to if strong types the language could understand what to do with “+” for strings so could concat instead of fail.
    - DaiPlusPlus 6y ago
      I'd prefer it to fail rather than overload the `+` operator for unrelated types. VB gets few things right, but of the things it does get right: using the `&` operator for string concatenation makes far more sense than overloading `+`.
- IggleSniggle 6y ago
  See also: why JavaScript is the way it is.
hhh 6y ago
During a massive migration to a different device management service I opened my CSV and saved it as xlsx as the site didn't support csv. Instantly 12% of the serial numbers had their leading zeroes removed and needed a corrective action. ~1800 devices.
- blacktriangle 6y ago
  Excel "helpfully" auto formatting zip codes, part numbers, etc with leading 0s into integer columns deserves to replace null references as the billion dollar mistake.
  - earthboundkid 6y ago
    They remained a couple of genes because Excel routinely mangles the names into dates. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
- aketchum 6y ago
  Hexidecimal values will often get converted to scientific notation if there is an "e".
mbesto 6y ago
Isn't Airtable effectively a strongly-typed spreadsheet? It forces you to chose the 'field type' for everything.
- chrisdone 6y ago
  Airtable is strongly typed in the fields of the tables, but the expression language is not. It is my conclusion based on researching Airtable, that the decision to have well typed tables is so that relational operations work nicely, and so that they can implement interfaces on top of these tables easily, such as calendars and work planning things etc. I don’t think that there is an underlying motivation to fix the ill-type code of the world, so to speak. Any non-trivial code work is done in JavaScript, anyway.
  - DaiPlusPlus 6y ago
    > Any non-trivial code work is done in JavaScript, anyway. JavaScript is strongly-typed though, it just lacks a way to declare types. One can easily restrict types in script code (using `typeof` for primitives and `instanceof` for object prototypes). People refer to it as "weakly-typed" because most (but not all) of the built-in types have implicit conversions to other types; and many of which are non-obvious: https://github.com/denysdovhan/wtfjs https://github.com/denysdovhan/wtfjs
chrisdone 6y ago
I commented elsewhere in this submission that I am working on a strongly typed spreadsheet. (Don’t want to spam my product link.) I do think that bringing new syntax to the table is justifiable if it brings new concepts with it. I agree, power query is essentially a separate product and doesn’t feel first class. I think that probably explains its relative obscurity, despite being highly powerful.

prepend 6y ago

I try to make my analyses in something reproducible and then have it “still work” in Excel. So the strongly typed stuff is done elsewhere. It’s funny though because so many people who crank on excel don’t know what types are. And don’t know what variables are. And don’t have the layers of mental abstraction that programmers who want types have and need. So the expected protections don’t really happen. So while it’s nice to not convert “01” to “1” automatically, lots of errors will probably be caused by people trying to add “01” and “02” and getting “0102” and being super confused.

gugagore 6y ago
I didn't realize you can do string concatenation with + .
- prepend 6y ago
  You can’t in excel, but I was referring to if strong types the language could understand what to do with “+” for strings so could concat instead of fail.
  - DaiPlusPlus 6y ago
    I'd prefer it to fail rather than overload the `+` operator for unrelated types. VB gets few things right, but of the things it does get right: using the `&` operator for string concatenation makes far more sense than overloading `+`.
IggleSniggle 6y ago
See also: why JavaScript is the way it is.

hhh 6y ago

During a massive migration to a different device management service I opened my CSV and saved it as xlsx as the site didn't support csv. Instantly 12% of the serial numbers had their leading zeroes removed and needed a corrective action. ~1800 devices.

blacktriangle 6y ago
Excel "helpfully" auto formatting zip codes, part numbers, etc with leading 0s into integer columns deserves to replace null references as the billion dollar mistake.
- earthboundkid 6y ago
  They remained a couple of genes because Excel routinely mangles the names into dates. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
aketchum 6y ago
Hexidecimal values will often get converted to scientific notation if there is an "e".

mbesto 6y ago

Isn't Airtable effectively a strongly-typed spreadsheet? It forces you to chose the 'field type' for everything.

chrisdone 6y ago
Airtable is strongly typed in the fields of the tables, but the expression language is not. It is my conclusion based on researching Airtable, that the decision to have well typed tables is so that relational operations work nicely, and so that they can implement interfaces on top of these tables easily, such as calendars and work planning things etc. I don’t think that there is an underlying motivation to fix the ill-type code of the world, so to speak. Any non-trivial code work is done in JavaScript, anyway.
- DaiPlusPlus 6y ago
  > Any non-trivial code work is done in JavaScript, anyway. JavaScript is strongly-typed though, it just lacks a way to declare types. One can easily restrict types in script code (using `typeof` for primitives and `instanceof` for object prototypes). People refer to it as "weakly-typed" because most (but not all) of the built-in types have implicit conversions to other types; and many of which are non-obvious: https://github.com/denysdovhan/wtfjs https://github.com/denysdovhan/wtfjs

chrisdone 6y ago

I commented elsewhere in this submission that I am working on a strongly typed spreadsheet. (Don’t want to spam my product link.) I do think that bringing new syntax to the table is justifiable if it brings new concepts with it. I agree, power query is essentially a separate product and doesn’t feel first class. I think that probably explains its relative obscurity, despite being highly powerful.