Linked data types

Cells that carry a structured entity (Stocks, Geography, custom) with refreshable fields accessed via dot notation

Linked data types are Excel's "Stocks" and "Geography" cells generalized into a framework. A cell holds an entity with named fields, and other cells reference those fields via dot notation (=A1.price). The library ships the entity model + a provider plug-in API; you wire your own data source.

This is how the storybook's "Insert stock at A1" button works — it writes an entity structured value into a cell, then refreshDataTypes walks every entity cell and calls the matching provider to refresh fields.

The entity shape

A linked-data-type cell's ev.structuredValue is an entity:

type Entity = {
  kind: "entity";
  dataType: string;          // matches a DataTypeProvider.id
  externalId: string;        // the ticker / location / etc.
  status: "loading" | "loaded" | "stale" | "error";
  fields?: Record<string, string | number | boolean>;
  formattedValue?: string;
};

The cell renderer paints formattedValue (or fv) as the display text. Field-access formulas (=A1.price) resolve through getEffectiveStructuredValue.

Wiring a provider

import type { DataTypeProvider } from "@rowsncolumns/spreadsheet-state";

const stockProvider: DataTypeProvider = {
  id: "stock",
  schema: () => [
    { field: "name", type: "string" },
    { field: "price", type: "number", numberFormat: '"$"#,##0.00' },
    { field: "currency", type: "string" },
    { field: "changePercent", type: "number", numberFormat: "0.00%" },
  ],
  async refresh(entity) {
    const ticker = (entity.externalId ?? "").toUpperCase();
    const row = await fetchStockQuote(ticker);
    return {
      fields: {
        name: row.name,
        price: row.price,
        currency: row.currency,
        changePercent: row.changePercent,
      },
      formattedValue: `${ticker}  $${row.price.toFixed(2)}`,
    };
  },
};

Pass providers to useSpreadsheetState:

resolveEntityField is exported from @rowsncolumns/spreadsheet-state; it reads entity.fields[property].

Inserting a linked data type

Write the entity shape into the cell's ev.structuredValue:

After the refresh:

  • =A2.price returns the numeric price.

  • =A2.changePercent returns the percent (with format applied).

  • =SUM(A2:A4.price) aggregates across multiple linked-data-type cells.

Dot-notation field access

=cellRef.field resolves through the formula engine's structured-value branch. The field name matches a key in entity.fields. The library auto-applies the numberFormat declared in the provider's schema() to the result.

Refresh model

refreshDataTypes({sheetId}) walks every cell whose structuredValue.kind === "entity" and calls the matching provider's refresh(). Each refresh sets the cell's status to "loading" while in flight, then "loaded" (or "error") when complete. Cells with downstream =A1.field formulas recompute automatically once the entity lands.

status: "stale" is the initial state for a freshly-inserted entity and triggers a refresh on the next refreshDataTypes call.

What's not yet supported

  • OOXML linkedDataType round-trip — Excel's native Stocks / Geography panels emit a specific element that's not yet parsed or emitted. Custom provider entities survive the export as plain JSON in structuredValue if the host writes them as such; Excel's native Stocks won't be recognized.

See Excel compatibility → "Phase 12 — linked data types" for the deferral notes.

Last updated