The news that Microsoft is adding Javascript support to Excel is important for developers and regular Excel user, but also a security concern.
Javascript is a programming language that’s used on many web pages to provide the fast interaction we’re used to. Javascript is able to get data from external sources and display in Excel faster than VBA or ‘Get and Transform’.
Office Insiders can try Excel Javascript now and it will be released to Office 365 customers in the future. There’s no public release date but it’ll probably be this year.
Why add Javascript to Excel?
Excel already has two programming languages, VBA and ‘M’ in PowerQuery so why add Javascript?
The main reason is portability. Javascript can be easily added to Excel for Windows, Mac and Online because the main Javascript engine is already available. We say ‘easily’, it’s still difficult, but a lot easier than recreating the entire VBA system for the Mac or a browser based Excel.
Excel Mobile apps will eventually get Javascript features as well.
It doesn’t mean the end of VBA, but it does mean that many developers will prefer to write Excel extensions in Javascript because they’ll work across a wider range of Excel releases.
Many developers know Javascript already, so the move into Excel development should not be hard for them. Microsoft is hoping to entice more developers into Excel.
Sheets, Google’s spreadsheet application, already has Javascript support. Microsoft will be congratulating themselves on this Excel ‘innovation’ but they’re really playing catch-up after Google … again.
Rumor squashing
Some people hearing the words ‘Excel’ and ‘Javascript’ have jumped to the strange idea that Excel itself is being rewritten in Javascript. Nonsense.
It’s one of those supposed ‘news’ stories that’s really just a misunderstood social media posting that got some online attention for less than a day. We won’t go into details because, frankly, life’s too short to bother.
Javascript is being added to Excel as a new way to add functions and features.
Really Simple Example
Microsoft has a really simple example of a function which takes two numbers, add them together plus 42 and return the total.
Here’s the Javascript code:
function ADD42(a, b) {
return a + b + 42;
}
In Excel it appears as a custom function like this.
Source: Microsoft
Of course, you can make a custom function like that already in Excel for Windows/Mac. The Javascript version works on both those platforms plus Excel Online (which doesn’t support VBA).
Getting Data
One place Javascript should shine is getting external data into Excel. One of Microsoft’s examples is getting temperature readings from a sensor. This is possible in VBA but Javascript is more widely supported when connecting to live data sources.
Javascript will be able to get readings from a sensor or data service each second or less. Compare that with ‘Get and Transform’ which normally refreshes data every 60 seconds at a minimum.
Excel and the ‘Internet of Things’
Add Javascript into Excel means faster and easier access to small connected devices known as the ‘Internet of Things’.
In Excel with VBA or PowerQuery it was possible but difficult and slow to link with real-time sensors. Javascript should streamline the connections without slowing down Excel, thanks to magic words like Asynchronous and Streaming.
Synchronous and Asynchronous
Excel Javascript functions can be Synchronous or Asynchronous.
Synchronous functions are for fast calculations like the Add43 one above. They run in the same process as Excel to take advantage of multithreaded power.
Asynchronous functions run separate to Excel. Excel starts the JS function then get on with other tasks until the function comes back with a response (which could be a few seconds later). Functions which grab external data won’t hold up Excel in the same way that Get and Transform does now.
Streaming
Excel with Javascript will be able to ‘stream’ data into a cell without a recalculation.
The current temperature from the sensor mentioned above could be constantly updated in a cell and not just when there’s a recalculation or data refresh. That will let Excel work with live data in a way it’s not able to at the moment.
In many situations, developers have created web pages with Javascript to show real-time data faster than Excel could cope.
Getting Started
If you’re familiar with Javascript, Microsoft has information and some example functions.
Note the ‘Known issues’ at the bottom of the page. In particular, debugging of asynchronous functions is only available in Excel for Windows.
Caution, little ones
Adding a development language into Office is an opportunity … for hackers.
You can be certain that hackers all over the world are already exploring Javascript in Excel for security holes to exploit. It will be interesting to see how well Microsoft has secured this new platform beyond their usual hype and platitudes.
So far, Microsoft has been pushing the benefits of Excel Javascript with little or no mention of the security risks.
Patience, Grasshopper
Excel with Javascript will be difficult at first. There will be difficulties and problems with the initial deployments. It was the same with PowerQuery and VBA in the beginning and ‘WordBasic’ before that.
Over time, things will settle down. Better examples and templates will appear. Help pages, tutorials and books will be released.
Then it’ll be possible for more advanced Excel users to dip their toes into Javascript waters without scalding themselves!
