bug: Broken Shifting on Copy-Paste and Fill-Handle Drag

[johnleninortiz]

Jspreadsheet Formula Translation Issue: Broken Shifting on Copy-Paste and Fill-Handle Drag

Issue Summary

When copying and pasting a cell formula, or using the fill-handle drag (autofill), Jspreadsheet Pro automatically translates the cell references relative to the target destination. 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. D2:OFFSET(D10,-1,0)), the static cell reference fails to translate.

For example:

• A formula in cell D10 is: =SUM(D2:OFFSET(D10,-1,0))
• When this cell is copied and pasted to E10 (or dragged via the fill handle):
  • Expected behavior: The formula should translate to: =SUM(E2:OFFSET(E10,-1,0))
  • Actual behavior: The reference inside the function translates correctly (D10 -> E10), but the start of the range (D2) remains completely unchanged, resulting in: =SUM(D2:OFFSET(E10,-1,0))
• This results in incorrect calculations as the starting cell of the range points to the wrong column.

---

Technical Analysis

This is the exact same underlying parser bug that affects row/column insertions and deletions. Jspreadsheet's formula translation engine (residing in @jspreadsheet/formula-pro) translates cell references relative to the target offset:

1. Cell Reference Matching: It matches individual cell references (e.g. D10) but ignores them if they are followed by a colon (e.g. D2:) to prevent double-shifting of ranges.
2. Range Matching: It only translates ranges that match the standard Cell:Cell pattern (e.g. D2:D9).

Because a hybrid range like D2:OFFSET(...) contains a colon but does not match a standard Cell:Cell range, it falls into a parser blind spot:

• The Range Translator ignores it because the right-hand boundary is a function call.
• The Cell Translator ignores the starting cell D2 because of the trailing colon.
• Thus, the starting reference is never translated.

---

Suggested Solution

Update the formula reference matching regular expression or parser tokenizer in the formula engine to translate all relative cell coordinates, even when they are immediately followed by a colon that precedes a function call:

// The parser should match all relative cell reference occurrences and shift them:
/(\$?)([A-Z]+)(\$?)([0-9]+)(?!\s*\()/g

[hodeware]

We are working to send you an update soon.