Named Expressions and Structured References

TL;DR;

Sometimes columns (and rows) have customized names. Using the A1 or R1C1 addressing in this context is very confusing for the end-user. We need a way to translate columns names to addresses.

(note: I stole the image from our friends. I hope they don't mind, they need this feature as well.)

Stages

• Stage 1: #239 API and internal structure
• Stage 2: #240 formula syntax
• Stage 3: #241 structured references
• Stage 4: #273 Structured References keywords

Description

The syntax here is based on Open Document Format Formula named expressions[3][4] and Excel structured references[5]. It should be 100% compatible with both of them and provides an extension to provide header names if the named expression/structured reference is called in the context of the worksheet (Handsontable instance). This is amazing how those things fit in together nicely.

When a Named Expression is a table (or worksheet) then we can query it deeper with structured references by using square brackets: [ ].

This spec is extended #27 and will close #5 as well.

Scopes

Both Named Expression and Structured References define two scopes: Worksheet (Local) and Workbook (Global). Each scope can have only unique names but if a name is in both there is a rule of precedence, which both specs define the same: If two variables have the same name, then the local scope takes the precedence over the global one.

Properties

• Name: String. Name of the expression. Required.
• Value: CellValue. Calculated from the Reference field. Read-only, calculated from reference.
• Reference: Cell, Range, Formula, Constant or Table. Required.
• Scope: String. "Workbook" or {Worksheet Name}. Default: Workbook
• Comment: String. Free for whatever. Default: undefined
• Visible: Boolean. Sets if the name should be private. Not displayed in the UI. Default: true
• Type: String. Each can be marked with a different icon in the UI. Read-only, calculated from reference.

Types

• Defined names:
  • Reference. If the expression does start with equality sign: range =A1:C30 or cell =A1
  • Formula. If the reference contains the formula: =SUM(A1:A5)
  • Constant. If reference is just a value: 3, "some string", 10/12/2020
• Defined table. If the expression does not start with equality sign: E1:G26 it is a table where the first row is a header and the last row is a total.

We could then add some built-in aliases (or added by Handsontable) and close #5 issue.

• { name: 'TRUE', reference: '=TRUE()', visible: false, comment: "Built-in variable for compatibility with other spreadsheet apps" },
• { name: 'FALSE', reference: '=FALSE()', visible: false, comment: "Built-in variable for compatibility with other spreadsheet apps" },

Methods

options = { comment, visibility }

• engine.addNamedExpression(name, value, scope, options)
• engine.removeNamedExpression(name, scope)
• engine.changeNamedExpression(name, newValue, newScope, newOptions)
• engine.getNamedExpressionValue(name, scope)
• engine.getNamedExpression(name, scope)
• engine.listNamedExpressions(scope?) - Return all named expressions as an array of objects.

CRUD

When CRUD operations are performed they should update named expressions references. Adjust references to single cells, expand or collapse ranges and tables.

Copy&Paste

@swistak35 noted that we need to check how copy&paste of named expressions is handled by other apps. Destination worksheet names may not exist in the target worksheet. Sometimes they will fall back to the global scope or were created with global scope and are now shadowed by local scope named expression.

Batch operations

It should be possible to add/remove multiple named expressions without triggering recalculation each time we call addNamedExpression method.

Syntax

There are three parts: =TableName[[RowName][ColumnName]] with some small differences. This is an absolute referenced table cell that is relative to the current worksheet.

To have totally absolute cell reference the table name can be prefixed with the worksheet name: =Sheet1!TableName[[RowName][ColumnName]]. To reference worksheet top header just omit the TableName ie: =Sheet1![[RowName][ColumnName]]

If table name is omitted: =[[RowName][ColumnName]] engine will check if we're already in defined table. This is an absolute cell in a relative table. If we're not in a table range, this will reference the worksheet headers.

If row name is omitted: =TableName[ColumnName] or =[ColumnName] the row is relative to the current row.

If the row is not provided the query should return the whole column. Same as: A:A.

If the column is not provided the query should return the whole row. Same as 1:1.

The external widget would need a worksheet reference: =Sheet1![[RowName][ColumnName]] to reference the cell of Handsontable instance. And since we're outside of any defined table this should look for a global column and row names.

If the name contains special characters it can be enclosed by additional square brackets: [[Column # Name]]. Probably we should keep this syntax for compatibility with other apps.

A row can also be one of those helpful keywords:

• [#All] will return all rows: data, headers and totals.
• [#This Row] or [@] or @[ColumnName] relative to the row (default when row is omitted).
• [#Headers] for the header row.
• [#Totals] for summary/totals row.
• [#Data] for data rows without headers and totals.

Reference operators can be applied (syntax depends on configuration #58). Use additional brackets around expressions with operators [ ].

• Range: =Table[[Column 1]:[Column 2]] same as A2:B7 (colon)
• Union: =Table[[Column 1]:[Column 2]],Table[[Column 4]:[Column 5]] same as A2:B7, D2:E7 (comma)
• Intersection: =Table[[Column 1]:[Column 2]] Table[[Column 4]:[Column 5]] same as A2:B7 D2:E7 (space). Note: row and column is an union: [Row] [Column] but we can omit it

Whitespace should be preserved:

• After the first left bracket ([ )
• Preceding the last right bracket ( ]).
• After a comma (, )

The problem: where is Handsontable in all of that

When we're not in any named table, or Handsontable ID is used as a table name then the RowName, ColumnName, [#Headers] should refer to Handsontable column headers and row headers. For this to work, we have to provide the mapping between name and index.

How do we feed headers to HyperFormula from an external source?

1. We can set them as arrays: rowHeaders, columnHeaders and mapping is done by HF internally both ways.
2. We have to define callbacks to get rowHeader name by index but also to get index by name:
   • getRowHeaderByIndex(sheet, index) { return rowHeaders[sheet][index]; }
   • getRowHeaderByName(sheet, name) { return rowHeaders[sheet].indexOf(name); }

Naming rules

• Up to 255 characters length.
• Names can contain large and small letters but matching is case insensitive. The evaluator engine should normalize the name. The registered name should be used when stringifying AST.
• Names that look like a cell reference are not allowed. A1: $A$100, or R1C1.
• The first character should be a letter or an underscore. The rest can contain letters, numbers, periods and underscores.
• Whitespace is not allowed in named expression. It is allowed inside structured references enclosed in square brackets [ ] and should be preserved to improve readability.
• Some characters have special meaning for syntax: [, ], # and '. They should be escaped with single quotation mark '.

Other

• I've checked Airtable syntax[1] (mustache like { }), but it's limited to current row references.
• Some differences apply to DAX syntax[2] where table name can contain white space. I was considering this as an extension that would be "backward" compatible when copying data from other spreadsheet software, but it would break exporting our data to them. So no, we shouldn't do that.
• Named Expressions should be part of the formula editor IntelliSense. Unless they are marked as hidden.
• With the support for constant arrays, we would have everything we need to provide built-in data sets i.e Countries list

Links

[1] https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference
[2] https://docs.microsoft.com/en-us/dax/dax-syntax-reference
[3] https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017964_715980110
[4] https://support.office.com/en-us/article/names-in-formulas-fc2935f9-115d-4bef-a370-3aa8bb4c91f1
[5] https://support.office.com/en-us/article/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

Start by reading the remaining Stage 3 structured-references work and Stage 4 keywords work, along with the listed engine methods and syntax rules. Determine how worksheet and workbook scopes, header-name mappings, CRUD updates, and structured-reference operators fit together. Done means the specified named-expression and structured-reference behavior is implemented and compatible with the documented examples.