FFormula

This interface class provides methods to modify the behavior of the operation formula.

This class should not be instantiated directly. Use factory methods on univerAPI instead.

@univerjs-pro/engine-formula/facade extends the core formula Facade with cross-unit Sheet and Base reference authoring.

Access

Access through:

• FUniver.getFormula()

Setup

Register @univerjs/engine-formula or a preset that includes it. In plugin mode, import @univerjs/engine-formula/facade. Additional methods below require their listed plugin packages. See Facade setup.

@univerjs/engine-formula

FFormula.calculationEnd

Listening calculation ends.

calculationEnd(callback: (functionsExecutedState: FormulaExecutedStateType) => void): IDisposable

Parameters

• callback — Required. The callback function to be called when the formula calculation ends.

Returns

The disposable instance.

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.calculationEnd((functionsExecutedState) => {  console.log('Calculation end', functionsExecutedState)})

Types: IDisposable · FormulaExecutedStateType

Package: @univerjs/engine-formula · Type definitions

FFormula.calculationProcessing

Listening calculation processing.

calculationProcessing(callback: (stageInfo: IExecutionInProgressParams) => void): IDisposable

Parameters

• callback — Required. The callback function to be called when the formula calculation is in progress.

Returns

The disposable instance.

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.calculationProcessing((stageInfo) => {  console.log('Calculation processing', stageInfo)})

Types: IDisposable · IExecutionInProgressParams

Package: @univerjs/engine-formula · Type definitions

FFormula.calculationResultApplied

Listens for formula results after every affected model has applied them.

calculationResultApplied(callback: (result: ISetFormulaCalculationResultMutation) => void): IDisposable

Parameters

• callback — Required. Receives each applied formula result asynchronously.

Returns

A disposable that unsubscribes from future result notifications.

Types: IDisposable · ISetFormulaCalculationResultMutation

Package: @univerjs/engine-formula · Type definitions

FFormula.calculationStart

Listening calculation starts.

calculationStart(callback: (forceCalculation: boolean) => void): IDisposable

Parameters

• callback — Required. The callback function to be called when the formula calculation starts.

Returns

The disposable instance.

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.calculationStart((forceCalculation) => {  console.log('Calculation start', forceCalculation)})

Types: IDisposable

Package: @univerjs/engine-formula · Type definitions

FFormula.executeCalculation

Forces formula calculation explicitly.

Normal facade mutations such as range.setFormula() already mark formula data dirty and schedule calculation automatically. Use this method only when an external data change did not produce a dirty command or an explicit full recalculation is required.

executeCalculation(): void

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.executeCalculation()

Package: @univerjs/engine-formula · Type definitions

FFormula.executeFormulas

Execute a batch of formulas asynchronously and receive computed results.

Each formula cell is represented as a string array: [fullFormula, ...subFormulas]

Where:

• fullFormula (index 0) is the complete formula expression written in the cell. Example: "=SUM(A1:A10) + SQRT(D7)".

• subFormulas (index 1+) are optional decomposed expressions extracted from the full formula. Each of them can be independently computed by the formula engine.

  These sub-expressions can include:

  • Single-cell references: "A2", "B2", "C5"
  • Range references: "A1:A10"
  • Function calls: "SQRT(D7)", "ABS(A2-B2)"
  • Any sub-formula that was parsed out of the original formula and can be evaluated on its own.

  The batch execution engine may use these sub-formulas for dependency resolution, incremental computation, or performance optimizations.

executeFormulas(formulas: IFormulaStringMap, timeout?: number): Promise<IFormulaExecuteResultMap>

Parameters

• formulas — Required. Nested structure (unit → sheet → row → column) describing formulas and their decomposed sub-expressions.
• timeout — Optional. Default: 30_000. Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

Returns

A promise that resolves with the computed value map mirroring the input structure.

Examples

const formulaEngine = univerAPI.getFormula()const formulas = {  Book1: {    Sheet1: {      2: {        3: [          // Full formula:          '=SUM(A1:A10) + SQRT(D7)',          // Decomposed sub-formulas (each one can be evaluated independently):          'SUM(A1:A10)', // sub-formula 1          'SQRT(D7)', // sub-formula 2          'A1:A10', // range reference          'D7', // single-cell reference        ],      },      4: {        5: ['=A2 + B2 + SQRT(C5)', 'A2', 'B2', 'SQRT(C5)'],      },    },  },}const result = await formulaEngine.executeFormulas(formulas)console.log(result)

Types: IFormulaExecuteResultMap · Promise · IFormulaStringMap

Package: @univerjs/engine-formula · Type definitions

FFormula.getAllDependencyTrees

Retrieve all formula dependency trees that were produced during the latest dependency-analysis run. This triggers a local dependency-calculation command and returns the complete set of dependency trees once the calculation finishes.

getAllDependencyTrees(timeout?: number): Promise<IFormulaDependencyTreeJson[]>

Parameters

• timeout — Optional. Default: 30_000. Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

Returns

A promise that resolves with the array of dependency trees.

Examples

const formulaEngine = univerAPI.getFormula()// Fetch all dependency trees generated for the current workbook.const trees = await formulaEngine.getAllDependencyTrees()console.log('All dependency trees:', trees)

Types: IFormulaDependencyTreeJson · Promise

Package: @univerjs/engine-formula · Type definitions

FFormula.getCellDependencyTree

Retrieve the dependency tree of a specific cell. This triggers a local dependency-calculation command for the given unit, sheet, and cell location, and returns the computed dependency tree when the calculation is completed.

getCellDependencyTree(param: { unitId: string; sheetId: string; row: number; column: number; }, timeout?: number): Promise<IFormulaDependencyTreeFullJson | undefined>

Parameters

• param — Required. The target cell location.
• timeout — Optional. Default: 30_000. Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

Returns

A promise that resolves with the dependency tree or undefined if no tree exists for that cell.

Examples

const formulaEngine = univerAPI.getFormula()// Query the dependency tree for cell B2 in a specific sheet.const tree = await formulaEngine.getCellDependencyTree({  unitId: 'workbook1',  sheetId: 'sheet1',  row: 1,  column: 1,})console.log('Cell dependency tree:', tree)

Types: IFormulaDependencyTreeFullJson · Promise

Package: @univerjs/engine-formula · Type definitions

FFormula.getFormulaExpressTree

Parse a formula string and return its formula expression tree.

This API analyzes the syntactic structure of a formula and builds an expression tree that reflects how the formula is composed (functions, operators, ranges, and nested expressions), without performing calculation or dependency evaluation.

The returned tree is suitable for:

• Formula structure visualization
• Explaining complex formulas (e.g. LET / LAMBDA)
• Debugging or inspecting formula composition
• Building advanced formula tooling

getFormulaExpressTree(formulaString: string, unitId: string): IExprTreeNode | null

Parameters

• formulaString — Required. The formula string to parse (with or without leading =)
• unitId — Required. The workbook unit id used to resolve defined names and tables.

Returns

A formula expression tree describing the hierarchical structure of the formula

Examples

const formulaEngine = univerAPI.getFormula()const formula = '=LET(x,SUM(A1,B1,A1:B10),y,OFFSET(A1:B10,0,1),SUM(x,y)+x)+1'const exprTree = formulaEngine.getFormulaExpressTree(formula)console.log(exprTree)

Example output (simplified):

{  "value": "let(x,sum(A1,B1,A1:B10),y,offset(A1:B10,0,1),sum(x,y)+x)+1",  "children": [    {      "value": "let(x,sum(A1,B1,A1:B10),y,offset(A1:B10,0,1),sum(x,y)+x)",      "children": [        {          "value": "sum(A1,B1,A1:B10)",          "children": [            {              "value": "A1:B10",              "children": []            }          ]        },        {          "value": "offset(A1:B10,0,1)",          "children": [            {              "value": "A1:B10",              "children": []            }          ]        }      ]    }  ]}

Types: IExprTreeNode

Package: @univerjs/engine-formula · Type definitions

FFormula.getInRangeFormulas

Retrieve the dependency trees of all formulas inside the specified ranges. Unlike getRangeDependents, this API only returns formulas whose definitions physically reside within the queried ranges.

Internally this triggers the same dependency-calculation command but with isInRange = true, and the promise resolves when the results are ready.

getInRangeFormulas(unitRanges: IUnitRange[], timeout?: number): Promise<IFormulaDependencyTreeJson[]>

Parameters

• unitRanges — Required. An array of workbook/sheet ranges defining the lookup boundaries:
• unitId The workbook ID.
• sheetId The sheet ID.
• range The zero-based grid range.
• timeout — Optional. Default: 30_000. Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

Returns

A promise that resolves with an array of IFormulaDependencyTreeJson describing every formula found in the provided ranges along with their parent/child relationships.

Examples

const formulaEngine = univerAPI.getFormula()// Query all formulas that lie within A1:D20 in Sheet1.const formulasInRange = await formulaEngine.getInRangeFormulas([  {    unitId: 'workbook1',    sheetId: 'sheet1',    range: { startRow: 0, endRow: 19, startColumn: 0, endColumn: 3 },  },])console.log('Formulas inside range:', formulasInRange)

Types: IFormulaDependencyTreeJson · Promise · IUnitRange

Package: @univerjs/engine-formula · Type definitions

FFormula.getRangeDependents

Retrieve the full dependency trees for all formulas that depend on the specified ranges. This triggers a local dependency-calculation command and resolves once the calculation completes.

getRangeDependents(unitRanges: IUnitRange[], timeout?: number): Promise<IFormulaDependencyTreeJson[]>

Parameters

• unitRanges — Required. An array of workbook/sheet ranges to query. Each range includes:
• unitId The workbook ID.
• sheetId The sheet ID.
• range The row/column boundaries.
• timeout — Optional. Default: 30_000. Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

Returns

A promise that resolves with an array of IFormulaDependencyTreeJson representing formulas and their relationships within the dependency graph.

Examples

const formulaEngine = univerAPI.getFormula()// Query all formulas that depend on A1:B10 in Sheet1.const dependents = await formulaEngine.getRangeDependents([  {    unitId: 'workbook1',    sheetId: 'sheet1',    range: { startRow: 0, endRow: 9, startColumn: 0, endColumn: 1 },  },])console.log('Dependent formulas:', dependents)

Types: IFormulaDependencyTreeJson · Promise · IUnitRange

Package: @univerjs/engine-formula · Type definitions

FFormula.getRangeDependentsAndInRangeFormulas

Retrieve both:

1. the full dependency trees of all formulas that depend on the specified ranges, and
2. the dependency trees of all formulas that physically reside inside the specified ranges.

This is a convenience API that combines the behaviors of getRangeDependents and getInRangeFormulas into a single call.

Internally, it triggers a local dependency-calculation command once and resolves when both result sets are available, avoiding duplicate calculations and event listeners.

getRangeDependentsAndInRangeFormulas(unitRanges: IUnitRange[], timeout?: number): Promise<IFormulaDependentsAndInRangeResults>

Parameters

• unitRanges — Required. An array of workbook/sheet ranges to query. Each range includes:
• unitId The workbook ID.
• sheetId The sheet ID.
• range The zero-based row/column boundaries.
• timeout — Optional. Default: 30_000. Optional timeout in milliseconds. If the dependency calculation does not complete within this period, the promise will be rejected.

Returns

A promise that resolves with an object containing:

• dependents: Dependency trees of all formulas that depend on the specified ranges (upstream consumers).
• inRanges: Dependency trees of all formulas whose definitions are located inside the specified ranges.

Examples

const formulaEngine = univerAPI.getFormula()const result = await formulaEngine.getRangeDependentsAndInRangeFormulas([  {    unitId: 'workbook1',    sheetId: 'sheet1',    range: { startRow: 0, endRow: 9, startColumn: 0, endColumn: 1 },  },])console.log('Dependent formulas:', result.dependents)console.log('Formulas inside range:', result.inRanges)

Types: IFormulaDependentsAndInRangeResults · Promise · IUnitRange

Package: @univerjs/engine-formula · Type definitions

FFormula.lexerTreeBuilder

The tree builder for formula string.

readonly lexerTreeBuilder: LexerTreeBuilder

Types: LexerTreeBuilder

Package: @univerjs/engine-formula · Type definitions

FFormula.moveFormulaRefOffset

Offsets the formula

moveFormulaRefOffset(formulaString: string, refOffsetX: number, refOffsetY: number, ignoreAbsolute?: boolean): string

Parameters

• formulaString — Required. The formula string to offset
• refOffsetX — Required. The offset column
• refOffsetY — Required. The offset row
• ignoreAbsolute — Optional. Whether to ignore the absolute reference

Returns

The offset formula string

Examples

const formulaEngine = univerAPI.getFormula()const result = formulaEngine.moveFormulaRefOffset('=SUM(A1,B2)', 1, 1)console.log(result)

Package: @univerjs/engine-formula · Type definitions

FFormula.onCalculationResultApplied

Waits until the latest formula-calculation results have been applied.

onCalculationResultApplied(timeout?: number): Promise<void>

Parameters

• timeout — Optional. Maximum wait in milliseconds. Omit to wait without an overall timeout.

Returns

Resolves when the latest calculation results have been applied; rejects if the supplied timeout expires.

Types: Promise

Package: @univerjs/engine-formula · Type definitions

FFormula.sequenceNodesBuilder

Resolves the formula string to a 'node' node

sequenceNodesBuilder(formulaString: string): (string | ISequenceNode)[]

Parameters

• formulaString — Required. The formula string to resolve

Returns

The nodes of the formula string

Examples

const formulaEngine = univerAPI.getFormula()const nodes = formulaEngine.sequenceNodesBuilder('=SUM(A1,B2)')console.log(nodes)

Types: ISequenceNode

Package: @univerjs/engine-formula · Type definitions

FFormula.setFormulaReturnDependencyTree

Enable or disable emitting formula dependency trees after each formula calculation.

When enabled, the formula engine will emit the dependency trees produced by each completed formula calculation through the internal command system. Consumers can obtain the result by listening for the corresponding calculation-result command.

When disabled, dependency trees will not be emitted.

This option only controls whether dependency trees are exposed. It does not affect formula calculation behavior.

setFormulaReturnDependencyTree(value: boolean): void

Parameters

• value — Required. Whether to emit formula dependency trees after calculation.
  • true: Emit dependency trees after each calculation.
  • false: Do not emit dependency trees (default behavior).

Examples

const formulaEngine = univerAPI.getFormula()// Enable dependency tree emissionformulaEngine.setFormulaReturnDependencyTree(true)// Listen for dependency trees produced by formula calculationconst trees = await new Promise<IFormulaDependencyTreeJson[]>((resolve, reject) => {  const timer = setTimeout(() => {    disposable.dispose()    reject(new Error('Timeout waiting for formula dependency trees'))  }, 30_000)  const disposable = commandService.onCommandExecuted((command) => {    if (command.id !== SetFormulaDependencyCalculationResultMutation.id) {      return    }    clearTimeout(timer)    disposable.dispose()    const params = command.params as ISetFormulaDependencyCalculationResultMutation    resolve(params.result ?? [])  })})console.log('Dependency trees:', trees)

Package: @univerjs/engine-formula · Type definitions

FFormula.setMaxIteration

When a formula contains a circular reference, set the maximum number of iterations for the formula calculation.

setMaxIteration(maxIteration: number): void

Parameters

• maxIteration — Required. The maximum number of iterations. The default value is 1.

Examples

// Set the maximum number of iterations for the formula calculation to 5.// The default value is 1.const formulaEngine = univerAPI.getFormula()formulaEngine.setMaxIteration(5)

Package: @univerjs/engine-formula · Type definitions

FFormula.stopCalculation

Stop the calculation of the formula.

stopCalculation(): void

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.stopCalculation()

Package: @univerjs/engine-formula · Type definitions

@univerjs/sheets-formula

FFormula.registerAsyncFunction

Register a custom asynchronous formula function.

registerAsyncFunction(name: string, func: IRegisterAsyncFunction): IDisposableregisterAsyncFunction(name: string, func: IRegisterAsyncFunction, description: string): IDisposable

Parameters

• name — Required. The name of the function to register. This will be used in formulas (e.g., =ASYNCFUNC()).
• func — Required. The async implementation of the function.
• description — Optional. A string describing the function's purpose and usage.

Returns

A disposable object that will unregister the function when disposed.

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.registerAsyncFunction(  'RANDOM_DELAYED',  async () => {    await new Promise((resolve) => setTimeout(resolve, 500))    return Math.random()  },  'Mock a random number generation function',)// Use the function in a cellconst fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const cellA1 = fWorksheet.getRange('A1')cellA1.setValue({ f: '=RANDOM_DELAYED()' })// After 0.5 second, A1 will display a random number

// Mock a user score fetching functionconst formulaEngine = univerAPI.getFormula()formulaEngine.registerAsyncFunction(  'FETCH_USER_SCORE',  async (userId) => {    await new Promise((resolve) => setTimeout(resolve, 1000))    // Mock fetching user score from database    return userId * 10 + Math.floor(Math.random() * 20)  },  {    description: 'customFunction.FETCH_USER_SCORE.description',    locales: {      zhCN: {        customFunction: {          FETCH_USER_SCORE: {            description: '从数据库中获取用户分数',          },        },      },      enUS: {        customFunction: {          FETCH_USER_SCORE: {            description: 'Mock fetching user score from database',          },        },      },    },  },)// Use the function in a cellconst fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const cellA1 = fWorksheet.getRange('A1')cellA1.setValue({ f: '=FETCH_USER_SCORE(42)' })// After 1 second, A1 will display a score

Types: IDisposable · IRegisterAsyncFunction

Package: @univerjs/sheets-formula · Type definitions

FFormula.registerFunction

Register a custom synchronous formula function.

registerFunction(name: string, func: IRegisterFunction): IDisposableregisterFunction(name: string, func: IRegisterFunction, description: string): IDisposable

Parameters

• name — Required. The name of the function to register. This will be used in formulas (e.g., =MYFUNC()).
• func — Required. The implementation of the function.
• description — Optional. A string describing the function's purpose and usage.

Returns

A disposable object that will unregister the function when disposed.

Examples

// Register a simple greeting functionconst formulaEngine = univerAPI.getFormula()formulaEngine.registerFunction('HELLO', (name) => `Hello, ${name}!`, 'A simple greeting function')// Use the function in a cellconst fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const cellA1 = fWorksheet.getRange('A1')cellA1.setValue('World')const cellA2 = fWorksheet.getRange('A2')cellA2.setValue({ f: '=HELLO(A1)' })// A2 will display: "Hello, World!"formulaEngine.calculationEnd((functionsExecutedState) => {  if (functionsExecutedState === 3) {    console.log(cellA2.getValue()) // Hello, World!  }})

// Register a discount calculation functionconst formulaEngine = univerAPI.getFormula()formulaEngine.registerFunction(  'DISCOUNT',  (price, discountPercent) => price * (1 - discountPercent / 100),  'Calculates final price after discount',)// Use the function in a cellconst fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const cellA1 = fWorksheet.getRange('A1')cellA1.setValue(100)const cellA2 = fWorksheet.getRange('A2')cellA2.setValue({ f: '=DISCOUNT(A1, 20)' })// A2 will display: 80formulaEngine.calculationEnd((functionsExecutedState) => {  if (functionsExecutedState === 3) {    console.log(cellA2.getValue()) // 80  }})

// Registered formulas support lambda functionsconst formulaEngine = univerAPI.getFormula()formulaEngine.registerFunction(  'CUSTOMSUM',  (...variants) => {    let sum = 0    const last = variants[variants.length - 1]    if (last.isLambda && last.isLambda()) {      variants.pop()      const variantsList = variants.map((variant) =>        Array.isArray(variant) ? variant[0][0] : variant,      )      sum += last.executeCustom(...variantsList).getValue()    }    for (const variant of variants) {      sum += Number(variant) || 0    }    return sum  },  'Adds its arguments',)// Use the function in a cellconst fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const cellA1 = fWorksheet.getRange('A1')cellA1.setValue(1)const cellA2 = fWorksheet.getRange('A2')cellA2.setValue(2)const cellA3 = fWorksheet.getRange('A3')cellA3.setValue({ f: '=CUSTOMSUM(A1,A2,LAMBDA(x,y,x*y))' })// A3 will display: 5formulaEngine.calculationEnd((functionsExecutedState) => {  if (functionsExecutedState === 3) {    console.log(cellA3.getValue()) // 5  }})

// Register a simple greeting functionconst formulaEngine = univerAPI.getFormula()formulaEngine.registerFunction('HELLO', (name) => `Hello, ${name}!`, {  description: 'customFunction.HELLO.description',  locales: {    zhCN: {      customFunction: {        HELLO: {          description: '一个简单的问候函数',        },      },    },    enUS: {      customFunction: {        HELLO: {          description: 'A simple greeting function',        },      },    },  },})// Use the function in a cellconst fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const cellA1 = fWorksheet.getRange('A1')cellA1.setValue('World')const cellA2 = fWorksheet.getRange('A2')cellA2.setValue({ f: '=HELLO(A1)' })// A2 will display: "Hello, World!"formulaEngine.calculationEnd((functionsExecutedState) => {  if (functionsExecutedState === 3) {    console.log(cellA2.getValue()) // Hello, World!  }})

Types: IDisposable · IRegisterFunction

Package: @univerjs/sheets-formula · Type definitions

FFormula.setInitialFormulaComputing

Update the calculation mode of the formula. It will take effect the next time the Univer Sheet is constructed. The calculation mode only handles formulas data when the workbook initializes data.

setInitialFormulaComputing(calculationMode: CalculationMode): void

Parameters

• calculationMode — Required. The calculation mode of the formula.

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.setInitialFormulaComputing(0)

Types: CalculationMode

Package: @univerjs/sheets-formula · Type definitions

@univerjs-pro/engine-formula

FFormula.buildReference

Builds one Sheet-range or Base-table reference fragment for Facade authoring.

Use this method when the caller knows both the Host Unit ID and the stable Source Unit ID. For a cross-Unit Source, it synchronously persists the Host-owned External Reference before returning. For a Host-local Source, it only returns local reference syntax. An unchanged binding is an idempotent no-op.

The returned value is a reference fragment, not a complete formula: it has no leading = and does not start calculation. The method does not load the Source Unit. Formula calculation later reads the Source through its stable unitId.

Authoring rule:

• Cell formula: build the reference, then pass the returned fragment to range.setFormula(...); the binding has already been persisted.
• Formula Shape: build the reference, then pass the same Source identity again in shape.setFormula({ formula, externalReferences }). The duplicate binding write is a no-op and the Shape setter remains the final consistency boundary.
• Hand-written or imported formula text: call upsertExternalReference() before writing the formula because the engine never guesses unitId from a name.

buildReference(options: IBuildFormulaReferenceOptions): string

Parameters

• options — Required. Host, caller-provided source Unit, and reference target.

Returns

A formula reference fragment.

Examples

Write a cross-Unit Sheet reference to a cell

const workbook = univerAPI.getActiveWorkbook()if (!workbook) throw new Error('No active workbook.')const salesWorkbook = {  unitId: 'sales-workbook',  formulaQualifier: 'Sales Workbook',}const reference = univerAPI.getFormula().buildReference({  hostUnitId: workbook.getId(),  unit: salesWorkbook,  target: {    kind: univerAPI.Enum.FormulaReferenceType.SHEET_RANGE,    sheetName: 'Sales',    range: { startRow: 1, endRow: 9, startColumn: 1, endColumn: 1 },  },})workbook.getActiveSheet().getRange('C1').setFormula(`=SUM(${reference})`)

Build a Host-local Sheet reference without creating a binding

const workbook = univerAPI.getActiveWorkbook()if (!workbook) throw new Error('No active workbook.')const reference = univerAPI.getFormula().buildReference({  hostUnitId: workbook.getId(),  unit: {    unitId: workbook.getId(),    formulaQualifier: workbook.getName(),  },  target: {    kind: univerAPI.Enum.FormulaReferenceType.SHEET_RANGE,    sheetName: 'Sales',    range: { startRow: 1, endRow: 9, startColumn: 1, endColumn: 1 },  },})// reference contains local syntax such as Sales!B2:B10.

Types: IBuildFormulaReferenceOptions

Package: @univerjs-pro/engine-formula · Type definitions

FFormula.removeExternalReference

Removes one Host-owned External Reference by referenceId or qualifier.

Removing a binding does not rewrite or delete formulas. It marks only the Host dirty; the next Host calculation resolves the remaining formula under the normal External Reference rules.

removeExternalReference(options: IRemoveHostExternalReferenceCommandParams): boolean

Parameters

• options — Required. Host plus reference ID or qualifier.

Returns

true when an existing binding was removed.

Examples

Remove a Host binding by qualifier

const workbook = univerAPI.getActiveWorkbook()if (!workbook) throw new Error('No active workbook.')const removed = univerAPI.getFormula().removeExternalReference({  unitId: workbook.getId(),  qualifier: 'Sales Workbook',})if (!removed) throw new Error('External Reference was not found.')

Types: IRemoveHostExternalReferenceCommandParams

Package: @univerjs-pro/engine-formula · Type definitions

FFormula.upsertExternalReference

Creates or rebinds one Host-owned External Reference.

Use this explicit API when formula text is hand-written, imported, or generated in a batch without buildReference(). Call it before writing the formula. qualifier is the public name used inside the formula, without brackets or quotes; sourceUnitId is the stable identity used for data requests.

Repeating the same mapping succeeds without mutation, dirty data, or undo.

upsertExternalReference(options: IUpsertHostExternalReferenceCommandParams): boolean

Parameters

• options — Required. Host and stable Source binding.

Returns

true when the binding already matches or was persisted.

Examples

Bind a hand-written cross-workbook formula before writing it

const workbook = univerAPI.getActiveWorkbook()if (!workbook) throw new Error('No active workbook.')const formula = univerAPI.getFormula()const bound = formula.upsertExternalReference({  unitId: workbook.getId(),  qualifier: 'Sales Workbook',  sourceUnitId: 'sales-workbook',  sourceUnitType: univerAPI.Enum.UniverInstanceType.UNIVER_SHEET,})if (!bound) throw new Error('Could not bind the Sales Workbook source.')workbook.getActiveSheet().getRange('C1').setFormula("=SUM('[Sales Workbook]Sales'!B2:B10)")

Types: IUpsertHostExternalReferenceCommandParams

Package: @univerjs-pro/engine-formula · Type definitions