
Excel Adds JavaScript and Power BI Support - pjmlp
https://dev.office.com/blogs/azure-machine-learning-javascript-custom-functions-and-power-bi-custom-visuals-further-expand-developers-capabilities-with-excel
======
Daiz
A bit surprised that there's no TypeScript support, especially considering how
verbose metadata you have to write as a result[1]. That whole thing could be
made much simpler with some TS and JSDoc, eg.

    
    
        /**
         * adds 42 to the input number
         * @param a the first number to be added
         * @param b the second number to be added
         */
        function ADD42(a: number, b: number): number {
          return a + b + 42;
        }
    

[1] [https://docs.microsoft.com/en-us/office/dev/add-
ins/excel/cu...](https://docs.microsoft.com/en-us/office/dev/add-
ins/excel/custom-functions-overview)

~~~
chatmasta
Any reason you can’t just use TS and compile to JS?

~~~
chaostheory
That's not realistic unless you don't plan on maintaining the code. Even if
you do have a process to constantly move compiled Javascript into Excel, let's
say you lose a minute each time (which is really generous as the code grows).
Assuming you make 60 changes a month, you could lose 12 hours of time per year
just because TS support isn't built into Excel.

~~~
fiatjaf
I think there's also a case for supporting every other language that compiles
to JavaScript, since users of these languages will also lose 12 hours per
year.

Actually, every other language in the world besides JavaScript should be
supported.

------
chatmasta
Microsoft has been doing great work in opening up their software the past
couple years. For anyone interested in this, I suggest taking some time to
read through Microsoft's Office Platform docs [0]. Quite a lot is possible.
The plugin API is really clean and you can create javascript plugins for any
office product, where you basically run a "website" in a frame within the
office product, and the code in the "website" has access to the office API.

I haven't used it myself yet but am planning on using it in a project I'm
working on currently. I'm building a questionnaire creator that can auto merge
into docx files by using comments as merge keys (you highlight where you want
to merge and leave a comment, then in the web UI where you build the
questionnaire, you associate each form field with a comment in the uploaded
docx file). At some point I would like to convert this to use the office API,
which would enable a lot of cool features for converting a docx form into a
questionnaire.

[0] [https://msdn.microsoft.com/en-
us/office/office365/howto/plat...](https://msdn.microsoft.com/en-
us/office/office365/howto/platform-development-overview)

~~~
gordon_freeman
So my question is how this integrates with their online version of Excel? For
example: I build some custom JS based functions in Excel and used them all
over my spreadsheet then I upload it to OneDrive and trying to open it in
online Excel form, will it work? That is key for someone like me who creates
sheet offline but then wants to consume the data via online version.

~~~
msaunders77
As the PM for this feature I'm happy to answer that it works in Excel Online
starting today in the developer preview!

Right now developers have to sideload the add-in manually. But when the
feature ships publicly, the add-in that has the custom JS functions will be
deployed to a "catalog". The various Excel platforms (like Excel for Windows,
Mac, and Excel Online) will all be able to access that catalog automatically
to run the same functions because a pointer to the add-in gets persisted in
the xlsx file.

That's a little different from VBA UDFs, which get stored in the file itself.
But one advantage of our new model is that it will be way easier for
organizations to manage and maintain their JS functions, compared to VBA.

~~~
govert
It would be wonderful if it were possible to integrate with this 'pointer'
mechanism in a custom way for .xll add-ins too, like those made in C/C++, .NET
(with Excel-DNA), Python (with PyXLL) or D (with excel-d)? There's a huge
ecosystem of Excel add-ins that could benefit by access to just a few of the
hooks being build for the JavaScript support.

------
rb808
VBA is well obsolete by now. I was really hoping they'd add C# or python to
Excel. Yes you can use VSTO to write modules but that isn't the same - you
can't send a spreadsheet with C# embedded like you can with VBA.

As a result I'm using Python and Jupyter notebooks now, not as friendly as
Excel but at least its modern and there are lots of libraries out there to
make the platform super powerful.

~~~
cm2187
I'd suggest ExcelDNA rather than VSTO. But that doesn't help with your main
point (nothing to install or support, you can extend the spreadsheet without
having to compile or install anything) which I completely agree with.

~~~
rb808
Agreed, thanks I do use ExcelDNA, it is much better than VSTO. But really I've
given up on Excel now.

~~~
p3llin0r3
What do you use as a replacement?

------
riskneutral
VBA wasn’t bad enough, now millions of analysts will have to contend with
spreadsheet spaghetti and JavaScript.

~~~
sevensor
I'm thinking of all the places where I've seen Excel used in operations. Often
it's the only software the company uses. Now we're going to get people who've
done a little javascript in college using it to elaborate the Spreadsheet That
Runs Our Business. In all likelihood, the resulting chimera will make an
impenetrable tangle of VBA and Javascript.

~~~
TuringTest
So, when are you developers going to build an End-User Development platform
with decent engineering practices, to replace Excel for business users?

~~~
zimablue
It's called Jupyter

~~~
TuringTest
Jupyter has too much syntax for end users, and its execution model (having to
run cells in order to prepare the state for executing other dependent cells)
is not intuitive.

Also, installing it is not exactly user friendly. Heck, you have to open a
terminal to launch it!

------
sp527
I've been using JS in Google Sheets, so this is a welcome addition. Honestly,
assuming you're already a software engineer, JS functions basically enable you
to be a wizard in Excel. Haven't even been at it that long and I've already
managed to impress some of my friends in finance with what I was doing in
Sheets.

This is a huge game-changer and it also introduces the opportunity for
enterprising (pun?) software developers to be a lot more valuable to a
business by moving core business logic out of VBA into git-managed JS and
making it versioned, highly reusable, etc. Going a bit further, I see an
avenue to offload heavy compute to servers and create a usable link between
Excel and e.g. Cassandra/Spark/HDFS.

You have to appreciate just how fundamental Excel is to businesses to see the
size of the opportunity Microsoft just gifted to developers.

------
sevensor
> Office developers have been wanting to write JavaScript custom functions for
> many reasons, such as:

> • Calculate math operations, like whether a number is prime.

It saddens me that this is a thing we want Javascript for.

So anyway, now we have XLL, COM, VBA, and Javascript. All of them different
but overlapping ways to extend what Excel can do. Am I missing any?

~~~
Maybestring
Power Query M

~~~
sevensor
My, that's nifty! Sad I've overlooked it, happy that it exists.

------
cremp
I can't wait to see how this is exploited in a few hours...

~~~
redleggedfrog
I think voting this down is unfair. That's the first thing I thought of when
they mentioned "bank balance." Well, then there's credentials. Now it's
complicated, and complicated and Javascript are a recipe for disaster.

------
johnklos
This will go absolutely wonderfully for Microsoft. Their track record with
security is impeccable and they've always had great success intermixing data
and executables with no negative consequences.

------
cm2187
My first question is will we be able to use wasm instead of javascript? That
would open excel not only to javascript (which people may or may not like) but
pretty much to any other language as long as someone builds a wasm
runtime/compiler to (which seems to be on the radar of lots of people,
starting with the .net team). Would also make more sense to me from a
performance point of view.

~~~
icebraining
It's already done; compile to wasm, then compile that to JavaScript using
wasm2asm :)

More realistically, many languages can already be compiled to JS, you might
not need wasm.

------
45h34jh53k4j
This is a horrible horrible idea, and will end in tears. We need less active
scripting in documents, not more. :-(

Example: coinhive monero miner distributed in xls:
[https://twitter.com/CharlesDardaman/status/99391267580461465...](https://twitter.com/CharlesDardaman/status/993912675804614657)

------
Ftuuky
But no Python support as they promised. So sad.

~~~
bakery2k
One issue with embedding Python is that it's difficult to sandbox - to
securely limit what the embedded runtime, and hence (potentially malicious)
custom functions, can do:

> [The Python developers'] standard answer to "How do I sandbox Python code?"
> has been "Use a subprocess and the OS provided process sandboxing
> facilities" for quite some time. [1]

JavaScript, OTOH, is designed to support secure in-process sandboxing. Other
languages with such support do exist (e.g. Lua), but JavaScript is by far the
most widely known.

[1] [https://mail.python.org/pipermail/python-
dev/2013-November/1...](https://mail.python.org/pipermail/python-
dev/2013-November/130145.html)

~~~
brudgers
Good point. Another architectural difference between the languages is that
Javascript is a [relatively] small language with a long history of working
with external API's [i.e. the browser]. On the other hand, Python has
extensive standard libraries that would require alignment with Excel. For
example, how does csv.reader integrate? It looks like a can of "do the obvious
thing" worms and a mountain of unexpected results and documentation.

Mostly, I think it comes down to Python being designed as a systems language
rather than a scripting language. Integrating Python would seem to mean either
a weak security model or a special (subset) version of Python. Neither is
really going to meet the fat part of the Bell Curve...people who just want to
get things done in Excel. Applying a 'browser abstraction' to Excel is
probably better than applying an 'OS abstraction'. Anyway, JS has been a part
of .NET and VS since JScript. Python, not so much.

~~~
bakery2k
> Python being designed as a systems language rather than a scripting language

Interesting - I hadn't really noticed how pronounced this dichotomy within
dynamic languages is. On the one hand there are small languages designed for
embedding and sandboxing (e.g. JavaScript, Lua, Tcl) and on the other hand,
larger, more general-purpose languages (Perl, Python, Ruby).

I always assumed that a dynamic language could work well in both contexts, but
in fact, most lie fundamentally on one side of the divide or the other. Only
JavaScript, due to its immense popularity, has really managed (with Node.js)
to expand from the first category into the second.

If Python were to be embedded in Excel, it would be expanding from the second
category into the first. As you mention, to do this safely it may be necessary
to create a special (subset) version of the language. Matz, the creator of
Ruby, is trying to take his language in this direction with mruby [1] - a
"lightweight implementation of Ruby complying to (part of) the ISO standard".

But, will these subset versions ever be popular? They necessarily leave the
majority of the language's ecosystem behind - and knowledge of the full
language will not necessarily transfer directly to the subset. Can a subset of
an existing general-purpose language, even a widely-known one, compete against
other languages that are specifically designed for safe embedding?

More generally, is it possible for a dynamic language to work well on both
sides of the divide, or must all (even brand-new) languages choose one side or
the other?

[1] [https://github.com/mruby/mruby](https://github.com/mruby/mruby)

~~~
brudgers
I think the more important divide between language communities may be between
consensus and individual authority. Python's BDFL and Javascript's
standardization lie at opposite ends of the spectrum. To caricaturize:
Javascript is the intersection of what many interested parties can agree on.
Python is the union of anything that struck a single individual's fancy over
the course of decades. The rise and fall of functional programming as Pythonic
is a case study of the language community's arbitrariness (if Python2 v
Python3 wasn't enough). As an aside, Ruby avoids this because it's vision is
not tribal. Principle of least surprise allows for differences among
programmers. Pythonic/unPythonic doesn't.

------
airstrike
The real story here is being able to use D3 in Excel. My prayers have been
answered. We truly are living in the future (except in the office, where we're
stuck with Excel 2010)

~~~
mr_toad
D3.js requires a DOM, and most visualisations require a DOM and SVG rendering.
I doubt MS are implementing a DOM or SVG support inside Excel (I could be
wrong).

~~~
seanmcdirmid
Most visualizations can actually get by with just canvas. No need for the DOM.

------
polskibus
Afaik powerbi uses old version of d3 (last 3.5 version). This move will likely
make the world stuck on it forever. It is kind of sad considering that d3 is
still growing in functionalities. I wonder if MS gave this dependency more
thought and is planning to address it in some way in the future.

------
sigzero
While I am not a fan, I see this a "potential" win for those running Office on
Macs.

------
ww520
Wait. Isn't JScript a supported COM Automation script for the longest time?
Excel is a COM Automation host. JScript just like VBA can run inside it and
access pretty much all its API.

~~~
seba_dos1
This is about custom functions in cells, not macros.

------
andyjpb
The Pentium FDIV bug aside, Excel has always been pretty good at maths. I
wonder if there will be a new class of maths bugs as they integrate with the
strange JS numbers model?

------
polskibus
What's the best way to reuse those js visualisations from powerbi elsewhere?
Has anyone tried it? Is the model well documented?

~~~
1812Overture
They're all in D3.js and available on GitHub.

------
noonebuttwo
What the state excel and R working together?

------
rbonvall
The examples in the repo are all ES5. Does someone know if it supports ES6?
There's no mention in the article.

------
megaman22
Yikes, I hope people won't be doing any financial calculations in Excel JS.
But I know they will.

~~~
cm2187
They will use what choice microsoft gives them. Unfortunately that choice
seems to be between a 20 year old language (VB6) and javascript...

~~~
justherefortart
VBScript is not VB6

~~~
cm2187
VBScript isn't a scripting language of Excel. VBA is VB6 embedded in Office.

[https://en.wikipedia.org/wiki/Visual_Basic_for_Applications](https://en.wikipedia.org/wiki/Visual_Basic_for_Applications)

~~~
justherefortart
Been so long I'd blocked it out. Thanks :-)

