Overview of data types in Excel add-ins (preview)

Note

Data types APIs are currently only available in public preview. Preview APIs are subject to change and are not intended for use in a production environment. Do not use preview APIs in a production environment or within business-critical documents.

Important

Some of the data types APIs, such as Range.valuesAsJSON are in active development and are not yet available in public preview. This article is intended as a conceptual introduction. Concepts described in this article that are not yet in public preview will be released to preview soon.

Data types in the Excel JavaScript API enable add-in developers to organize complex data structures as objects, such as formatted number values, web images, and entity values.

Prior to the data types addition, the Excel JavaScript API supported string, number, boolean, and error data types. The Excel UI formatting layer is capable of adding currency, date, and other types of formatting to cells that contain the four original data types, but this formatting layer only controls the display of the original data types in the Excel UI. The underlying number value is not changed, even when a cell in the Excel UI is formatted as currency or a date. This gap between an underlying value and the formatted display in the Excel UI can result in confusion and errors during add-in calculations. Custom data types are a solution to this gap.

Data types expand Excel JavaScript API support beyond the four original data types (string, number, boolean, and error) to include web images, formatted number values, entity values, arrays within entity values, and improved error data types as flexible data structures. These types, which power many linked data types experiences, allow for precision and simplicity during add-in calculations and extend the potential of Excel add-ins beyond a 2-dimensional grid.

Data types and custom functions

Note

The custom functions integration with data types is currently in public preview and is only compatible with Office on Windows. Preview features are subject to change and are not intended for use in a production environment. We recommend that you try them out in test and development environments only. Do not use preview features in a production environment or within business-critical documents.

To try out the custom functions integration with data types in Office on Windows, you must have an Excel build number greater than or equal to 16.0.14623.20002. To use this feature, you need to join the Office Insider Program and then choose the Beta Channel Insider level. To learn more, see Join the Office Insider Program.

Data types enhance the power of custom functions. Custom functions accept data types as both inputs to custom functions and outputs of custom functions, and custom functions use the same JSON schema for data types as the Excel JavaScript API. This data types JSON schema is maintained as custom functions calculate and evaluate. To learn more about integrating data types with your custom functions, see Custom functions and data types core concepts.

See also