Workspace cross-Unit formulas
Load core, the Host Unit Skill, and the Sheet or Base Source Skill first. A cross-Unit formula has
two supported consumers: a Sheet cell, or a normal Host Shape whose displayed text is calculated
from a Sheet range or Base table column. Unit Skills own Unit-local content and visual behavior;
this Topic Skill owns external Source binding, formula, calculation, and cross-Unit verification.
Resolve the public Facade contract before authoring:
Use api show for FRange.setFormula, FShape.setFormula, FShape.getFormulaResult, FFormula.buildReference, FFormula.upsertExternalReference, and FFormula.onCalculationResultApplied.
Identity contract
- The caller must already know the stable Source
unitId, Source Unit type, Unit name, and source coordinates. An existing Host binding is also authoritative. Do not resolve a Unit ID from a display name. - Use the Source Unit name as the normal
formulaQualifier. Reusing a qualifier with a different Source explicitly changes what every Host formula using it resolves on its next write or fresh load. If both Sources must remain available, choose an explicit readable Host-local alias for at least one. The persisted qualifier-to-unitIdbinding, not global name uniqueness, selects the Source. buildReference()writes that binding into the Host snapshot and returns a safely quoted formula fragment. It does not list or preload Workspace Units.- Sheet cell formulas and formula-driven Shapes read the same Host external-reference metadata. A
mapping written by
buildReference()is therefore shared by both consumers. - A Sheet cell reads the persisted Host mapping directly. For a Shape, pass the same stable identity
again in
setFormula({ formula, externalReferences }); the repeated binding is an idempotent consistency check. - Do not stage a Source into the task Worktree merely to read it. The Workspace runtime loads an authorized Source on first calculation. Do not persist a Workspace URL or share link as identity.
Sheet Source binding
The command still targets only the Host Unit. Replace the Source placeholders with exact identity and Sheet coordinates established before this authoring step, then build the reference in the Host context:
const hostUnit = workbook;
const hostSheet = hostUnit.getSheetByName("Dashboard");
if (!hostSheet) throw new Error("Host sheet Dashboard was not found.");
const source = {
unitId: "<source-unit-id>",
qualifier: "<source-unit-name-or-host-local-alias>",
sheetName: "Orders",
};
const formula = api.getFormula();
const reference = formula.buildReference({
hostUnitId: hostUnit.getId(),
unit: {
unitId: source.unitId,
formulaQualifier: source.qualifier,
},
target: {
kind: api.Enum.FormulaReferenceType.SHEET_RANGE,
sheetName: source.sheetName,
range: { startRow: 1, endRow: 3, startColumn: 1, endColumn: 1 },
},
});
Sheet cell consumer
Continue in the same execution by subscribing before the write:
const targetCell = hostSheet.getRange("C1");
const applied = formula.onCalculationResultApplied(30_000);
targetCell.setFormula("=SUM(" + reference + ")");
await applied;
return { formula: targetCell.getFormula(), value: targetCell.getValue() };
Formula-driven Shape consumer
Use the Host Unit Skill to create a regular Shape, then bind it to the same reference and Source identity. The Facade retains Formula Shape method and enum names; they are API details, not a separate authoring workflow.
const shape = hostSheet.insertShape({
shapeType: api.Enum.ShapeTypeEnum.Rect,
transform: { left: 700, top: 240, width: 280, height: 72 },
});
if (!shape) throw new Error("Formula-driven Shape could not be inserted.");
const applied = formula.onCalculationResultApplied(30_000);
shape.setFormula({
formula: "=SUM(" + reference + ")",
externalReferences: [
{
qualifier: source.qualifier,
sourceUnitId: source.unitId,
sourceUnitType: api.Enum.UniverInstanceType.UNIVER_SHEET,
},
],
});
await applied;
const result = shape.getFormulaResult();
if (result?.status !== api.Enum.FormulaShapeResultStatus.SUCCESS) {
throw new Error("Formula-driven Shape failed: " + JSON.stringify(result));
}
return { shapeId: shape.getId(), formula: shape.getFormula(), result };
Base Source binding
For a Base Source, keep the Host and selected consumer unchanged and build a table-column reference:
const source = {
unitId: "<source-base-unit-id>",
qualifier: "<source-base-name-or-host-local-alias>",
};
const reference = api.getFormula().buildReference({
hostUnitId: hostUnit.getId(),
unit: { unitId: source.unitId, formulaQualifier: source.qualifier },
target: {
kind: api.Enum.FormulaReferenceType.TABLE_COLUMN,
tableName: "Budget",
columnName: "Amount",
},
});
tableName must be the Source Base's real OOXML table identifier and columnName must
match the real field name. Never pass "table" as a generic placeholder. Resolve the Source table
from Base metadata first; buildReference() owns qualifier quoting and escaping.
Use that reference in the Sheet cell recipe above. For a formula-driven Shape, use the same
externalReferences structure with sourceUnitType: api.Enum.UniverInstanceType.UNIVER_BASE.
Existing formula text
For hand-written, imported, or batch formula text, persist the same Host mapping before writing the formula and fail when the mapping is rejected:
const source = { unitId: "<source-sheet-unit-id>", qualifier: "Orders" };
const bound = api.getFormula().upsertExternalReference({
unitId: hostUnit.getId(),
qualifier: source.qualifier,
sourceUnitId: source.unitId,
sourceUnitType: api.Enum.UniverInstanceType.UNIVER_SHEET,
});
if (!bound) throw new Error("Cross-Unit Source binding failed.");
const applied = api.getFormula().onCalculationResultApplied(30_000);
hostSheet.getRange("C1").setFormula("=SUM('[Orders]Sheet1'!B2:B10)");
await applied;
Use UNIVER_BASE for a Base Source. The qualifier in formula text and metadata must match exactly.
When an upsert rebinds an existing qualifier, write the formula for the current authoring operation
after the upsert and await calculation. executeCalculation() recalculates compiled formulas but
does not by itself reparse every existing formula against the new Host binding. Prefer
buildReference() whenever names need quoting or escaping.
A Sheet, Doc, Slide, or Board may own a formula-driven Shape; use its Unit Skill to create and later
resolve the Shape by stable ID.
Verification and failure
Register onCalculationResultApplied() before the selected consumer's formula write. In a later
read-only execute, verify a cell's exact formula and cached value, or a Shape's stable ID,
isFormulaShape(), exact formula, and successful raw/display result. Missing, invalid, ambiguous,
inaccessible, or type-incompatible bindings fail closed; do not repair them by guessing a Source
from a loaded Unit or display name. Finish with the Host Skill's screenshot and review flow.