We are trying to capture whenever a cell value changes, whether that be due to a user entering a value in a cell directly, or when a formula calcValue is updated. The cellSave event seems to meet our requirements however we've seen a few of instances where the cellSave event isn't being fired:
- When using autoFill, cellSave isn't being fired for the cells which are updated because of the fill.
- When using autoFill, if you have a formula that references a cell within the range that was updated (recreate steps below) the cellSave event isn't always fired for the calculated value of the formula changes.
- When clearing the contents of a cell, cellSave isn't being fired for the cell(s) which were cleared
- When clearing the contents of a cell which is referenced by a formula, cellSave isn't being fired when the calculated value of the formula changes
Reproduce steps for issue 2:
On an empty spreadsheet enable cellSave event
- In cell A1 enter the formula =SUM(C1:C10)
- In cell C1 enter the number 1
- In cell C2 enter the number 2
- In cell E1 enter the formula =SUM(C1:C10)
- Select cells C1 and C2 and using autofill drag down to C10
Looking at the output of the cellSave event you can see the calcValue for the formula in A1 is updated for each of the new numbers the autofill populated. But the formula in Cell E3, whilst being updated in the UI isn't actually firing the cellSave event.
Now if you follow the same steps above yet, before you do the autofill, if you enter anything into another cell, for example enter the word "Hello" into cell H8
Now when you do the autofill the cellSave event is fired for both A1 and E1.
It looks like if the last thing you typed is a formula, and then you use autoFill, and the range you autofilled is referenced in that last formula you typed in, then the cellSave event isn't fired.
This happens too when the last thing you type is a formula, and then you delete (clear contents) of a cell or range of cells which are referenced in that forumla, the cellSave event isn't fired also.
I hope that makes sense, any thoughts, solutions or options would be gratefully received. Our goal is to have an event we can listen for that will tell us when any cell's value has changed either directly because a value was changed, or indirectly because the calculated value of it's formula has changed.