Microsoft recently announced a developer preview release supporting JavaScript to create custom functions directly in Excel. This addition moves beyond the existing Microsoft Office JavaScript API, which is limited to building add-ins or web applications that interact with the object models in Office host applications.
The Microsoft Office team has created a GitHub source code repository to learn how to use JavaScript within Excel functions. The example repository separates functionality into four primary areas:
- JavaScript: custom function source code
- JSON: metadata used by Excel to show available custom functions
- HTML: preview release mechanism for referencing the relevant JavaScript source code and a method to initialize a custom function
- XML: informs Excel about the location and namespace of the custom function and its resources
Custom functions have no associated user interface and developers should not attempt to manipulate the DOM. The project expects to remove the HTML file for custom function initialization before the final release of this feature.
Modern JavaScript features such as Promises are supported, allowing developers to define custom functions as synchronous, for quickly computed values, or asynchronous, for calculations that may take a while to complete. Streams, an asynchronous promise that returns a value multiple times rather than just once, are also an option for Excel custom JavaScript functions.
Excel's JavaScript leverages the same Chakra JavaScript engine used by the Microsoft Edge web browser. This addition brings some parity to Google Sheets which already supports JavaScript within Sheets' custom functions.
Custom functions support is currently limited to the developer preview versions of Excel for Windows, Mac, and Excel Online. Custom functions are not currently available for Excel on mobile devices, or in production versions of Excel.
TypeScript users may also write custom JavaScript functions. Like any other JavaScript environment, TypeScript should be transpiled to JavaScript. Microsoft does not intend to provide direct TypeScript support within Excel.
While there isn't an official production release date for custom JavaScript functions within Excel, those interested in custom JavaScript functions in Excel should try this preview release and provide feedback to the Office team.
