bug: Broken Shifting in Hybrid Range Expressions

[johnleninortiz]

Issue Summary

When deleting or inserting rows/columns in Jspreadsheet Pro, formula references are automatically shifted. However, if a formula uses a hybrid range expression where one boundary is a static cell reference and the other is a function call (e.g. C14:OFFSET(I14,0,-1)), the static cell reference fails to shift.

For example:

• A formula in cell I14 is: =SUM(C14:OFFSET(I14,0,-1))
• When a row above row 14 (e.g., row 13) is deleted, the formula should shift to: =SUM(C13:OFFSET(I13,0,-1))
• Actual behavior: The reference inside the function shifts correctly (I14 -> I13), but the start of the range (C14) remains unchanged, resulting in: =SUM(C14:OFFSET(I13,0,-1))
• This results in incorrect calculations as the starting cell of the range points to the wrong row.

---

Technical Analysis

Jspreadsheet's formula parser/shifter likely handles cell and range shifting using two distinct mechanisms:

1. Range Shifting: Matches standard Cell:Cell ranges (e.g., C14:D15) via regex patterns like [A-Z]+[0-9]+:[A-Z]+[0-9]+.
2. Cell Shifting: Matches individual cell references (e.g. I14), but excludes them if they are followed by a colon (e.g. C14:) to prevent double-shifting of ranges.

Because C14:OFFSET(I14,0,-1) contains a colon but does not match the standard Cell:Cell range pattern, it falls into a blind spot:

• The Range Shifter ignores it because the right side (OFFSET(...)) is a function, not a cell name.
• The Cell Shifter ignores the starting cell C14 because it is followed by a colon.
• Thus, the starting cell reference is never shifted.

---

Suggested Solution

Update the formula reference detection regex or tokenizer in Jspreadsheet's shifting logic to recognize individual cell references even when they are part of a custom range prefix, or update the range parser to allow function/expression bounds:

// Instead of ignoring any cell followed by a colon:
/(\$?)([A-Z]+)(\$?)([0-9]+)(?!:)/g

// The shifter should match all cell reference occurrences and shift them if their row/column index is affected by the operation.

Temporary Client-Side Workaround

We bypassed this issue on our side by manually intercepting worksheet structural changes inside our spreadsheet wrapper component:

Before the operation (onbeforeinsertrow / onbeforedeleterow / onbeforeinsertcolumn / onbeforedeletecolumn): We scan the grid, locate all cells containing formulas (values starting with =), and store their formulas and original coordinates in memory.

After the operation (oninsertrow / ondeleterow / oninsertcolumn / ondeletecolumn): We iterate through the cached formulas, calculate their new cell coordinates, and use a custom regex to parse and shift their internal row/column references if they fall at or after the modification boundary.

Restoring: Finally, we call worksheet.setValue(newCellName, shiftedFormula) to overwrite the grid with the correctly shifted formulas, bypassing Jspreadsheet's internal shifter.

[johnleninortiz]

@hodeware are there any estimates of when this can be delivered?