Keep original and current cell value

Tags: #<Tag:0x00007f8b1d5c3780> #<Tag:0x00007f8b1d5c3550>
#1

Hello there!
Is there a way to keep original (initial) and a current value of a cell?
I need to keep the original one so I can compare it to the current one and change cell’s style if the value differs + have a way to discard current value and go back to the original one (via e.g. context menu).
An additional issue: I intend to have the original value as a formula that references other cell (='Sheet1'!A1).
I know that cell accepts only primitive values and I can’t pass an object. I tried to keep the original value as a metadata but when I use formulas, the formulas are shown in an editor and I want to show their values instead of source data (formulas)

How can I keep original and current cell value? How can I replace the formula in editor with the calculated value?

#2

Hi @alojzy231

With formulas you may want to know 3 different types of data

  • data
  • source data
  • deep clone

Here https://jsfiddle.net/5pus1kc9/3/ is an example that shows what is the difference.

In out example we have formulas and merged cells (they change values to null underneath) so it shows the full picture. in general

  • getData() - returns the current dataset with formulas being recalculated and shown as a value
  • getSourceData() - returns the current dataset with formulas NOT being recalculated and shown as a value
  • deep clone - for this, we use JSON's parse() and stringify() to create a clone of the dataset. This was you have formulas NOT being calculated and pure dataset without Handsontable integration (like merge cells). So if you would like to get initial data from (1, 2) cell you just call JSON.parse(deepClone)[1][2].

If you need anything more than that please let me know.

#3

I’m fully aware of different types of data (sourceData and data).

My question here is: How can I store original and current value in the cell and based on these data style the cell (if they differ) and show proper option in context menu (so I have an option to revert to an original value).

Second question: Is there a way to editor show the data and not sourceData (instead of formula show the calculated value). I use formulas to populate cells with the default data.

#4

#1
When you already have this JSON.parse(deepClone)[1][2] you can create a condition via afterChange, here’s a demo https://jsfiddle.net/jhsczkLg/. It also works well for undo/redo.

#2
I am not sure what would be the expected outcome. If a cell does not have the formula in the editor it is not a formula at all. You can however, play with the cellmeta, I just do not know that would you do with the formula when user changes the value in the cell editor. Can you explain that part? For example let’s say that we have =A1*3, where A1 is 2, so user see 6 in the renderer but =A1*3 in the editor. So, if now they will see 6 in the editor as well, what happens to the formula?

#5

#1
I’ve created a codesandbox example in which I have created visitorEditRenderer function which compares original (initial) value (set in cell’s metadata) and current value and sets red background if they differ. I’ve researched that solution on forum here. I believe that will suffice for what I want to achieve and thus I can close first part of my problem (question #1). Thanks!

#2
I have an use case where I have 2 sheets: sheet1 and sheet2 and it’s easier for me to reference value from sheet1 in sheet2. So in sheet2 I’ll have something like: ='sheet1'!A1 or more complex formulas e.g. ='sheet1'!A1*B1 (conditions, wrapped in =IFERROR(*) etc.) and the output of the formula (displayed value) should be treated as cell’s new value (so sourceData and data are the same) and that value is displayed in an editor; the formula is forgotten (isn’t shown in an editor) but the output is saved as the cell’s new value (is shown in an editor).

For example let’s say that we have =A1*3 , where A1 is 2 , so user see 6 in the renderer but =A1*3 in the editor. So, if now they will see 6 in the editor as well, what happens to the formula?

in this case I want to “forget” about the formula and keep 6 in the editor.

#6

I am glad to that #1 is solved as with #2 you would need to override the data collection method for the custom editor. I do not have a ready to implement example, but I can recommend reading our guide at https://handsontable.com/docs/javascript-data-grid/cell-editor/ if you haven’t seen it yet.

1 Like
#7