Wrong value result when formula got empty cell reference

Tags: #<Tag:0x00007efc5e3c5968>
#1

In the same situation google spreadsheet interpret empty cell reference as zero.

Is that missing functionality or bug? How can I fix it on my project?

#2

Welcome @john.makeev

Can you share an example? I can see that it works odd if we use undefined as a value https://jsfiddle.net/handsoncode/2u3kmfLg/ but anything else is copied without errors.

#3

Sorry for non-specific question if it was,
I mean, it doesn’t work in expressions: E1+1; SUM(E1:E3) etc

https://jsfiddle.net/JohnMak/1wLjmpqb/1/

#4

You’re right. Even if we use the numeric cell type we get the issue. In Handsontable an empty cell is always an empty string so I believe that this is the cause of the issue. However I might have a workaround for you. You can set up ‘0’ for all the empty cells and if you do not want them to appear as 0 use the custom renderer to erase the value - example https://jsfiddle.net/AMBudnik/8gm0nte3/

#5

I guess that problem in formula plugin basis, how it treat the cell value, and it looks like it uses JS parseFloat of cell value.
Because the result is the same.
Your suggestion is close what I’ve done, but reality is a bit more complicated, because user can erase whole cell value, parent application could update the data, user can start edit an ‘empty’ cell, and finally user could want simply have ‘0’ inside cell

Just maid a bit tricky solution:
The main idea is that JS parseFloat and HT formula plugin treat ‘-00’ as zero

Solution was in few steps:

  1. Fill initial data empty cells with ‘-00’
  2. Use custom renderer that show nothing if cell value is ‘-00’
  3. Create hook on afterSetDataAtCell and set ‘-00’ if new value is empty string

Hope that could help somebody =)

#6