Spreadsheet

The Spreadsheet component provides an interactive interface for viewing and editing spreadsheet data in dashboards.

The Spreadsheet allows users to work with values, formulas, formatting and styling, comments, grouped rows or columns, and conditional formatting within a single Spreadsheet view.

A Spreadsheet is not an isolated dashboard component. It is backed by a Model, which stores Spreadsheet data, evaluates expressions, and synchronizes changes between users. The dashboard component serves as the user interface, while the Model provides the underlying Spreadsheet state and processing logic.

Depending on the Model Type, a Spreadsheet may exist in different contexts. In an absolute model, a single Spreadsheet is shared across the entire system. In an instantiable model, each instance can have its own Spreadsheets. In a relative model, Spreadsheets can be associated with relative objects such as devices, users, or other entities.

Spreadsheet cells may reference platform data by using Standard Reference syntax. These references are used inside expressions, which work like classic spreadsheet formulas. This allows the Spreadsheet to be used not only as a visual table, but also as an interface for viewing and editing values from model variables and other accessible contexts.

Spreadsheet Session

When a dashboard containing a Spreadsheet component is opened, the system retrieves the Spreadsheet definition from the Model, creates a Spreadsheet session, and renders the Spreadsheet in the dashboard.

A Spreadsheet session represents the runtime state of the Spreadsheet during user interaction. It may contain temporary user changes, evaluated expressions, and other intermediate data required while the Spreadsheet is open. Multiple Spreadsheet sessions may exist at the same time when several users work with the same Spreadsheet.

Cells and Ranges

Spreadsheet operations may address either individual cells or rectangular cell ranges.

Cell-based operations are used for working with specific cell content, such as values, expressions, styles, formats, or comments. Range-based operations are used for actions that apply to rectangular areas of the Spreadsheet, such as clearing cells, merging cells, unmerging cells, or recalculating formulas for a selected area.

Cell Structure

A Spreadsheet cell may contain several independent layers of information.

Layer

Description

Value or Error

The current value stored in the cell. If the cell evaluation results in an error, the cell contains error information, for details, see Error Codes.

Expression

An expression is a Spreadsheet formula used to calculate a cell value.
Expressions can reference other cells (using A1 notation), functions, and platform data via Standard Reference syntax.

Format

Formatting applied to the cell value.

Style

Visual styling of the cell.

Comments

User comments attached to the cell.

Different Spreadsheet functions modify different layers of the cell.

A cell can contain either a direct value or an expression. An expression calculates the cell value based on other cells, Spreadsheet functions and other parts of the Unified Data Model. When a value changes, the spreadsheet engine recalculates dependent formulas.

In the Spreadsheet component, enter expressions in the formula bar above the spreadsheet grid, not directly in cells. Expressions work like classic spreadsheet formulas, but unlike traditional spreadsheet applications, expressions do not require the = prefix.

Example: sum(A1, B1)

Error Codes

When a cell formula cannot be evaluated, the Spreadsheet displays an error code in the cell instead of the calculated value.

Error Code

Description

#UNKNOWN!

Unknown error. The formula could not be evaluated, and the exact error type is not available.

#NULL!

Null value. The formula refers to a null, empty, or unavailable value where a valid value is required.

#DIV/0!

Division by zero. The formula divides a value by zero or by an empty cell.

#VALUE!

Invalid value. The formula uses a value of the wrong type, such as text where a number is expected.

#REF!

Invalid reference. The formula refers to a cell, range, or data reference that does not exist or cannot be accessed.

#NAME?

Unknown function or name. The formula contains a function name, variable, named range, or reference that is not recognized.

#NUM!

Invalid number. The formula produces an invalid numeric result or uses a number outside the supported range.

#N/A

Value not available. The requested value could not be found or is not available.

#ERROR!

Evaluation error. The formula could not be evaluated or the result is not available.

Public Variables (Properties)

[?]

The Spreadsheet component has the following basic properties and property groups that are common to all components:

Name, Advanced Properties, Container Properties, Styles, Preloader, Events, Bindings, Kebab Menu.

Inherited Variables (Properties)

The Spreadsheet component is internally based on Data Table component, so it inherits its shared properties.

Custom Variables (Properties)

Spreadsheet

This property defines parameters of the Spreadsheet managed by this component.

Variable Name:

spreadsheetParameters

Records:

1

Record Format:

Field Name

Field Type

Field Description

modelContextPath

String

Path to the model context where the spreadsheet is stored.

name

String

Name of the Spreadsheet.

ranges

Data Table

Defines Spreadsheet ranges.

savingMode

Integer (0 by default)

Defines how Spreadsheet changes are saved.

Supported values:

0 — On Each Change;

1 — Manually;

2 — Disabled.

When saving is disabled, a new Spreadsheet copy is created each time the component is opened. Changes made in one copy of the same Spreadsheet do not affect other copies.

evaluateOnOpen

Boolean

Specifies whether the cell formulas and conditional bindings are (re)evaluated when the spreadsheet is opened.

nameSuffix

String (virtual by default)

Suffix appended to the Spreadsheet name when savingMode is set to 2 (Disabled).

concurrentEditing

Boolean

Specifies whether multiple users can edit the Spreadsheet simultaneously.

Maximum Permission Level

This property defines the maximum permission level available in the Spreadsheet. If a user has a higher permission level in the system, the effective permission level in the Spreadsheet component is limited by this value, and only operations allowed by that effective level remain available.

Variable Name:

maximumPermissionLevel

Records:

1

Record Format:

Field Name

Field Type

Field Description

maximumPermissionLevel

String

Defines the maximum permission level available in the Spreadsheet. If a user has a higher permission level in the system, the effective permission level in the Spreadsheet is limited by this value.

Selected Ranges

This property defines the selected cell or cell range in the Spreadsheet. It is used to set the default position when the Spreadsheet is opened and to track the cell or range currently focused by the user.

Variable Name:

selectedRanges

Records:

0…Unlimited

Record Format:

Field Name

Field Type

Field Description

startRowIndex

Integer

Defines the zero-based index of the first row in the selected range.

endRowIndex

Integer

Defines the zero-based index of the last row in the selected range.

startColumnIndex

Integer

Defines the zero-based index of the first column in the selected range.

endColumnIndex

Integer

Defines the zero-based index of the last column in the selected range.

Lazy Load

Enables lazy loading mode. When enabled, Spreadsheet data is loaded on demand rather than all at once when the component is opened. This may improve performance when working with large spreadsheets.

Variable Name:

lazyLoad

Records:

1

Record Format:

Field Name

Field Type

Field Description

lazyLoad

Boolean

Enables or disables lazy loading mode.

Was this page helpful?