Excel JavaScript API Part 1: Overview & Comparison

I have been spending time in the last few months experimenting with the new JavaScript Excel API.

This is the first of a planned series of posts examining the JS-API from the point of view of an Excel Office developer.

The main advantage of the JS API is that it allows you to write code that works for Windows Excel, Mac Excel, Excel Online and Excel on IOS. The JS API is still immature but is undergoing rapid development by the Microsoft Office Extensibility team, with new releases of the API arriving roughly quarterly.

The JS API uses browser-based JavaScript/TypeScript and HTML and CSS technologies. It’s main objectives are:

• To work across such a wide variety of end-points (mobile, tablet, cloud, desktop).
• To target today’s developers who work mainly or exclusively using web technologies .

The API executes asynchronously by queuing up the API interactions with Excel until you request them to be executed in a batch (Context.Sync()).

JavaScript & TypeScript

For VBA or C developers JavaScript is a weird, exasperating but surprisingly powerful language. To mention a few differences:

• Variables don’t really have Types and are case sensitive. The scope of variables (called Lexical Scope) is seriously weird and seems to often catch out even seasoned JavaScript developers.
• Arrays are not really arrays and only have one dimension, so you quickly learn about arrays of arrays. They don’t have fixed upper bounds and can be sparse. They have powerful methods like MAP, REDUCE, SLICE, and SORT. (But its really hard to make JS array sort work the same way as an Excel sort!)
• Functions are first class citizens – you can pass them around like variables – Anonymous/Lambda functions are fully supported – you really need to get your head around this!
• Asynchronous processing is handled by things called Promises which can be chained together with some difficulty (this situation is known as the Pyramid of Doom)
• JavaScript as a language is still changing fairly rapidly and has features that may or may not be supported by particular browsers
• JavaScript’s popularity is partly bound up with and fed by the many very powerful and freely available frameworks (JQuery, AngularJS, …)

And by the way JavaScript is not a script language version of Java: it is a completely different language.

TypeScript is an attempt by Microsoft to fix some of the more egregious JavaScript failings.
TypeScript is basically a Transpiler – it compiles TypeScript into JavaScript so that just about anything that can execute JavaScript can also execute transpiled TypeScript.
Two major advantages of TypeScript are:

• You can assign Types to variables and they have sensible scope!
• You can use Async Await instead of Promises which makes your code much more readable and avoids the Pyramid of Doom.

Excel API Technologies Compared

There are 4 main families of Excel APIs:

Excel ‘C’ XLL API

This API is the most tightly integrated and lowest publicly available interface to Excel:

• Best performing API
• Low-level interface requires C programming and memory management skills.
• Visual Studio IDE
• Runs in the Excel Process.
• Most capability for UDFs (multi-threaded, asynchronous, RTD, Cluster etc)
• Only covers a subset of the Excel Object Model
• Limited native UI support
• Used as a foundation layer by many other products/platforms/languages (XLL Plus C++, Excel DNA .NET, Addin Express .NET, PyXLL Python, FCell F# …)
• Windows Desktop only
• Application addin level only
• Can use all the C and C++ frameworks (Standard Template Library, BOOST …)

Excel COM and Automation API

This API is the richest and most widely used API

• Primary languages VBA and VB6
• Good performance
• Widest object model support
• Supports single-threaded synchronous UDFs
• Runs in the Excel Process
• Windows
• Mac OS Desktops/Laptops (but VBA only with some differences to Windows VBA)
• Macro recorder provides low entry point
• Built-in but ancient IDE for Windows VBA
• Can be embedded in a document or used as an addin at application level.
• VBA largely unchanged for many years and has only a limited and dying framework

COM-Interop API

This API adds an additional .NET interop layer on top of the COM/Automation interface. Ideally this would have been a useful stopgap until a proper .NET Excel interface was developed, but sadly that never happened.

• Poor performance
• Runs in separate process
• Supports .NET framework and languages
• Windows only
• Visual Studio IDE
• Primarily application level but can be bound to a document using VSTO
• Not suitable for UDFs
• Microsoft’s offering is VSTO

Products such as Excel DNA and Addin Express support both the COM-Interop .NET world and the ‘C’ API to enable proper UDF support, improved performance whilst keeping the richness of the .NET framework.

Excel JS-API Release 1.4

The new kid on the block! This is the API that is currently being actively developed by Microsoft, so it is very much a moving target. Comments below reflect the 1.4 Version of the API as at February 2017.

• Your code runs unchanged across multiple endpoints.
  • But this can result in lowest common denominator support
• Supports the many JavaScript frameworks
• Works best with TypeScript
• Good Web Support
• Office Store support
• Runs in a separate browser process
• Requires Internet: no offline capability
• No UDF support currently
• Very poor performance
• Targeted at professional web developers
• Object model support
  • Limited but rapidly expanding
  • Single workbook only: cannot copy between, open, close or save workbooks
  • Limited control of Calculation and Screen Updating
  • No Copy Paste Special
• Hybrid Cloud/Application/Workbook Level
• Batched Asynchronous processing only

What Next?

My next blog posts on JS will focus on the performance of the JS-API.

If you want to learn more about the JS-API I recommend Building Office Addins by Michael Zlatkovsky, who is a developer on the Microsoft Office Extensibility team.