FlexSheet formula parser incorrectly tests for blank string

FlexSheet formula parser incorrectly tests for blank string

Posted by: michael.lucks on 30 April 2019, 10:56 pm EST

• 

  michael.lucks

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 30 April 2019, 10:56 pm EST

  When a Flexsheet formula contains a condition that tests for the existence of a blank string, the test returns true when the target cell = 0. For example:

  1. A1 = 0
  2. A2 contains a formula ‘=(A1=“”)’

  In Excel, the formula correctly evaluates to FALSE – but in FlexSheet the formula incorrectly evaluates to TRUE.

  Here is a fiddle that further illustrates the behavior:

  http://jsfiddle.net/lucksm1/9f0bny2h/18/

  How can I test accurately for a blank string?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 1 May 2019, 4:46 am EST

  Hi,

  We are able to replicate the issue at our end, therefore we have forwarded a bug report to the concerned team for further investigation with internal tracking id #377114. We will let you know about any updates regarding the same. Until then, you could work around the issue by creating a custom function to check if the cell is empty. Please refer to the following code snippet:

  flexSheet.addFunction('isblank', (...params) => {
      var val = params[0][0][0];
      return val == '';
  }, 'Checks if cell is empty', 1, 1);
  

  You may also refer to the following sample demonstrating the same:
  
  https://jsfiddle.net/5b1zLrcs/

  Regards

• 

  michael.lucks

  • Post Options:
  • Link
    Copy

  Posted 1 May 2019, 6:27 pm EST

  Thank you for the workaround. Please notify me when the bug is repaired.

• 

  michael.lucks

  • Post Options:
  • Link
    Copy

  Posted 2 May 2019, 11:37 pm EST

  By the way, the equality test in your suggested function should be:

  val === “”

  not

  val == “”

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 8 May 2019, 3:16 am EST

  Hi,

  The issue with comparing empty strings with 0 returning wrong value has been fixed in the latest nightly build. You may confirm the same from the following sample which uses the nightly build:
  
  https://stackblitz.com/edit/js-d1roog?file=index.js
  
  The fix will include in the next stable release.

  Note; Nightly builds are not tested for production and should not be used in the production environment.

• 

  michael.lucks

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 6:05 pm EST

  Thank you!