A busy day over at Ignite 2021 gives us an early look at a significant new feature in Excel that has the potential to have a huge impact in the coming years.
API access to custom data types
There is now a preview version of programmatic access to custom data types, via the Excel JavaScript API.
This development brings together the significant possibilities of custom data types, with the evolving features of the JavaScript API.
This combination will have a multiplier effect for what Excel developers and add-in authors will now be able to do.
Adding third party functionality to Excel – in an automated manner – just got a huge boost.
But, wait… what are custom data types? And what is the JavaScript API?
Custom data types
Custom data types have been around for a while now and give you the ability to define you own data objects, with various properties (and relationships between properties) that can give Excel a lot more power as a tool for custom data structures.
The first in-built examples included:
- Geographical data (places, cities, locations etc), containing rich information such as population statistics and place features: think “Atlas meets Encyclopaedia”
- Movies, containing rich information such as release dates, actors, genres, etc: think “IMDB meets LinkedIn”
- Stock history, containing rich information about companies and their stock price history: think “Bloomberg meets Yahoo Finance”
There are now many more examples. You can use custom data types for most things you might use a database for – anything that has a data structure. Ideas, vehicles, the entire animal kingdom – go for it!
Any record in a custom data set (such as a city, movie, company, idea, vehicle, animal, etc) can be represented in a single cell in Excel when using that data structure. Various properties or attributes of that item (think “database fields”) are accessible from within that cell, as part of the extended data structure.
Custom data types lets you define your own data structure and create your own custom objects.
JavaScript APIs
Since 2016, programmatic API access to Excel has been offered for developers, with a gradually increasing functionality set.
You can read more about building add-ins for Excel using the JavaScript API at Excel JavaScript Object Model in Office Add-ins.
New APIs are added in preview before being deployed into production software versions. The custom data types API has just commenced in preview, so will be evaluated further before being deployed.
Being able to perform CRUD functions on custom data types – using inputs created dynamically or programatically – helps unleash the power of custom data types for developers.
Since JavaScript is the dominant language of the visually accessible part of the web, there are big opportunities to integrate existing user-facing functionality with custom data types in Excel.
Access to information available on the web just got opened up, in particular the ability to specify how that information is stored and how it relates to other information. This is a big development!
Preview status
The API for creating and manipulating custom data types is in preview, which means it is available for “insider” Excel developers to play with and evaluate.
In time, we expect some version of it to make it into a future release of the JavaScript API requirements set.
You can read more about preview APIs at Excel JavaScript Preview APIs.
Get excited!
Excel has long been a default database tool used by people who want to create a database, without actually creating a database.
Custom data types, combined with the ability to define and manage them using the JavaScript API, have the potential to give Excel a lot more powerful capabilities in classifying and describing complex information.
Who knows what capabilities add-in developers will now be able to unleash? Using custom data types through the new API, the possibilities just got much more exciting.
We can only begin to imagine what is possible with the combination of custom data types and programmatic access to them via JavaScript.
After all, JavaScript itself can be created programmatically too…
Learn more
To read the Ignite 2021 announcement about all this, see Ignite 2021: Excel add-ins and data types.
