SpreadSheet feature: Save on the server the result of a formula, not the formula itself

Webix JS
#1

Hi,

I just realized that we don’t have the ability to save only the result of a formula, not the formula itself. There should be an option for that.

Data/save object, getCellValue method, onCellChange event send only the formula of a cell (when the cell contains a formula of course), not the value.

It’s very annoying in some cases. For my customers, I use the spreadsheet a lot so that they can easily modify price lists, taxes, margins, sales coefficients, etc.

Thanks to the spreadsheet, they can easily move with the keyboard, copy and paste many cells, make formulas from other columns (for example to increase the price by 5%).

But it is not practical to save easily their modifications on the server if we get a formula and not the result (especially if the formula contains other cells, for example =(A5+B5)*1.2).

it would be very useful to have an option to send the result of the formula, not the formula itself.

But maybe there’s something I didn’t see in the doc?

Thank you

Olivier

Translated with DeepL Translate: The world's most accurate translator

#2

Hello,

You can get to the calculated value via the getRow method. It can return the whole row object, and you will be able to read and save any desired property:

on:{
  onCellChange:function(row, column){
     console.log(this.getRow(row)[column]);
  }
}

https://snippet.webix.com/bwv33eku

#3

Great! Thanks Helga.

#4

@Helga - As I am using “onBeforeValueChange” , It does not update current value. It gives me previous value. and I can not move to “onCellChange”. Do you have any solution to get result of formula using "onBeforeValueChange "?
on:{
onBeforeValueChange:function(row, column){
console.log(this.getRow(row)[column]);
}
}

#5

@priyab,
Please note that onBeforeValueChange takes 4 parameters ans you can get old and new value:
https://snippet.webix.com/37tkjkhw

#6

@annazankevich - considering sinppet (https://snippet.webix.com/37tkjkhw) , If you reference cell value on sheet , it gives us formula not result.
Example- If cell A2 referencing a value from cell b2, so in cell A2 we create formula like “=B2” , to which expected output should be value of cell B2 but what we get is “=B2”, so I want result of formula here.

#7

Hello @priyab,

Example- If cell A2 referencing a value from cell b2, so in cell A2 we create formula like “=B2” , to which expected output should be value of cell B2 but what we get is “=B2”, so I want result of formula here.

It is possible to get the result of the calculations after a slight delay (this is necessary, as the result is not yet calculated inside of the onBeforeValueChange handler) via various methods. For instance, you could use the getRow() method and get the calculated value of the referenced cell. Please take a look at the following example: https://snippet.webix.com/hi4d35uy.