Return a changelog of actions that happened from a function call.

[MartinDawson]

So this is a follow up discussion to this one here: #739

The fundamental issue is keeping Hyperformula in sync with a spreadsheet that has extra data with it such as styles and comments. I am sure you guys at Handsontable have the same issue. I proposed a solution in 739 that was not really in hyperformula's scope.

Example:

• A user deletes a row on our spreadsheet. We then call hyperformula.removeRows() and the values in hyperformula automatically shifts everything up a row. However we have to keep our cell styles in sync with this so we then have to write code that shifts the cell styles up a row. This is bad because there are 2 sources of truth and the cell styles can now get out of sync with hyperformula values. The same issue occurs in copy/cut/paste.

Proposed solution:

• On a hyperformula such as a removeRows it would be good if it could also return a 'changelog' of the actions that happened. I.e for removeRows(this.sheet.sheetId, [ 1, 1, ]) it would return this:

{
  old: {
    row: 1,
  },
  new: {
    row: 0
  }
}

This way in our spreadsheet we can just read the old values and the new values and shift our data up by the same amount. Keeping everything in sync. This solves the 'keeping in sync' issue and does not bloat hyperformula with stuff that slows down performance.

Let me know your thoughts on this.

Thanks!

[AMBudnik]

That's an interesting idea and definitely a topic we should consider while planning future features related to tracking changes made in the cells.

[wojciechczerniak]

Well, no, those are not two sources of truth. You don't read the same property, for example, value from two different sources where you will get two different values. Source for value is just one, HyperFormula. And there is only one source for styles, your StyleStorage. They are kept in two different types of storages, but it's a perfectly normal and common situation. Especially today, when everything is build based on microservices.

IMO You have everything you need to shift the data correctly. It's the very same input a removeRows method gets. Also, removeRows already returns changed cell collection, which is much more useful than repeating the input arguments and harder to calculate on your own.

function removeRows(sheetId, rowsToRemove) {
   hfInstance.removeRows(sheetId, rowsToRemove)

   const { height } = hfInstance.getSheetDimensions(sheetId)
   const shiftedRows = [ ]

   for (let i = 0; i < height; i++) {
     if (i > rowsToRemove[0]) {
        shiftedRows.push({ old: i, new: i - rowsToRemove[1] })
     }
   }

   return shiftedRows
}

Skipped some edge cases for brevity. And for thousands of rows it's a long collection of indexes we could calculate anyway. IMO unnecessary overhead and memory footprint.

What you need is Unit of Work pattern to keep them in sync. Alter both with a single transaction or rollback if any of storages fails to apply changes.

Or just keep them next to each other and they will stay in sync

class HyperFormulaCalculationService implements CalculationService {
  #hyperformula: HyperFormula
  #styles: StylesStorage

  removeRows(sheetId: number, indexes:  ColumnRowIndex[][]) {
    this.#hyperformula.removeRows()
    this.#styles.removeRows()
  }
}

[MartinDawson]

Thanks for the suggestion. I'll try it this way!