Worksheet Node

Worksheet🔗

Overview🔗

A Worksheet provides a grid of cells for entering and displaying data. Each cell may contain formulas and refer to other cells.

The Worksheet Node uses the Rune language to define formulas and code. The Rune language is similar to Rust. Unlike Rust, Rune doesn't require type annotations, and it is designed to run in a sandboxed environment.

Keyboard🔗

The Worksheet Node supports the following keyboard shortcuts:

Control + Shift + H: Toggle a cell's HTML/Markdown mode. If enabled, any HTML or Markdown will be rendered as HTML. This is particularly useful for rendering SVG charts/plots.
Navigation:
- Copy / Paste (platform-dependent, usually Ctrl+C / Ctrl+V or Cmd+C / Cmd+V): Copy and paste the selected cell.
- Arrow keys: Move the selection up, down, left, or right.
- Enter: Edit the selected cell
- Page Up / Page Down: Scroll the worksheet up or down.
- Home: Move the selection to the first cell in the row (top).
- Home + Ctrl: Move the selection to the first cell in the worksheet (top-left).
- Home + Alt: Move the selection to the first cell int row (left).
Editing Cell:
- Escape: Cancel editing the cell, returning the original value.
- Enter + Shift: Add a new line in the cell.
- Enter: Save the changes and move the selection down.
- Enter + Ctrl: Save the changes and move the selection left.
- Enter + Alt: Save the changes and move the selection right.
- Enter + Ctrl + Alt: Save the changes and move the selection up.

Cell Formulas🔗

The result of a formula is displayed in the cell. The formula is evaluated when the cell is updated.

If the first character is = then it's interpreted as a formula. If it's a single quote ' then it's interpreted as a string. Any other value will attempt to interpret it as a value, e.g. 43.2 will become an f64 and some text will be interpreted as a string.

Below is a basic example of multiplying two numbers:

=23*53

Result: 1219

More complex formulas can be created using variables and functions. For example:

=let a = 23;let b = 53; a*b

Other cells can also be referenced using the cell and cellr functions. For example:

=cell(5, 10) * cellr(-1, -1)

cell - Returns the value of the cell at the specified row, and column.
cellr - Returns the value of the cell at the specified row and column relative to the current cell.

Data Types🔗

The worksheet node uses the Rune scripting language for formulas, which means that data types are more strict than traditional spreadsheet formula languages. In general, integers and floating point numbers will not automatically be compatible in mathematical calculations. In these situations and error will be displayed when types are incompatible and require manual intervention.

Numbers without a decimal point are represented as 64 bit integers. Numbers with a decimal point are represented as 64 bit floating point numbers.

The Decimal type is available for calculations requiring improved precision without rounding, such as for financial calculations. Numbers are defined as Decimal using the d(..) function.

=d("0.1")+d("0.2") // results in 0.3

The d(..) function also accepts numeric values

=(d(0.1) + d(0.2)) * d(43) // the d(..) function can convert both floats and integers into Decimal numbers

Using floating point numbers instead in scenarios requiring Decimal precision can result in incorrect results:

=0.1+0.2 // results in 0.30000000000000004

=(0.1 +0.2) * 43.0 // results in 12.900000000000002

Performing floating point operations inside a call to the Decimal function will result in the inner floating point operations calculating first. Take care to ensure if decimal precision is required that the d(..) function is called before performing any calculations.

Node Inputs🔗

As a node in the graph, the Worksheet Node can connect to other nodes to receive data. The number of inputs available is configured in the Configuration Panel.

Data in connected upstream nodes can be accessed by a worksheet depending on the channel's data format. RecordBatch, JSON, and raw text are currently supported.

RecordBatch Input Data🔗

Data from a RecordBatch may be accessed with the input command: =input(INPUT_ID, CHANNEL_NAME, ADDRESS)

Address can be a single cell, a range, a whole column or a whole row. =input(0, "Data", column("SepalLengthCm")) - selects the entire SepalLengthCm column from Input 0's Data channel.

JSON and Raw Text🔗

These data types currently only support fetching the entire record using input_all =input_all(INPUT_ID, CHANNEL_NAME)

For example, this will fetch the Plot channel on input 1 =input_all(1, "Plot")

Upstream node data is available to cells depending on what format it's in:

Tabular data in application/vnd.apache.arrow format will be displayed as a worksheet and can be referenced in formulas.
Object data in application/json format will be displayed as a single cell with the data's string representation.
Other data will be displayed as a single cell with the data's string representation. Cell formulas can not generally access the contents of the data except as a string or byte array.

Regardless of the data format, the data can be passed around the worksheet, manipulated to the extent that the data format allows, and passed to downstream nodes.

Node Outputs🔗

A Worksheet node can have several outputs. Outputs are configured in the Configuration Panel. Each output provides two channels: a RecordBatch channel, which can generally be used by machine learning nodes, and a JSON channel.

All Supported Formula Commands🔗

General Supporting Functions🔗

Command	Example	Description
`address(column, row)`	`=address(1, 2)`	Generates an address to a cell a sheet. Generally used with the `input` command.
`addresses([values])`	`=addresses([(1,2), (1,3)])`	Generates a list of addresses to cells in a sheet. Generally used with the `input` command.
`address_range((a,b), (x,y))`	`=address_range((10,10), (20,20))`	Generates a rectangular window of addreses in a sheet. Generally used with the `input` command.
`column(column)`	`=column(43)`	Selects an entire column. Generally used with the `input` command.
`d(value)`	`=d("4.001")`	Defines a Decimal number. Decimal numbers are suitable for financial calculations. This implementation uses the Rust Decimal crate.
`json(value)`	`=json([2,3,4])`	Creates a JSON map
`row(row)`	`=row(15)`	Selects an entire row. Generally used with the `input` command.

Data Retrieval Functions🔗

Note that some functions may return multiple values. In these cases, the results will be an array.

Command	Example	Description
`cell(column, row)`	`=cell(1, 2)`	Retrieves the value from the specified cell address.
`cellr(offset_x, offset_y)`	`=cellr(-1, 0)`	Retrieves the value from the cell relative to the current cell's position.
`input(input_id, channel_name, key)`	`=input(0, "Data", column("SepalLengthCm"))`	Retrieves the data from the specified input channel. Key may be any addressing value, such as column, row, address_range, etc.
`input_all(input_id, channel_name)`	`=input_all(1, "Plot")`	Retrieves the data from the specified input channel.

Aggregate Operation Functions🔗

Note that these operations work on both static arrays, as well as calculated arrays and data fetched as arrays from input channels.

Command	Example	Description
`sum(values)`	`=sum([1, 2, 3])`	Adds values together.
`product(values)`	`=product([1, 2, 3])`	Multiplies values together.
`count(values)`	`=count([1, 2, 3])`	Counts the number of elements in the list.
`min(values)`	`=min([1, 2, 3])`	Returns the minimum value in the list.
`max(values)`	`=max([1, 2, 3])`	Returns the maximum value in the list.
`average(values)`	`=average([1, 2, 3])`	Provides the average value of the list.