search

handsontable / hyperformula

HyperFormula is an open-source headless spreadsheet for business web apps. It comes with over 400 formulas, CRUD operations, undo-redo, clipboard support, and sorting.
https://hyperformula.handsontable.com/
Other
2k stars 110 forks source link

Ability to know which formulas changed after a CRUD operation (before: `formulasUpdated` hook) #1156

Open AMBudnik opened 1 year ago

AMBudnik commented 1 year ago

Description

We should consider:

The formulasUpdated hook was described in this issue https://github.com/handsontable/hyperformula/issues/135#issue-556160779. The issue, was close as done without introducing the hook so I've created a separate issue.

Pre-hook description

Cell references inside formulas are automatically updated when adding/removing/moving rows or columns. formulasUpdated event should fire whenever any formula gets updated. The payload for this event should contain the list of cells that contain the updated formulas and the string representations of the updated formulas.

Related issues/discussions:

BrianHung commented 1 year ago

Reposting from discussions: https://github.com/handsontable/hyperformula/discussions/1155

845 #135 https://github.com/handsontable/handsontable/issues/8114

It would be useful to have a formulasUpdated listener, as mentioned in #135.

I've been trying to integrate hyperformula with an external data source (to work with React etc), and have noticed that some methods like addRow and moveRows modify internal formula representations.

Essentially the same problem that handsontable faces in https://github.com/handsontable/handsontable/issues/8114, where you can move rows and columns but formulas referencing values in those moved rows or columns are now out of date.

For example if we had a =SUM(A1:B3) and the third row was deleted, the formula would be changed to =SUM(A1:B2).

Right now the only way to correctly react to changes in formulas is to use getAllSheetsSerialized.

However, this isn't as efficient as valuesUpdated is for listening to changes in values, since there can be a lot of sheets and would cause excessive re-rendering without manual diffs (which is also expensive).

sequba commented 11 months ago

This is a complex task which requires redesigning some parts of the core functionalities. Also it could affect the performance of the engine. As @swistak35 wrote in https://github.com/handsontable/hyperformula/issues/135#issuecomment-593164908:

I've decided to not do it immediately because it needs significant work, so I wanted to double-check, how much we need that event right now. This is because currently we have an optimization, so we don't update the formula immediately after CRUD operations, but only when we need that formula for recomputation (or for any other case, like when user asks for it). Instead of immediate computation, we do that lazily -- it's not so easy to track these changes and we would need to do some refactoring.

If we want to proceed with this feature, we should start with a research/POC task with the aim to find out how much effort it requires and what impact on performance it has.