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:

const { refreshDataTypes } = useSpreadsheetState({
  dataTypeProviders: [stockProvider],
  getEffectiveStructuredValue: (structuredValue, property) => {
    if (property && structuredValue?.kind === "entity") {
      return resolveEntityField(structuredValue, property);
    }
    if (property) {
      const value = get(structuredValue, property);
      return value ?? "";
    }
    return structuredValue;
  },
});

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:

const tickers = ["AAPL", "MSFT", "GOOG"];
onChangeSheetData?.((prev) => {
  const next = { ...prev };
  const sheet = (next[activeSheetId] ?? []).slice();
  next[activeSheetId] = sheet;
  tickers.forEach((ticker, idx) => {
    const rowIndex = 1 + idx;
    const row = sheet[rowIndex] ?? {};
    const values = (row.values ?? []).slice();
    values[1] = {
      ev: {
        structuredValue: {
          kind: "entity",
          dataType: "stock",
          externalId: ticker,
          status: "stale",
          formattedValue: ticker,
        },
      },
      fv: ticker,
    };
    sheet[rowIndex] = { ...row, values };
  });
  return next;
});

// Now refresh so fields hydrate
await refreshDataTypes?.({ sheetId: activeSheetId });

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.

A2 entity {ticker: "AAPL", price: 182.50, currency: "USD"}

=A2                  → "AAPL  $182.50"   (cell's formattedValue)
=A2.price            → 182.50            (numeric)
=A2.currency         → "USD"             (string)
=A2.price * 100      → 18250

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.