Ignite 2021: Excel add-ins and data types 

Chris

We’re excited to announce new capabilities that let you programmatically create your own custom data types within Excel. This helps solve a key problem developers have today with requirements around metadata associated with cells. With this new set of capabilities, you can leverage the technology behind data types, which powers the experiences of Wolfram, Stocks, Geography, and other linked data types that can be found in Excel today. Onboard your add-in to the next generation of Excel experiences with data types, and reduce your time to market by reaping the benefits of our deeply integrated feature set.

Data types

More than just text and numbers

Countless solutions have been built with Excel. Many of these solutions are powered by cells which most often contain (or evaluate to) just text or numbers. With the addition of data types, Excel has evolved to a world where cells can contain something much richer. Linked data types (Stocks, Geography, Wolfram and Power BI), entity values, dynamic arrays, improved errors, images in cells, lambdas, and formatted number values are all new types that have delivered on this promise and back many of the experiences you have seen introduced in the product today.

Increasing developer velocity

With the addition of data types APIs, we want to empower you as a developer to take advantage of many of the experiences we have built that power data types. This will reduce the complexity of your add-in and eliminate the need for custom solutions or surfaces that power many add-ins today. We have built out data types, inspired by many of the patterns we have seen across all the solutions built in Excel.

For example, if you have a solution that creates another dimension on top of your data, you can make use of a card view. If you want to assign a given format to a value and have it travel through the calculation engine, you can use formatted values. Ultimately, if you need another dimension added to your data, you can make use of our composable types to deliver a truly custom experience for users of your add-in.

New Range.valuesAsJson API

The first API we’re introducing, Range.valuesAsJson, allows you to both read and write data types. If you’re familiar with the Range.values API, this API works similarly The major difference is that this new API can return augmented information about basic types (text, numbers, errors) as well as information about the new data types we have introduced. One other notable difference is the introduction of a schema that conforms to the types that are available to Excel.

Our focus has been on exposing the structures so that you can bring your data to Excel using our schema and leverage many of the built-in experiences today. Additionally, we are allowing you to make use of these types within custom functions. This opens the door to create new experiences and innovate on what is possible with the product today.

Flexible containers

The following is the complete list of types we’re exposing in this first release:

  • Entity values
  • Formatted number values
  • Web images
  • Improved errors
  • Arrays*

*Only supported as a property of an entity and cannot be a standalone value currently.

With these value types, you are free to use them as both inputs and outputs for your experiences, and you can also arbitrarily combine them.

For example, you can create an entity value that contains properties of all the types, such as a web image for richer context about the entity, formatted values for things like currencies or dates, arrays of types for lists of data, and even additional entities through nesting.

This allows you to take your flat data and package it up into a logical value, as shown in the following example.

Example of how to take your flat data and package it up into a logical value

Schema

As noted, the structures are all driven by our schema. Here are some quick examples of JSON that adheres to the schema and creates a few types. Be sure to take note of the type field, which tells Excel what type a given value is.

Quick examples of JSON that adhere to the schema and creates a few types..

Built with services in mind

We are building this API with the world of services in mind. 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. We would love to learn more about what does and doesn’t work as you begin developing solutions that make use of the APIs.

Custom functions

In addition to generally supporting read and write of data types, we have also added support for data types within custom functions now in public preview. This unlocks a whole new class of functions that customize analysis or generate reporting, taking full advantage of the powerful Excel calculation engine.

You can either generate data types through custom functions or take created data types as the function arguments into calculation. The same schemas are used when interacting with data types using custom functions, so after you finalize the structure of data types, we’ll keep the consistent experience between APIs and custom functions. Get more information about Custom Functions.

Availability

We are in the process of rolling out support for data types. The next release of the preview APIs will add support which we expect to happen near the end of the month. Stay tuned for a final version number

Upon release, to access these new APIs, you will need:

  1. A valid Insiders: Beta build of Excel
    1. Windows: greater than or equal to 16.0.14626.10000
    2. Mac: greater than or equal to 16.55.21102600
  2. The latest preview version of the js APIs

We will continue to make this available to a wider audience over the coming months, so watch for updates.

Learn more

To learn more about data types and access the documentation, check out our developer overview and see the Ignite announcements at:

Connect with us

We continue to deliver new capabilities based on your needs. The best way to stay up to date is to follow this blog. And, join our monthly Office Add-ins community call, the second Wednesday of each month at 8:00AM PST/PDT, to connect with us and our community. See you there!

 

2 comments

Leave a comment