CODEWITHSUNDEEP
javascriptexceloffice365office-addinsoffice-app
superB
superB

Reputation: 288

Accessing Old Cell Value in BindingDataChanged Event and Cell Location in Excel using office.js (Office Add-ins)

I have a table in Excel and I want to detect changes when the user modifies the cell values. In order to reflect those particular changes, I need to know the cell location and old cell value. I was able to achieve this in an Excel VBA/COM add-in, but not with JavaScript.

Upvotes: 0

Views: 403

Answers (1)

Michael Saunders
Michael Saunders

Reputation: 2668

There isn't any straightforward way to get the location and old cell value at this point. But here are some workarounds that you might use until there's a better way:

-Keep track of the contents of the user's table: you can get the entire table when it first gets linked to the add-in. Add a binding and listen for BindingDataChanged events to detect updates. Every time there's an update, you can manually compare it with your own copy. When the add-in is restarted you can reinitialize your in-memory copy using the Settings object.

-Keep track of the user's selections within the table using the BindingSelectionChanged event and the Document.SelectionChanged event. Then whenever the data in the table changes, determine using the saved BindingSelectionChangedEventArgs which cell was selected when the user made a change. Note that changes to the table can happen even if the user does nothing - for example, another add-in could change content. Also note that the BindingSelectionChangedEventArgs report the final position of the user's selection after it changes, not the initial position, so you'll need to use the second-last position whenever the user changes a cell but keeps her selection inside the table.

Also, you might not actually need to use the workarounds above, depending on what your add-in is doing: for example, if the table is small you could overwrite the necessary columns entirely whenever there's a change.

-Michael (Program Manager on the add-ins team)

Upvotes: 3

Related Questions