Named Expressions and Structured References

[wojciechczerniak]

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

• [x] Stage 1: #239 API and internal structure
• [x] 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

• [x] Defined names:
  • [x] Reference. If the expression does start with equality sign: range =A1:C30 or cell =A1
  • [x] Formula. If the reference contains the formula: =SUM(A1:A5)
  • [x] 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 }

• [x] engine.addNamedExpression(name, value, scope, options)
• [x] engine.removeNamedExpression(name, scope)
• [x] engine.changeNamedExpression(name, newValue, newScope, newOptions)
• [x] engine.getNamedExpressionValue(name, scope)
• [ ] engine.getNamedExpression(name, scope)
• [x] 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

[marek-baranowski]

+100 🔥🔥🔥

Great analysis! And happy to see this feature added to the roadmap, for me it's essential ✌️ I was actually planning to give this task a try soon. Will be happy to help, I was dealing with a similar case at Reactivepad, with tables and structured references:

BTW. do you maybe have some data on how your users use Handontable in most cases, 1. as a spreadsheet; 2. as a table (like column names instead of A, B, C etc.)?

That's one of the toughest design problems in Reactivepad - to give people a familiar spreadsheet which doesn't fit very well within document (among many other issues) or to stay with table & calculated columns, something that many big players (Notion, Airtable, Coda) already successfully deployed.

[wojciechczerniak]

Thanks! And thank you for the real use case example.

We don't have such data. Handsontable can be used in both scenarios and it's up to the developer which one she will choose for her end users. Notion and Airtable have a database like tables where each row is a record, they have row relative notation. But there is also Quip which took another way and has A1 notation within the document and it doesn't feel like it does not fit well within the document. I think that both are valid approaches.

[marek-baranowski]

@wojciechczerniak just wanted to ask about the realistic launch date of this feature :) Of course no rush from my side, I know you have your schedules. Just wondering if I should start working on this, either as an alternative implementation or maybe as the contribution to the official code.

[wojciechczerniak]

@marek-baranowski We have a call scheduled next Monday where we will discuss tasks for February/roadmap. Until then I don't have any ETA, sorry.

[marek-baranowski]

sure, no problem.

[marek-baranowski]

@marek-baranowski We have a call scheduled next Monday where we will discuss tasks for February/roadmap. Until then I don't have any ETA, sorry.

@wojciechczerniak let me just remind myself with the question about ETA :) were you able to estimate this task maybe?

[krzysztofspilka]

@marek-baranowski we're still determining dates and scope :(

[swistak35]

PRs: #167 #192

In these PRs part of this was done, meaning: formula expressions on workbook level, without ability to call them from the other formulas. But they can be registered and changes to them are in ContentChanges sets.

From the naming rules, these were applied:

Up to 255 characters length.

:heavy_check_mark:

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.

:heavy_check_mark: but as I said above, there's no "AST part" yet

Names that look like a cell reference are not allowed. A1: $A$100, or R1C1.

:heavy_check_mark: for the first part, we don't have the R1C1 syntax so I didn't do that part

The first character should be a letter or an underscore. The rest can contain letters, numbers, periods and underscores.

:heavy_check_mark: and also unicode letters are accepted

Whitespace is not allowed in named expression. It is allowed inside structured references enclosed in square brackets [ ] and should be preserved to improve readability.

:heavy_check_mark: whitespace not allowed, but the rest doesn't apply for regular named expressions

Some characters have special meaning for syntax: [, ], # and '. They should be escaped with single quotation mark '.

Not done, because it doesn't matter in these case? These characters are forbidden in named expression name, so I assume this is only for structured references syntax.

---

From methods, for now there's option to add named formula expression on workbook level, change it's formula, list available ones and get the value of specific named expression.

---

Also, the -1 sheet workaround is hidden, but still "leaks" when someone would try to use non absolute references :) I don't know what we want to do about it because I see that different tools works in a little bit different way

• some of them treat A1 expression as self-references and work in whatever sheet you use it
• some of them don't allow you to enter the formula without being absolute about the specific cell I didn't check all of the tools, someone else may do that and clarify what are the options :eyes: But it's decision still to be done and the usage of relative references should be treated as undefined for now

[wojciechczerniak]

@swistak35

Also, the -1 sheet workaround is hidden, but still "leaks" when someone would try to use non absolute references :) I don't know what we want to do about it because I see that different tools works in a little bit different way

• some of them treat A1 expression as self-references and work in whatever sheet you use it
• some of them don't allow you to enter the formula without being absolute about the specific cell I didn't check all of the tools, someone else may do that and clarify what are the options 👀 But it's decision still to be done and the usage of relative references should be treated as undefined for now

I would go with the first one. If the reference is not defined as absolute it's relative to the sheet (context) it's called in. We even have this context provided in calculateFormula method so this should be a safe approach.

---

What about finishing the API? I've seen that we can get the expression value, which is the result of the calculation? But can we get the expression itself? IMO we're missing one method:

• [x] engine.getNamedExpressionValue(name, scope)
• [ ] engine.getNamedExpression(name, scope)