Edit

Excel data types core concepts

  • Article

This article describes how to use the Excel JavaScript API to work with data types. It introduces core concepts that are fundamental to data type development.

The valuesAsJson property

The valuesAsJson property (or the singular valueAsJson for NamedItem) is integral to creating data types in Excel. This property is an expansion of values properties, such as Range.values. Both the values and valuesAsJson properties are used to access the value in a cell, but the values property only returns one of the four basic types: string, number, boolean, or error (as a string). In contrast, valuesAsJson returns expanded information about the four basic types, and this property can return data types such as formatted number values, entities, and web images.

The following objects offer the valuesAsJson property.

Note

Some cell values change based on a user's locale. The valuesAsJsonLocal property offers localization support and is available on all the same objects as valuesAsJson.

Cell values

The valuesAsJson property returns a CellValue type alias, which is a union of the following data types.

The CellValue type alias also returns the CellValueExtraProperties object, which is an intersection with the rest of the *CellValue types. It's not a data type itself. The properties of the CellValueExtraProperties object are used with all data types to specify details related to overwriting cell values.

JSON schema

Each cell value type returned by valuesAsJson uses a JSON metadata schema designed for that type. Along with additional properties unique to each data type, these JSON metadata schemas all have the type, basicType, and basicValue properties in common.

The type defines the CellValueType of the data. The basicType is always read-only and is used as a fallback when the data type isn't supported or is formatted incorrectly. The basicValue matches the value that would be returned by the values property. The basicValue is used as a fallback when calculations encounter incompatible scenarios, such as an older version of Excel that doesn't support the data types feature. The basicValue is read-only for ArrayCellValue, EntityCellValue, LinkedEntityCellValue, and WebImageCellValue data types.

In addition to the three fields that all data types share, the JSON metadata schema for each *CellValue has properties available according to that type. For example, the WebImageCellValue type includes the altText and attribution properties, while the EntityCellValue type offers the properties and text fields.

The following sections show JSON code samples for the formatted number value, entity value, and web image data types.

Formatted number values

The FormattedNumberCellValue object enables Excel add-ins to define a numberFormat property for a value. Once assigned, this number format travels through calculations with the value and can be returned by functions.

The following JSON code sample shows the complete schema of a formatted number value. The myDate formatted number value in the code sample displays as 1/16/1990 in the Excel UI. If the minimum compatibility requirements for the data types feature aren't met, calculations use the basicValue in place of the formatted number.

// This is an example of the complete JSON of a formatted number value.
// In this case, the number is formatted as a date.
const myDate: Excel.FormattedNumberCellValue = {
    type: Excel.CellValueType.formattedNumber,
    basicValue: 32889.0,
    basicType: Excel.RangeValueType.double, // A read-only property. Used as a fallback in incompatible scenarios.
    numberFormat: "m/d/yyyy"
};

Begin experimenting with formatted number values by opening Script Lab and checking out the Data types: Formatted numbers snippet in our Samples library.

Entity values

An entity value is a container for data types, similar to an object in object-oriented programming. Entities also support arrays as properties of an entity value. The EntityCellValue object allows add-ins to define properties such as type, text, and properties. The properties property enables the entity value to define and contain additional data types.

The basicType and basicValue properties define how calculations read this entity data type if the minimum compatibility requirements to use data types aren't met. In that scenario, this entity data type displays as a #VALUE! error in the Excel UI.

The following JSON code sample shows the complete schema of an entity value that contains text, an image, a date, and an additional text value.

// This is an example of the complete JSON for an entity value.
// The entity contains text and properties which contain an image, a date, and another text value.
const myEntity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: "A llama",
    properties: {
        image: myImage,
        "start date": myDate,
        "quote": {
            type: Excel.CellValueType.string,
            basicValue: "I love llamas."
        }
    }, 
    basicType: Excel.RangeValueType.error, // A read-only property. Used as a fallback in incompatible scenarios.
    basicValue: "#VALUE!" // A read-only property. Used as a fallback in incompatible scenarios.
};

Entity values also offer a layouts property that creates a card for the entity. The card displays as a modal window in the Excel UI and can display additional information contained within the entity value, beyond what's visible in the cell. To learn more, see Use cards with entity value data types.

To explore entity data types, start by going to Script Lab in Excel and opening the Data types: Create entity cards from data in a table snippet in our Samples library. The Data types: Entity values with references and Data types: Entity value attribution properties snippets offer a deeper look at entity features.

Linked entities

Linked entity values, or LinkedEntityCellValue objects, are a type of entity value. These objects integrate data provided by an external service and can display this data as an entity card, like regular entity values. The Stocks and Geography data types available via the Excel UI are linked entity values.

Web image values

The WebImageCellValue object creates the ability to store an image as part of an entity or as an independent value in a range. This object offers many properties, including address, altText, and relatedImagesAddress.

The basicType and basicValue properties define how calculations read the web image data type if the minimum compatibility requirements to use the data types feature aren't met. In that scenario, this web image data type displays as a #VALUE! error in the Excel UI.

The following JSON code sample shows the complete schema of a web image.

// This is an example of the complete JSON for a web image.
const myImage: Excel.WebImageCellValue = {
    type: Excel.CellValueType.webImage,
    address: "https://bit.ly/2YGOwtw", 
    basicType: Excel.RangeValueType.error, // A read-only property. Used as a fallback in incompatible scenarios.
    basicValue: "#VALUE!" // A read-only property. Used as a fallback in incompatible scenarios.
};

Try out web image data types by opening Script Lab and selecting the Data types: Web images snippet in our Samples library.

Improved error support

The data types APIs expose existing Excel UI errors as objects. Now that these errors are accessible as objects, add-ins can define or retrieve properties such as type, errorType, and errorSubType.

The following is a list of all the error objects with expanded support through data types.

Each of the error objects can access an enum through the errorSubType property, and this enum contains additional data about the error. For example, the BlockedErrorCellValue error object can access the BlockedErrorCellValueSubType enum. The BlockedErrorCellValueSubType enum offers additional data about what caused the error.

Learn more about the data types error objects by checking out the Data types: Set error values snippet in our Script Lab Samples library.

Next steps

Learn how entity data types extend the potential of Excel add-ins beyond a 2-dimensional grid with the Use cards with entity value data types article.

Use the Create and explore data types in Excel sample in our OfficeDev/Office-Add-in-samples repository to experiment more deeply with data types by building and sideloading an add-in that creates and edits data types in a workbook.

See also