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;
}
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.
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...
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].
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!
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.
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.
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.
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.
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.
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?
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.
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?
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.
> 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.
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().
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.
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).
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.
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.
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?
