Microsoft Adds Support for JavaScript Functions in Excel

El_Heffe

https://www.bleepingcomputer.com/news/microsoft/microsoft-adds-support-for-javascript-functions-in-excel/

"Office developers have been wanting to write JavaScript custom functions for many reasons," Microsoft says, "such as: (1) Calculate math operations, like whether a number is prime. (2) Bring information from the web, like a bank account balance. (3) Stream live data, like a stock price."

https://i.imgur.com/DHrOyTN.gif

https://i.imgur.com/e31fkco.gif

https://i.imgur.com/6K4BKtY.gif

masonwheeler

@el_heffe I see no way this could possibly end up doing anything horrible or being a malware vector...

TimeBandit

@masonwheeler Add to this that they consider adding Python

https://www.bleepingcomputer.com/news/microsoft/microsoft-considers-adding-python-as-an-official-scripting-language-to-excel/

What could possibly go wrong

mott555

Node.js web server hosted in Excel!!

masonwheeler

@mott555 Excel hosted in Node.js web server!!

Erufael

Does this mean NodeBB could use Excel as a database...

TimeBandit

@erufael And since NodeBB could be hosted in Excel, imagine the performance gain by hosting both in the same Excel workbook

blakeyrat

For 20 years, people bitch about only being able to use VBA in Excel
Excel adds support for a new language
People bitch about that

TimeBandit

@blakeyrat said in Microsoft Adds Support for JavaScript Functions in Excel:

Excel adds support for a new language

Why JavaScript?
Why not C#?

blakeyrat

@timebandit Because 2018 Microsoft is full of the kind of idiot open source developers who think Node.JS and NPM is the solution to all the world's problems.

Sumireko

Awesome, now Excel can be abused even more to be a database

HardwareGeek

@timebandit said in Microsoft Adds Support for JavaScript Functions in Excel:

@masonwheeler Add to this that they consider adding Python

https://www.bleepingcomputer.com/news/microsoft/microsoft-considers-adding-python-as-an-official-scripting-language-to-excel/

What could possibly go wrong

Well, given the choice of VBA, JS, or python, python would be the least bad choice.

LB_

@timebandit said in Microsoft Adds Support for JavaScript Functions in Excel:

Why JavaScript?

Because we can't have any sane languages in Excel, that would be heresy.

pie_flavor

=JSEVAL($A2)

Benjamin Hall

@hardwaregeek Honestly, Fortran would be better than VBA in my eyes. Not by much, and only the more recent versions, but...

Despite my worries about security, I'd rank them as python >> JS >>>>>>>>>...> VBA.

bb36e

@benjamin-hall said in Microsoft Adds Support for JavaScript Functions in Excel:

Fortran

I'm a lazy asshole so I haven't bothered clicking the link, but does anyone know how the JS interpreter would deal with numbers? I'm like 30% sure Excel doesn't use IEEE754 floats, so there has to be some way to bridge the gap between JS numbers and Excel numbers.

anotherusername

@timebandit said in Microsoft Adds Support for JavaScript Functions in Excel:

@blakeyrat said in Microsoft Adds Support for JavaScript Functions in Excel:

Excel adds support for a new language

Why JavaScript?
Why not C#?

If you're writing C#, you should be using a real database rather than Excel.

anotherusername

@bb36e well, they definitely aren't using fixed point for currency.

antiquarian

@erufael said in Microsoft Adds Support for JavaScript Functions in Excel:

Does this mean NodeBB could use Excel as a database...

How do you know it isn't?

@benjamin-hall said in Microsoft Adds Support for JavaScript Functions in Excel:

Honestly, Fortran would be better than VBA in my eyes. Not by much, and only the more recent versions, but...

Or they could switch to Ada and no one would ever use it. Problem solved.

dkf

@bb36e said in Microsoft Adds Support for JavaScript Functions in Excel:

I'm like 30% sure Excel doesn't use IEEE754 floats

I'm pretty sure they are doing, somewhere (having hardware support for your fractional number system helps a lot with performance) but they might've wrapped them up inside objects.

cartman82

Hey Microsoft, how about you first allow me to write formulas in a multiline editor, then we can talk about javascript?

Dreikin

@antiquarian said in Microsoft Adds Support for JavaScript Functions in Excel:

@erufael said in Microsoft Adds Support for JavaScript Functions in Excel:

Does this mean NodeBB could use Excel as a database...

How do you know it isn't?

@ben_lubar we have a new project for you.

blakeyrat

@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:

Hey Microsoft, how about you first allow me to write formulas in a multiline editor,

What's stopping you from doing it now?

Tsaukpaetra

@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:

Hey Microsoft, how about you first allow me to write formulas in a multiline editor, then we can talk about javascript?

You... can?

0_1525841299271_d8aac649-17ea-4fbd-a29c-3f38249834a5-image.png

cartman82

@blakeyrat said in Microsoft Adds Support for JavaScript Functions in Excel:

Hey Microsoft, how about you first allow me to write formulas in a multiline editor,

What's stopping you from doing it now?

Last time I tried messing with that, it was a miserable experience.

Trying it over teamviewer now. Nothing I press will add a newline to the formula textbox.

Luhmann

@cartman82
the down arrow at the right of the formula box wasn't an obvious thing to click?

0_1525851994066_0f2ee540-8493-4bad-96b4-bb834c59cc8f-image.png

Edit: or right-click in the formula bar ... there is an expand option in the quick menu

Onyx

@bb36e said in Microsoft Adds Support for JavaScript Functions in Excel:

@benjamin-hall said in Microsoft Adds Support for JavaScript Functions in Excel:

Fortran

I'm a lazy asshole so I haven't bothered clicking the link, but does anyone know how the JS interpreter would deal with numbers? I'm like 30% sure Excel doesn't use IEEE754 floats, so there has to be some way to bridge the gap between JS numbers and Excel numbers.

Never mind that, I'm waiting for someone to try and handle dates!

Filed under: Not sure whether Excel or JS handle it worse...

cartman82

@luhmann said in Microsoft Adds Support for JavaScript Functions in Excel:

the down arrow at the right of the formula box wasn't an obvious thing to click?

Nope. On my wide screen, that arrow is waaaay to the right and looks basically like a "command history" kind of UI (eg. address bar in Chrome). There is no indication this will allow you to add lines. Even when you expand it, pressing ENTER still does the same thing as before, so there is no obvious benefit. If I didn't know new lines are possible, I might have just collapsed it back and never tried it again.

The point is, every time I get Excel sheet with formulas made by business people, they obviously have NO idea they can format text into multiple lines. Their formulas are all single line unreadable LISP-like hell. Just stacked braces and copy pasted expressions galore (here's another thing - how about multiple statements and variables?)

MS should work more on what their users actually use and where they are failing. Javascript is nice, but it's not gonna matter much to people who are actually Excel's core userbase.

Steve_The_Cynic

@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:

Hey Microsoft, how about you first allow me to write formulas in a multiline editor, then we can talk about javascript?

This sort of error is the Dark Side of in-cell editing. Use the Forcemula Bar, Luke!

I mean, I get why people like in-cell editing (for me it's one of those "meh" features that I use because it''s there, not because it's actually a UX improvement), but it isn't a universally good thing.

Steve_The_Cynic

@el_heffe said in Microsoft Adds Support for JavaScript Functions in Excel:

https://www.bleepingcomputer.com/news/microsoft/microsoft-adds-support-for-javascript-functions-in-excel/

"Office developers have been wanting to write JavaScript custom functions for many reasons," Microsoft says, "such as: (1) Calculate math operations, like whether a number is prime. (2) Bring information from the web, like a bank account balance. (3) Stream live data, like a stock price."

All of those things can be done either directly with cell formulas (the third can be done fairly easily with DDE, although that is a bit 1993...) or with VBA.

My vote for the reasons why developers want this?

They are stupid.
They are idiots.
They are stupid idiots.

On the question of IEEE floats in Excel... Possibly not, or at least not in the file format. What FP format did Excel for 68K Macs use? (I would, however, expect that "ordinary" FP data in memory uses the IEEE formats.)

dkf

@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:

Possibly not, or at least not in the file format.

The XLSX would… except the pure numbers there are almost certainly human-readable text (inside XML inside a ZIP).

marczellm

@steve_the_cynic said in Microsoft Adds Support for JavaScript Functions in Excel:

All of those things can be done either directly with cell formulas (the third can be done fairly easily with DDE, although that is a bit 1993...) or with VBA.

Both of which are completely unreadable and horrible to write.

Scarlet_Manuka

@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:

Nothing I press will add a newline to the formula textbox.

Alt+Enter

pie_flavor

@marczellm More so than JavaScript?

Onyx

This reminded me of this... "beauty" I wrote to deal with Excel's dates:

$this->dateTimeFormatter = new \IntlDateFormatter($_SESSION['locale'],
    \IntlDateFormatter::SHORT, \IntlDateFormatter::MEDIUM);

/* snip */

$dateTimeFormat = $this->dateTimeFormatter->getPattern();

// Convert to Excel's special snowflake format
$dateTimeFormat = preg_replace('/([^y])y([^y])/', '$1yyyy$2', $dateTimeFormat);
$dateTimeFormat = preg_replace('/(\W)a(\W|$)/', '$1AM/PM$2', $dateTimeFormat);

$this->dateTimeFormatter->setPattern($dateTimeFormat);

TimeBandit

@anotherusername said in Microsoft Adds Support for JavaScript Functions in Excel:

If you're writing C#in any language, you should be using a real database rather than Excel.

FTFY

cheong

@blakeyrat said in Microsoft Adds Support for JavaScript Functions in Excel:

For 20 years, people bitch about only being able to use VBA in Excel

Humm... That's new to me.

For 20 years, anyone who need to do intra-workbook programming should have already learnt VBA. And anyone who have yet to learn probably never need to do that.

Onyx

@cheong said in Microsoft Adds Support for JavaScript Functions in Excel:

For 20 years, anyone who need to do intra-workbook programming should have ~~already learnt VBA. And anyone who have yet to learn probably never need to do that~~stopped and thought long and hard about what they are doing.

FTFY

Gąska

@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:

human-readable text (inside XML

You contradict yourself.

boomzilla

@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:

@bb36e said in Microsoft Adds Support for JavaScript Functions in Excel:

I'm like 30% sure Excel doesn't use IEEE754 floats

I'm pretty sure they are doing, somewhere (having hardware support for your fractional number system helps a lot with performance) but they might've wrapped them up inside objects.

They're probably using VARIANTs, which can be normal floating point stuff. At least, that's how stuff gets exposed via COM. I don't know if they put the variant wrapper just for that stuff (which seems to be what VBA uses, just like any external consumer) or if the main program does, too.

Tagged union - Wikipedia

They could also be VT_DECIMAL under the covers, though. I guess someone with Excel and something that already does COM could take a peek. Been a while and I don't have anything handy for investigating.

boomzilla

@cartman82 said in Microsoft Adds Support for JavaScript Functions in Excel:

here's another thing - how about multiple statements and variables?

Uh, that's what cells are, dude.

heterodox

@dkf said in Microsoft Adds Support for JavaScript Functions in Excel:

I'm pretty sure they are doing, somewhere (having hardware support for your fractional number system helps a lot with performance) but they might've wrapped them up inside objects.

Am I the only one here who's optimistic and thinks they could be using an arbitrary precision library?

HardwareGeek

@onyx said in Microsoft Adds Support for JavaScript Functions in Excel:

I'm waiting for someone to try and handle dates!

Make sure you get affirmative consent first before handling your date, or you may be accused of sexual assault.

boomzilla

@heterodox said in Microsoft Adds Support for JavaScript Functions in Excel:

Am I the only one here who's optimistic and thinks they could be using an arbitrary precision library?

I'm sure they could be:

[MS-MCIS]: DECIMAL

The DECIMAL structure is used to represent an exact numeric value with a fixed precision and fixed scale. When vType is set to VT_DECIMAL

However:

Numeric precision in Microsoft Excel - Wikipedia

Accuracy within VBA

Although Excel nominally works with 8-byte numbers by default, VBA has a variety of data types. The Double data type is 8 bytes, the Integer data type is 2 bytes, and the general purpose 16 byte Variant data type can be converted to a 12 byte Decimal data type using the VBA conversion function CDec.[16] Choice of variable types in a VBA calculation involves consideration of storage requirements, accuracy and speed.

hungrier

@boomzilla
0_1525873295689_22726360-884e-4459-8c36-08fb0a98ab21-image.png
0_1525873310175_ffb56c62-d9b1-4a5b-8abf-8b2e5a53e61a-image.png

mott555

@hardwaregeek said in Microsoft Adds Support for JavaScript Functions in Excel:

@onyx said in Microsoft Adds Support for JavaScript Functions in Excel:

I'm waiting for someone to try and handle dates!

Make sure you get affirmative consent first before handling your date, or you may be accused of sexual assault.

Sexual assault is actually a pretty reasonable description of how Excel handles datetimes.

Onyx

@boomzilla said in Microsoft Adds Support for JavaScript Functions in Excel:

Accuracy within VBA

Although Excel nominally works with 8-byte numbers by default, VBA has a variety of data types. The Double data type is 8 bytes, the Integer data type is 2 bytes, and the general purpose 16 byte Variant data type can be converted to a 12 byte Decimal data type using the VBA conversion function CDec.[16] Choice of variable types in a VBA calculation involves consideration of storage requirements, accuracy and speed.

Recently, I got an Excel where some somehow managed to store two phone numbers into a single cell. Those values were (presumably) parsed into numbers even before that, and then they inserted it into Excel without spaces, commas, anything. The result was "phone numbers" in the form of 9.12E+17, which you could expand, yes, but due to loss of precision the second number was unsalvageable, even manually, because loss of precision ate the last few digits.

I know this technically has nothing to do with the thread, but fuck it, needed to get this off my chest.

heterodox

@boomzilla Ugh. I hate it, but am not surprised, when my optimism is proven wrong. :(

boomzilla

@onyx said in Microsoft Adds Support for JavaScript Functions in Excel:

The result was "phone numbers" in the form of 9.12E+17, which you could expand, yes, but due to loss of precision the second number was unsalvageable, even manually, because loss of precision ate the last few digits.

Ah, yes, I'm sure many of us have been bitten by Excel's automatic conversion to scientific notation.

HardwareGeek

@mott555 said in Microsoft Adds Support for JavaScript Functions in Excel:

@hardwaregeek said in Microsoft Adds Support for JavaScript Functions in Excel:

@onyx said in Microsoft Adds Support for JavaScript Functions in Excel:

I'm waiting for someone to try and handle dates!

Make sure you get affirmative consent first before handling your date, or you may be accused of sexual assault.

Sexual assault is actually a pretty reasonable description of how Excel handles datetimes.

I think I'd call it more "aggravated assault." Sexual assault sounds rather more fun.

Although we should really just call Excel's date handling what it is, date rape.