Reference

Reference to a single cell or range of cells.

Param

a cell address (A1, $A$1, Sheet1!A1, etc.) or range (Sheet1!A1:B2, etc.) or name (totalRevenue) or another Reference instance

Param

name of the sheet where ref should be resolved. Overridden by an explicit sheet name in ref if present.

Param

name of the workbook where ref and sheetName should be resolved. Overridden by an explicit workbook name in ref if present.

Constructors

Constructor

new Reference(ref, options?): Reference

Parameters

ref

a cell address or name, or another Reference (copy) or Range.

string | Range | Reference

options?

ReferenceOptions = {}

Returns

Reference

Throws

if ref is not a Reference and not a Range and cannot be parsed as a Reference.

Properties

conditional

readonly conditional: boolean

True if the referenced cell(s) are not certain to be required up-to-date before the depending cell is evaluated.

For instance, IF(A1, B1, C1) will reference B1 and C1 conditionally (and may need neither of them, if A1 resolves to an error value), so we need not, and must not, require B1 and C1 to be up-to-date before evaluating the IF.

Likewise HLOOKUP(A1, my_range, 2) will reference the first row of my_range unconditionally but the remainder conditionally, and in the end at most one cell out of my_range will actually be required up-to-date.

So conditionally-referenced cells will be checked for up-to-dateness when the referencing formula is evaluated, and if they are not up-to-date, the evaluation will throw EvaluationOrderException to force queue reordering.

ctx

readonly ctx: undefined | EvaluationContext

dynamic

readonly dynamic: boolean

True if this reference originates from a reference function whose result cannot be determined at dependency graph construction time, because it depends on the runtime arguments or spill state. These are INDIRECT(...) and OFFSET(...) and ANCHORARRAY.

Such a reference is not represented in the dependency graph when initially constructed, and thus is not determined until the formula is evaluated in recalculation. It is then recorded as a dynamic dependency, so that subsequent recalculations (or the same one upon redo-propagation to the same formula cell) can account for the dependency in queue ordering and propagation.

name

readonly name: null | string

nonValue

readonly nonValue: boolean

If true, this reference will not be used to read cell values. This is for use by dependencies such as that of FORMULATEXT(A1), which depends on the formula of cell A1 but not on its value, so should not require A1 to be up-to-date before the depending cell is evaluated.

range

readonly range: null | Range

sheetName

readonly sheetName: string

trim

readonly trim: NumericTrim

workbookName

readonly workbookName: string

parse()

readonly static parse: (refStr) => null | Readonly<ParsedReference> = parseReference

This returns an object with a breakdown of the reference or a null if the reference wasn’t valid.

Parameters

refStr

string = ''

The reference to parse

Returns

null | Readonly<ParsedReference>

Accessors

_

Get Signature

get _(): undefined | EvaluationContext

get isAddress(): boolean

_resolveArea<O>(options): AreaArray<AreaArrayElement<O>>

Resolve this reference to an AreaArray, or if that’s not possible, throw (don’t return) a FormulaError instance. NOTE: the area array is populated only where the sheet is populated, so the array itself may have smaller dimensions if this reference extends beyond sheet bounds, but will have bounds and default-value information to to represent the rest.

Type Parameters

O

O extends Readonly<{ returnBoxed: boolean; returnCells: boolean; returnLambda: boolean; }>

Parameters

options

ResolveAreaOptions<O>

Returns

AreaArray<AreaArrayElement<O>>

an area array, in which:

sheetName is exactly the sheetName of this reference (which may be null, and if not null, may differ in case from the actual name of the referenced sheet)
workbookName is exactly the name of the workbook to which the workbookName of this reference resolves (which is the default workbook of this reference’s resolver, if this reference does not have a workbookName). Note that unlike the sheetName attribute, this workbookName attribute can differ in case from that of the reference.

Throws

if:

this is a name reference and the name is not found, or its formula evaluates to a string or number or error, or fails to evaluate due to a circular dependency in defined names referencing one another
this reference’s workbook is not found in this reference’s resolver (can also happen if this reference has no workbookName, if there are no workbooks in this reference’s resolver, though this is probably an edge case)

_resolveToNonName()

_resolveToNonName(opts?): object

Inner implementation of resolveToNonName that also returns the defined-name Cell object from whose formula the result is obtained.

Parameters

opts?

EvaluationContext

the evaluation context, if not this.ctx

Returns

object

result of evaluating the formula of the last defined name that does not result in another name reference (else this if not a name reference, or #NAME? if any name is not found, or if following defined-name formula results leads into a loop of name references), and that last defined-name cell object if there is one. In the case of a reference loop there isn’t one. The resolved may be a reference, but is guaranteed not to be a name reference.

nameCell?

optional nameCell: DefinedName

resolved

resolved: A1Reference | Matrix | MaybeBoxed<ArrayValue>

[iterator]()

[iterator](): IterableIterator<A1Reference>

Iterate over all cells of this range, yielding a single-cell address reference for each cell. The Reference instance yielded from this generator is reused, so the consumer must not hold on to it, only use it during each yield.

Returns

IterableIterator<A1Reference>

Throws

if this is not an address reference and cannot be resolved to one

any()

any(predicate): boolean | FormulaError

Return true if any of the referenced cells satisfy the given predicate, else false.

Parameters

predicate

(arg0) => boolean

a function returning true/false for a given cell

Returns

boolean | FormulaError

true if the predicate returns true for one of the referenced cells. Error if:

this is a name reference and the name is not found, or resolves to a string or number or error, or encounters a circular dependency via defined names referencing one another
this reference is workbook-qualified
this reference has no resolver

applyTrim()

applyTrim(): FormulaError | Reference

Returns

FormulaError | Reference

collapse()

collapse(): A1Reference

Return a cloned instance with range set to the top-left corner of this reference’s range.

Only call this for an address reference, it will throw on a name reference.

Returns

A1Reference

collapseToCell()

collapseToCell(r?, c?): never

Return a cloned instance that refers to the cell at row r, column c (zero-based) of this range. This must be an address reference (having a .range and not a .name). Note that bounds checking is not performed; this can yield a cell outside this range. The returned instance is guaranteed to be new, even if it is identical to this.

Parameters

r?

number = 0

which row to get (0 for first, 1 for second, etc.)

c?

number = 0

which column to get (0 for first, 1 for second, etc.)

Returns

never

a reference like this one but narrowed to that cell.

collapseToColumn()

collapseToColumn(c?): A1Reference

Return a cloned instance that refers to column c (zero-based) of this range. This must be an address reference (having a .range and not a .name). Note that bounds checking is not performed; this can yield cells outside this range.

Parameters

c?

number = 0

which column to get (0 for first, 1 for second, etc.)

Returns

A1Reference

a reference like this one but narrowed to that column. The returned instance is guaranteed to be new, even if it is identical to this.

collapseToNthCell()

collapseToNthCell(n): never

Return a reference to the zero-based nth cell in a left-to-right-then-top-down traversal of this range. This must be an address reference (having a .range and not a .name). Note that bounds checking is not performed; this can yield a cell outside this range. The returned instance is guaranteed to be new, even if it is identical to this.

Parameters

n

number = 0

zero-based index of the cell to return

Returns

never

reference like this one except narrowed to the single cell specified.

collapseToRow()

collapseToRow(r?): A1Reference

Return a new Reference that refers to row r (zero-based) of this range. This must be an address reference (having a .range and not a .name). Note that bounds checking is not performed; this can yield cells outside this range.

Parameters

r?

number = 0

which row to get (0 for first row, 1 for second, etc.)

Returns

A1Reference

a reference like this one but narrowed to that row. The returned instance is guaranteed to be new, even if it is identical to this.

contains()

contains(other): boolean

Parameters

other

Reference

Returns

boolean

cropToSheet()

cropToSheet(sheet): Reference | A1Reference

Parameters

sheet

WorkSheet

Returns

Reference | A1Reference

getCellId()

getCellId(): string

Get this reference’s name if this is a name reference, else the unprefixed A1 address of the top-left corner cell.

Returns

string

getRefId()

getRefId(cellID): string

Get fully-qualified cell ID. This is either a global name or a cell address prefixed with sheet name (‘Sheet1!A1’) and (if present) a workbook pathname. The sheet name (or global name) is cased as in this reference (or in cellID) which may be arbitrary. If you need consistent casing, use getCanonicalCellId.

Parameters

cellID

optional, cell ID to qualify. If null (the default), use the (top-left) cell ID of this reference.

null | string

Returns

string

the cell ID prefixed with sheet name and with workbook pathname if present

is1D()

is1D(): boolean

Returns

boolean

isResolvable()

isResolvable(): boolean

Returns

boolean

offset()

offset(rows, cols, height, width): A1Reference

Return a cloned instance with the range shifted and/or changed in size.

Only call this for an address reference, it will throw on a name reference.

Parameters

Generate a prefix according to the workbook name and directory and the sheet name of this reference. The prefix may be empty, but if it is not, it ends with ’!’ so that the reference range expression can be concatenated directly onto it.

Returns

string

the prefix for this reference

resolveAreaBoxed()

resolveAreaBoxed(): AreaBoxedValueArray

Returns

AreaBoxedValueArray

resolveAreaCells()

resolveAreaCells(cropTo?): AreaCellArray

Parameters

cropTo?

"any-cell-information" | "cells-with-non-blank-values"

Returns

AreaCellArray

Throws

if:

this is a name reference and the name is not found, or its formula evaluates to a string or number or error, or fails to evaluate due to a circular dependency in defined names referencing one another
this reference’s workbook is not found in this reference’s resolver (can also happen if this reference has no workbookName, if there are no workbooks in this reference’s resolver, though this is probably an edge case)

resolveAreaValues()

resolveAreaValues(): AreaValueArray

Returns

AreaValueArray

Throws

if:

this is a name reference and the name is not found, or its formula evaluates to a string or number or error, or fails to evaluate due to a circular dependency in defined names referencing one another
this reference’s workbook is not found in this reference’s resolver (can also happen if this reference has no workbookName, if there are no workbooks in this reference’s resolver, though this is probably an edge case)

resolveCell()

resolveCell(): null | CellItem

Returns

null | CellItem

resolveCellOrDefinedName()

resolveCellOrDefinedName(): null | CellItem

Resolve a single cell from this reference. This differs from resolveCell and resolveAreaValues and resolveAreaCells in that:

if a defined-name formula evaluates to a non-reference, this returns the defined-name cell object itself (where resolveCell would return a blank cell object, and resolveAreaValues and resolveAreaCells would return an error, wrapped in a cell object in the latter case)
if this (or the result of the last defined-name that does not evaluate to a name reference) is a range reference, this returns the top left cell of the referenced range, but resolveCell returns an artificial blank cell.

The reference must have a context, else this will throw.

Returns

null | CellItem

resolveName()

resolveName(opts?): object

Resolve name reference if that’s what this is, returning what its formula resolves to. If this is already an address range reference, it is just returned. If it is a name reference, the rawOutput result of that name’s formula is returned. That may be:

another reference (if the formula is e.g. =Sheet1!A1)
a number, string, boolean or FormulaError (for e.g. =A1+B2, =A1&B2, =A1=B2, or =IDONTEXIST())

If the defined name has no last-computed value, or it is stale, or the formula is context-dependent, then the formula must be evaluated in this call. If this happens, the result is just returned but is not assigned as the last-computed value (.v) of the defined-name object. This is because the change of .v here would not propagate to dependents (because it is not occurring in the recalculation algorithm) — and then when recalculation happens, it may evaluate this defined-name formula again and get the same value, and thus consider it up-to-date and not propagate recalculation to dependents … so recalculation may incorrectly fail to update those dependent cells.

Parameters

opts?

EvaluationContext

the evaluation context, if not this.ctx

Returns

object

resolved: the result of evaluating the named formula (#NAME? if the name is not found), and nameCell: the defined-name cell object to which the name was resolved

nameCell?

optional nameCell: DefinedName

resolved

resolved: MaybeBoxedFormulaValue

resolveRange()

resolveRange<Boxed>(opts?): FormulaError | Boxed extends false ? ArrayValue : MaybeBoxed<ArrayValue>[]

Return the cell values of the given range, optionally skipping blank cells. Default is to skip unpopulated blanks, i.e. blank values in unpopulated areas.

Type Parameters

Boxed

Boxed extends boolean = false

Parameters

opts?

IterationOptions<Boxed>

Returns

FormulaError | Boxed extends false ? ArrayValue : MaybeBoxed<ArrayValue>[]

the specified cell values, or error if this is a name reference that cannot be resolved, or whose formula resolves to an error or a non-range-reference value.

resolveSingle()

resolveSingle(): ArrayValue

Returns

ArrayValue

resolveSingleBoxed()

resolveSingleBoxed(): MaybeBoxed<ArrayValue>

Returns

MaybeBoxed<ArrayValue>

resolveToNonName()

resolveToNonName(opts?): A1Reference | Matrix | MaybeBoxed<ArrayValue>

Resolve this name reference (if that’s what this is) and evaluate its formula, and if that yields a name reference, recurse until something other than a name reference is obtained, or a loop is detected. If a loop is detected, return ERROR_NAME with a circular dependency detail message. Else return the first non-name-reference result. If this reference is already not a name reference, just return this.

Parameters

opts?

EvaluationContext

the evaluation context, if not this.ctx

Returns

A1Reference | Matrix | MaybeBoxed<ArrayValue>

this reference if it is not a name reference, else the first defined-name formula result that isn’t a name reference, else ERROR_NAME if defined-name formula results form a loop of name references leading back to this reference. Guaranteed not to return a name reference.

resolveWorkbookAndSheet()

resolveWorkbookAndSheet(resolver?, requireSheet?): FormulaError | { sheet: null | WorkSheet; workbook: Workbook; }

Parameters

resolver?

null | EvaluationContext

requireSheet?

boolean = true

set to false to return a null sheet rather than throw an error

Returns

FormulaError | { sheet: null | WorkSheet; workbook: Workbook; }

error if workbook fails to resolve, or if sheet fails to resolve and requireSheet is true

Throws

if this reference is missing a resolver.

toMatrix()

toMatrix(expandError): FormulaError | Matrix

Produce a Matrix populated with the cell values from the range of this reference.

Parameters

expandError

boolean = true

Returns

FormulaError | Matrix

toString()

toString(abs): string

Parameters

abs

boolean = false

Returns

string

visitFormulaCells()

visitFormulaCells(callback): void

Visit the formula cell(s) pointed to by this reference. If this is a name reference, visit that single defined name cell. Precondition: Resolver (this._) must be set.

Parameters

callback

CallbackWithStop<CellItem>

Returns

void

withContext()

withContext(ctx): Reference

Return a cloned instance with the given evaluation context. Used to be called resolver in many places.

Parameters

ctx

undefined | EvaluationContext

Returns

Reference

withPrefix()

withPrefix(p): Reference

Return a cloned instance with the given changes made to prefix properties. Any that are null or absent are not set (but you can pass ” to reset them).

Parameters

p

sheetName?

string

workbookName?

string

Returns

Reference

withRange()

withRange(range): A1Reference

Return a cloned instance with the given range.

Parameters

range

Range | { $bottom?: boolean; $left?: boolean; $right?: boolean; $top?: boolean; bottom?: number; left: number; right?: number; top: number; unbounded?: number; }

Returns

A1Reference

withTrim()

withTrim(trim): A1Reference

Parameters

trim

Trim

Returns

A1Reference

from()

readonly static from<Arg>(cellRef, options?, silent?): Arg extends Range ? A1Reference : Arg extends Reference ? Arg<Arg> : null | Reference

Type Parameters

Arg

Arg extends string | Range | Reference | A1Reference | NameReference

Parameters

cellRef

Arg

options?

ReferenceOptions

silent?

boolean = true

Returns

Arg extends Range ? A1Reference : Arg extends Reference ? Arg<Arg> : null | Reference