JSON Custom Data Types Come to Excel Spreadsheets – InApps Technology 2022

Roughly 750 million people on the planet use Excel, Microsoft’s familiar spreadsheet application, for storing and working with data.

So it’s a big deal when new functionality gets added.

Sure enough, at this year’s annual Microsoft Ignite conference in early November, the company announced that Excel is now upping its game with changes that at least one tech site described as Microsoft bringing JavaScript to Excel.

But the truth is much more complicated: Microsoft already added the ability to write custom functions for Excel using JavaScript Add-Ins back in 2018. Instead, Excel is now also getting the tremendous power and flexibility of the customizable data types found in object-oriented programming. Which is to say, Excel will now also support creating your own data types using JavaScript Object Notation (or JSON).

And this will ultimately bring the ability to expose data from your own in-house services into Excel spreadsheets.

Send in the Add-Ins

This new functionality arrives through an Excel power-user feature called Add-Ins (which at one time were called “Apps for Office”). For years Microsoft has been allowing these functionality-extending applications to be integrated into Excel, letting them add custom keyboard shortcuts, dialog boxes and even a custom “ribbon” of icons below Excel’s menus.

Add-Ins can be distributed through the Office Add-In store (or deployed through your IT department). And, of course, some users even create their own (though Microsoft’s documentation describes building them using Visual Studio or the Yeoman generator for Office Add-ins). Thanks to Add-Ins, Excel has been getting more and more sophisticated; content Add-Ins even allow the embedding of YouTube video players in Excel spreadsheets.

But now Excel Add-Ins are also getting the powerful new ability to create custom data types. In an early November blog post, Wangui McKelvey, general manager for Microsoft 365, wrote that the feature transforms complex data into “more flexible structures” that “expose this data to users in more natural ways, while still providing easy access to the full information users care about for their analysis or reporting.”

In her blog post, McKelvey emphasized that this means that now data in your Excel applications could be drawn from your own custom data source. (In an example from one Ignite conference skilling session, the columns of an in-house database are seamlessly gathered together into single objects.)

The new support for custom data types “allows you to take your flat data and package it up into a logical [single] value,” Chris Gross, a Microsoft program manager working on Excel, explained in a blog post. (These objects can even include additional properties like images that illustrate the object being defined.)

And as the skilling session demonstrated, the objects created from the in-house database system can then be seamlessly fed into calculations of annual profit margins for each object type. Yes, user-generated “custom functions” can take as their arguments these user-created custom data types.

“We’ve built these APIs with services in mind,” said Gross in the skilling session. In his blog post, he wrote that “we want to make it easy for you as a developer to extend any service and easily expose your data to Excel through your Add-In. It should be as simple as adopting our schema and passing the schematized values for display and re-use within Excel.”

Or, as he said in his skilling session, “It’s as easy as connecting your pre-existing service to Excel.”

At one point McKelvey’s post also specifically highlighted an intra-company scenario where power users can “create Add-Ins or solutions which can connect data types to your own service or data.”

Gross re-emphasized their compatibility with services later in the skilling session, telling his audience that Microsoft was “building out a platform for developers to deliver on.”

A Multiyear Journey

The feature is the culmination of years of development.

In the past, Excel’s cells have been mostly populated with text or numbers. (Some cells also allowed true-false Boolean values — and of course, there were also error messages like #REF! for invalid references.) But now Microsoft “wants to take Excel beyond numbers and text,” claims a headline at ZDNet (while underscoring that the new feature “isn’t aimed at Excel-using business execs but developers who need to wrangle metadata in Excel cells.”)

Looking beyond text and numbers, In the skilling session, Gross described Microsoft’s path: “We have been on a multiyear journey to uplevel the types of data you can work with natively in Excel.”

Starting in 2018, Microsoft first added an additional data type for geographical names (which came pre-populated with properties like population) as well as a type for Stocks, both tapping Microsoft’s “Knowledge Graph” data set for up-to-date information.

Microsoft dubbed them “connected data types,” since their values could be refreshed with new information online, and last year Microsoft announced a partnership with Wolfram to create several hundred more. (“Complement your analysis with connected data types like chemistry, nutrition, ZIP codes, historical events, even genetics.”)

But now they’ve taken the final step: the ability to create your own custom data types — along with custom functions to make use of them. To implement this new functionality, Microsoft has expanded an already existing feature for Excel developers. Microsoft’s documentation had already included an advanced Excel feature that lets developers specify a range of cells that will all have their values assigned or adjusted. This can be as simple as:

Worksheets("Sheet1").Range("A1").Value = 3.14159

But a range can obviously spread across multiple cells too — and more importantly, the values can also be assigned by a line of code. For example, numbers in the range could be all be tested and rounded down if their value is below a certain threshold.

To keep everything simple and intuitive, Range is using the long-familiar syntax for an object, along with several object-style properties (like Range.Value and also Range.Count — the number of cells in the range).

So now Microsoft is adding another object-style property for Range, with the name Range.valuesAsJson. This now allows the value-assigning code to use the familiar syntax of JavaScript Object Notation. (Although this week an overview page for datatypes was still warning that Range.valuesAsJSON was “in active development and are not yet available in public preview.”)

A New Schema to Specify Data Organization

Along with all this, Microsoft has announced a new schema specifying how data gets organized in Excel — which includes a new Type property (specifying the data’s type), which can also work with the custom data types. “Our focus has been on exposing the structures so that you can bring your data to Excel using our schema,” Gross wrote in his blog post.

However, Microsoft’s official documentation contains another disclaimer: “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.”

It also warns the custom functions integration with data types “is currently in public preview and is only compatible with Office on Windows.”

“To use this feature, you need to join the Office Insider Program and then choose the Beta Channel Insider level.”

But Microsoft ultimately has some very high hopes for the new feature. In last week’s blog post, McKelvey described it as “giving developers the ultimate freedom to build in Excel.”

And she argued that it’s part of a larger process of “making Office a universal, interactive canvas for creators of all kinds.”