Functions not running correctly if they spill beyond number of visible rows

Functions not running correctly if they spill beyond number of visible rows

Posted by: dean.kinnear on 18 March 2025, 9:56 am EST

• 

  dean.kinnear

  • Post Options:
  • Link
    Copy

  Posted 18 March 2025, 9:56 am EST

  If a function spills beyond number of visible rows, the function results in a #SPILL error.

  PFA workbook and screenshots:
  
  Not enough Rows Spill.zip

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 19 March 2025, 7:59 am EST

  Hi,

  During our investigation, we have identified the following key observations regarding the behavior of SpreadJS when handling dynamic array formulas from Excel:

  1. Cause of the #SPILL! Error in SpreadJS

  When an Excel file is loaded into SpreadJS, the framework automatically trims unused rows in the spreadsheet. In this case, the formula:

  =FILTER(HSTACK('Mapped TB'!$O$6#,TAKE('Mapped TB'!$A$6#,,2),'Mapped TB'!$L$6#,'Mapped TB'!$P$6#,'Mapped TB'!$Q$6#,'Mapped TB'!$R$6#,'Mapped TB'!$S$6#),('Mapped TB'!$L$6#<>0)+((Businesses_Count>1)*(('Mapped TB'!$P$6#<>0)+('Mapped TB'!$Q$6#<>0)+('Mapped TB'!$R$6#<>0)+('Mapped TB'!$S$6#<>0))),Mapped_TB_No_Unallocated_Codes)

  is applied in cell B5.

  In Excel, the # symbol after a cell reference (e.g., $O$6#) is known as the spilled range operator, which dynamically references the entire range of values originating from the specified cell.

  The #SPILL! error occurs in SpreadJS due to an insufficient number of rows available to accommodate the spill range required by the formula. By default, SpreadJS loads only eight rows, which may be insufficient for the formula’s dynamic range.

  Resolution:

  To correctly view and execute this formula in SpreadJS, additional rows must be inserted, followed by recalculating the sheet to ensure the spill range has adequate space.

  2. Observations in Excel

  When the Excel file is loaded directly into SpreadJS, the aforementioned issue occurs. However, upon saving the Excel file using CTRL+S (which triggers recalculation in Excel) and then reloading it into SpreadJS, the issue is resolved.

  This indicates that the original Excel file being loaded into SpreadJS is not pre-calculated, leading to incorrect handling of spilled ranges. Ensuring that the Excel file is fully calculated before importing it into SpreadJS mitigates this issue.

  We request you to confirm the calculations in the source Excel and try loading it into the spreadJS. However, we also reached out to the development team to figure out a solution for the spill Range error issue and how we can handle it dynamically. The internal track ID is “SJS-28720”. We will keep you updated regarding any updates.

  Hope this resolves your issue, please feel free to reach out if further assisstance needed.

  Best Regards,