The Excel Javascript API

(Republished from the original Explainly blog)

This is part one, where I will discuss what the Office Javascript library is and why it matters from the context of Office's customization history and changing market dynamics.
I will link to future parts here as I post them.

Microsoft first introduced the Office Javascript API with Office 2016. Prior to Office 2016, extensions and customizations for the various Office applications were written in VBA and run internal to the application environment via macros or in VB.NET or C# and connected to the Office applications via document, template, or application-level modifications, generally called "Visual Studio Tools for Office" or VSTO extensions.

VBA [Visual Basic for Applications] was built on the back-bone of Visual Basic 6 which is a scripting language exclusive to the Windows operating systems that, outside of VBA, has not been supported by Microsoft since 2008. The VBA interface worked with Windows' Component Object Model (COM) to allow interaction with various applications outside of the local office library. This limited the full capabilities of VBA to Windows. Microsoft created a sandboxed version of the Office applications with VBA for Mac to allow for the fundamentals but without the ability to interface with other COM-based applications.

VSTO, first introduced in 2005, offered a more modern programming approach using managed code to the world of Office customization. Tools for converting VBA into VB.Net were made available with the thought that document-level customizations would gradually replace most document-specific VBA modules with application or template-level customizations supplanting the VBA add-ins and modules that impacted the applications more generally. The VSTO model was a COM-based solution, making it completely unavailable to Office for Mac. Between this lack of non-Windows support and the more structured programming environment, VSTO add-ins never reached a large audience.

With the growth of smartphones and tablets - few of which run Windows - and more and more users interacting with their spreadsheets online via web-based applications, Excel functionality became significantly less transferable. Further, Google was the clear leader in meeting many users' needs by offering easy collaboration and a simple interface early on. In 2014, Google released add-ons for Sheets and Docs, allowing users to begin writing custom JavaScript-based add-ins for their Google documents with code following documents as easily as adding VBA to an Excel file, but without quite as much functionality.

Excel for Windows might have been a powerhouse, but Windows was no longer the primary battleground for more casual users.

With Office 2016, Microsoft launched their response with their Office Javascript (Office JS) library and significant improvements to Office Online. The Javascript library offers a web-oriented language for interacting with and expanding the application. Beyond being web-based, Microsoft integrated a basic browser into their desktop applications to allow for add-ins that function across the desktop / online divide.

This system runs Javascript asynchronously alongside the Office application, offering up a whole new series of issues to handle and a fairly significant departure in programming concepts that can make porting functionality from VBA or VSTO to Javascript quite the challenging hurdle, especially in understanding the different nature of the interaction and the associated debugging. The hurdle is well worth overcoming as Javascript offers a world of libraries and systems to use such as Node JS, JQuery, Vue, and more to build rich tools that can work with services far beyond the local Office application with more ease and cross-platform support than VBA or VSTO could.

A Microsoft Garage project, Script Lab, offers a way to write, edit, and test Javascript directly inside Excel just as you might write VBA, opening the door for casual VBA-only programmers to begin writing their add-ins in Javascript for better portability and sharing.

In my upcoming posts, I'm going to be detailing the challenges I have dealt with and am dealing with as I build Excel Javascript extensions, with a particular focus on building using the Vue.js framework. As part of this process, I intend to convert my Bookmarks add-in from VSTO to Javascript and release it on the Microsoft Store. I hope my documentation via these posts will help other developers overcome similar issues.