Excel Adds JavaScript and Power BI Support | Hacker News Reader

Excel Adds JavaScript and Power BI Support

dev.office.com

192 pointspjmlp103 comments

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...

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

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.

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.

If you look at their example, the "type information" is actually encoded in an ancillary JSON file: https://github.com/OfficeDev/Excel-Custom-Functions/blob/mas...

In theory, you could embed all of the metadata in JSDoc + TS annotations.

Presumably there will be type definition files available at some point. That way the solution can be maximally general: JS for people who can't use TS for whatever reason (maybe because they're a legacy Excel shop that can't introduce new technologies), TypeScript for everyone else.

Because... this is no support for TypeScript in native JS runtimes?

It would add to complexity if you introduce a compilation step before executing them in JS runtime, especially when you are targeting multiple platforms.

That's hardly a problem. Since they can run JS, they could just add the TypeScript compiler itself into Excel and compile code on demand (and cache the results).

Hi, I'm the PM for the feature. Thanks for this feedback about TypeScript support! I totally agree that TypeScript is ideal for this use case.

As other commenters have mentioned, we want to stay flexible for everyone, whether or not they use TypeScript. We'll provide support to make the TypeScript development experience great, like type definition files. You can compile any TS files to JS at development/deployment time (admittedly a little more legwork for your dev environment, yes).

Finally, we'll be releasing an update for Script Lab[1] to support custom JavaScript functions soon. And since Script Lab supports TS we've used exactly the model you suggest above to define the function metadata in Script Lab.

-Michael

[1] https://github.com/OfficeDev/script-lab/blob/master/README.m...

I'm the kind of person this is directly aimed at, finance background, Excel officionado begging for a better scripting language. I can tell you that I am not interested in js for Excel one bit. Python for Excel however...

I can't agree enough. Python baked in would be a dream come true.

We did announce (but not yet release) custom Azure Machine Learning functions in Excel. I mention in the blog that since Python runs on Azure ML, you'll be able to run custom Python functions in the cloud, as part of Excel formulas[1].

That announced Azure ML feature is optimized for machine learning models (in terms of the tools/deployment/runtime we've provided), so even though it will work for any Python functions it might not be exactly what you're asking for. Also, depending on your specific use case, you may or may not need client-side execution (Azure ML functions run in the cloud today). I can't share specifics on other unannounced features, but Azure ML functions are only our first foray in this space: we think custom functions running in the cloud are very important for many different use cases in Excel.

Also, note that on the JavaScript side, the language choice isn't something new for Excel or Office: for example, there's a public ecosystem of add-ins that automate Excel via JavaScript today[2]. Custom functions are just the latest way we've enabled for extending Excel as part of an add-in[3].

[1] https://dev.office.com/blogs/azure-machine-learning-javascri... [2] https://appsource.microsoft.com/en-us/marketplace/apps?produ... [3] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...

Have you given PyXLL a try?

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...

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.

I think the answer to that is probably in the docs, but IIRC it’s possible as long as your JS code is hosted publicly with a valid cert. Not sure though, don’t quote me on that.

Afaik the API is the same, if anything the online version gets updates earlier than installed counterparts. So yeah, it should work from the web too.

There might be additional checks if you package your solution as a proper add-in (it might have to be enabled by an O365 admin).

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.

As the PM can I beg you to look again at Python built in to replace VBA?

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.

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.

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.

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

What do you use as a replacement?

I know Python was in consideration some time ago.

https://excel.uservoice.com/forums/304921-excel-for-windows-...

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

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.

And if that works for the business... then where's the problem?

Well, on the one hand, it's not a problem. It's pure elitism to insist that laypeople learn Real Programming and make their spreadsheets the Right Way. Excel + JS is going to be an empowering tool. But on the other hand, it's going to be a nightmare to maintain when you layer it on top of VBA macros, and some poor soul is going to end up responsible for making it continue to limp along. Nobody will understand how it works, it will take hours to calculate, and it will become a drag on the business. I mean, we're there already with macros alone. Imagine if Javascript and VBA macros are allowed to coexist in the same system. It's going to happen, it's going to be somebody's problem, and I feel sorry for them.

I thought this was part of the business model. Microsoft makes bank by rolling out an "empowering" tool (think Excel Macros, Access VBA, and LightSwitch) that allows the business folks to create their own solutions. Eventually they outgrow or simply can't maintain those solutions and hire developers, whom Microsoft made bank selling them "real" dev tools, to convert them into proper applications. Rinse and repeat.

It's not as cynical as that.

Microsoft provide the right tools at the right point in the business lifecycle.

Have you ever been in a company where That Spreadsheet is rebuilt "properly" at great expense by "people who know what they are doing" and then, the first time the business wants a change it takes more than an afternoon and everyone goes back to the spreadsheet again?

Some (most?) people aren't programmers and have no choice. Microsoft lets them be the masters of their own destiny.

I don't think this worry comes out of elitism. On the contrary, I think it comes from trying to protect the business users - as messy as their tooling already is - from the JavaScript culture.

> My language has good culture, JavaScript has bad culture

I think you failed to move the goalpost away from elitism.

Well, if you want particulars - my favourite language has plenty of cultural problems, like huge NIH syndrome, or complete inability to actually finish a library (and don't get me started on documentation...). But JavaScript seems to have an unique cultural combination of "move fast and break things" coupled with complete disregard for any kind of efficiency considerations. I don't want to see the web bloat ported over to spreadsheets too.

You're making hasty generalization. JavaScript isn't one big homogeneous culture. I've never worked on a team where unnecessary library bloat and disregarding efficiency was acceptable. I've been to plenty of websites that reject that attitude. Usually businesses don't want to pay the extra dev hours (or quality dev rates) to get code optimized to my preferences.

Your technical debt is someone else's business opportunity. This is how the IT world keeps expanding, despite increasing the degree of automation available to every process.

Javascript's concatenation and equality rules alone likely will lead to your first premise being incredibly false.

It works until it doesn't then your are in trouble - oh sorry Mr tax man our excel made a mistake and you say we own you HOW MUCH due by the end of the month.

God forbid someone will figure out how to plug NPM into Excel-JS, and the bloat from the web will get ported into business spreadsheets as well.

(I guess that's one way to drive new hardware sales, though.)

It's too late. We're all doomed.

https://docs.microsoft.com/en-us/office/dev/add-ins/tutorial...

Step 1 in the official Excel Add-Ins tutorial includes installing Node and npm.

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

Agree. The choice isn't between spaghetti VBA and clean code, it is between spaghetti VBA and business users doing more copy-paste and manual work. It's not economical to have a professional development team looking at every single task that needs to be automated in a business.

Might as well ask the people running a mom and pop store in Podunk when they're going to unseat Amazon.

> replace Excel for business users

Not likely. Given the depth and breadth of its penetration, all the man-millenia that have been spent encoding business rules in Excel, I suspect that in the end Excel will outlive Windows itself, just as COBOL has outlived the mainframe era.

I'd love to see a principled approach to extending Excel, and maybe MS have already done it. There are a lot of ways to extend Excel, and given the MS practice of keeping backwards compatibility alive for decades, none of them is going away any time soon. Could be one of them is actually good. I just today learned about Power Query M, which looks promising.

It's called Jupyter

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!

That's the goal of all low-code and no-code tools. Just take a look at air-table, Mendix or out systems, for example. Gartner and forester have many more listed in their quadrants.

Having seen a VBA-powered spaghetti-monster of a spreadsheet in use, the title made me shudder in fear. I feel bad for whoever gets allocated one of these 'business-critical applications' to maintain.

VBA is good and simple enough to easily learn.

however, VBA code is also spaghetti code with global vars and <what not> mixed into it.

Source: I have debugged vba macros in excel before.

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.

> 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?

Power Query M

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

RTDServer

Thanks for this -- I have some COM automation that would greatly benefit from RTDServer, now that I know about it.

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

It might be a small comfort, but Javascript is getting arbitrary precision integer support this year:

https://github.com/tc39/proposal-bigint

VSTO

Master Data Services

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

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.

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.

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.

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.

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...

But no Python support as they promised. So sad.

They never promised Python support.

I think they were considering it, they even sent out a survey.

https://news.ycombinator.com/item?id=15927132

That's...very different from making a promise, though.

You're right. But since the feedback to their survey was so positive, I was really expecting it.

If that's true, this stuff just changed the world more than half of the political issues that people march on streets about. The brightest people in office jobs around the world that aren't well integrated with developer teams (almost all of them) just got pushed to learn javascript, puke.

I get the appeal of "DAE hate JS" on Reddit where everyone upvotes you, but it seems out of place on a forum of professionals because JS is not much different than other dynamically-typed languages. I'd say it's better than Python which doesn't have async-by-default nor idiomatic closures. But "puke" is a bit childish.

I'd kind of assume everyone on HN has made their minds up about languages and I'm not qualified to change the opinions of any of them or even really try to be informative. So the puke is just for the (70% at a guess?) people who would agree that this is a bad choice.

Since I work with both python and javascript in exactly this dirty moving data around for analysts space (but haven't for long), I'll qualify a bit.

The most important point is that python absolutely brutally dominates the data science space, there are no javascript equivalents to the python ecosystem, it's not even close.

Secondary point is that javascript is much lower level than python in a language sense. Python already has classes built in, reflection, sane typing, just tons of batteries.

Javascript as someone smarter than me said before is lisp with C syntax. It's definitely possible to build a good language out of Javascript by using the right libraries and making decisions on how you're going to implement inheritance etc but it's just tons harder than python or almost any other language which has defaults on these decisions made for you. Guys automating their data flows aren't going to be at the level to make these decisions well but they will run into the same problems they solve and implement their own insane schemes.

I'd much much rather debug amateur VBA or Python than amateur javascript.

JavaScript has every feature you listed, plus a ton.

I'll give you the batteries included part, but you're missing the critical component here, which is Python's dep mgmt story is terrible. Pyenv seems cool, but it's basically NPM - which is a critical part of what they're trying to do here.

You can definitely mess up with python envs but JS absolutely doesn't have all the features I listed.

It doesn't have anything like python's built in power of classes, it's not just having a class syntax which is a thin wrapper around prototypes, it's having metaclasses, class decorators, properties, type comparison that works for inheritance, super().

It also doesn't have sane types/casts.

JavaScript by design supports both full OOP and full functional programming. There is no reason why you cannot implement what you mentioned in JavaScript.

> python absolutely brutally dominates the data science space

The language where when you 3/2 you get integer. Try R, the language created/developed by Statisricians for Statisticians. Python doesn't even have a buil-in support for missing data.

This is out of date, 3/2 is 1.5 in latest python.

I don't know what you mean by built in support for missing data but at language level there is None and in numpy/pandas there is np.NaN

> There are many useful function types developers can enable with Machine Learning, such as:

> Any custom Python code, like a function to analyze text in cells.

Sounds like you'll probably be able to hack Excel into running whatever Python code you want using the Azure ML integrations. I guess it'll be Javascript the people currently writing VBA are pushed toward though.

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...

I think this is the real reason why we will never see Python inside Excel. The other, minor, reason is that Guido has pretty much pooped all over the idea of backwards compatibility in Python, which is pretty much integral to Microsoft's vision.

Other benefit is the web version can potentially run the javascript in the browser instead of waiting for a network call. Not sure what the security implications are here.

Didn't and doesn't stop them from using VBA, which of course is already used by malicious entities everywhere, so if they're unwilling to stop supporting that then why not let unsandboxed python work?

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.

> 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

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.

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)

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).

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

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.

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

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.

This is about custom functions in cells, not macros.

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?

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

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

What the state excel and R working together?

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

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

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

VBScript is not VB6

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

https://en.wikipedia.org/wiki/Visual_Basic_for_Applications

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

Reply on news.ycombinator.com