How to identify the changed cell in office js api

Question

When the data is changed in a binding in excel, the BindingDataChanged event if fired.

function addHandler() {
    Office.select("bindings#MyBinding").addHandlerAsync(
    Office.EventType.BindingDataChanged, dataChanged);
}
function dataChanged(eventArgs) {
    write('Bound data changed in binding: ' + eventArgs.binding.id);
}

But the eventArgs do not have information about what part of the data is changed. Is there any way, we can track this information? We have bindings with large number of cells like 5000 rows * 15 columns or 90 rows * 350 columns. We are using office js api 1.2.

Update 1 Used the code provided by Michael Saunders below. Seeing some strange behavior.I have selected G9:H9 and pressed delete. But the header is always returning as Column1 and Column2 (Look at the toastr notification on top right). I was expecting Column7 and Column8. Is this the expected behavior? (The testing was done on Excel 2016 with a table binding and not on office 365. Will try it next)

Answer 1

I think the above solution of tracking selectionchanged is clever but I believe it gets you in a race condition between events firing. I tested the above solution and it appears that the SelectionChange triggers before the DataChanged Event which means you would be grabbing the current selected cell rather than the previous. I do not think you can avoid this race condition because events are async but potentially you could track previous and current selection like this:

myBinding.addHandlerAsync(Office.EventType.BindingSelectionChanged, onSelectionChange);

var startRow, rowCount, startColumn, columnCount;
var previousStartRow, previousRowCount, previousStartColumn, previousColumnCount;



function onSelectionChange(eventArgs){
    // save "previous" selected cell into previous variables 
    previousStartRow = startRow;
    previousRowCount = rowCount;
    previousStartColumn = startColumn;
    previousColumnCount = columnCount;

    // re-assign the current selected to the eventArgs
    startRow = eventArgs.startRow;
    rowCount = eventArgs.rowCount;
    startColumn = eventArgs.startColumn;
    columnCount = eventArgs.columnCount;
}

function onBindingDataChange(eventArgs){
    eventArgs.binding.getDataAsync({
        startRow: previousStartRow,
        rowCount: previousRowCount,
        startCol: previousStartColumn,
        columnCount: previousColumnCount  
    }, function(result){
        // Do whatever you need with result.value.
        // You might want to compare and update your in-memory representation of the data. 
    });
}

I tested it and it works...not nice and potentially there is a better way to track multiple levels but it seems to work. Ideally the DataChanged Event would have this in the EventArgs like VSTO. Fingers crossed it's coming soon!:)

Note - It does seem to work with copy & paste but this solution would need to be enhanced to handle the following situations:

• Cell Drag n' Drop with the "+" sign.
• Multiple Cells Selected and then updating individual cells with the keyboard and enter

Update 1 - Another situation

• When updating the edge cells of a binding and hitting enter it will cause the selection to be outside of the binding range. The on selection change event does not kick off and the above solution would fail

Answer 2

There isn't a direct way to get the row in the binding which changed.

The best workaround to avoid checking your entire table is to track the user's selection. The BindingSelectionChanged event fires every time the user selects something in the Binding, which always happens when entering data in a table. The solution is to always save in memory the most recent previous selection location in a binding. Then when the data changes, check that stored location.

Here's the code:

myBinding.addHandlerAsync(Office.EventType.BindingSelectionChanged, onSelectionChange);

var previousStartRow, previousRowCount, previousStartColumn, previousColumnCount;

function onSelectionChange(eventArgs){
    previousStartRow = eventArgs.startRow;
    previousRowCount = eventArgs.rowCount;
    previousStartColumn = eventArgs.startColumn;
    previousColumnCount = eventArgs.columnCount;
}

function onBindingDataChange(eventArgs){
    eventArgs.binding.getDataAsync({
        startRow: previousStartRow,
        rowCount: previousRowCount,
        startCol: previousStartColumn,
        columnCount: previousColumnCount  
    }, function(result){
        // Do whatever you need with result.value.
        // You might want to compare and update your in-memory representation of the data. 
    });
}

-Michael, PM for Office add-ins

Answer 3

The only issue with Micheal's answer is that in the onBindingDataChanged function the getDataAsync method's parameter object's startColumn is incorrectly passed as startCol, and that is why the starting column is returned.

function onSelectionChange(eventArgs) {
    startRow = eventArgs.startRow;
    rowCount = eventArgs.rowCount;
    startColumn = eventArgs.startColumn;
    columnCount = eventArgs.columnCount;
}

// When data in the table is changed, this event is triggered.
function onBindingDataChanged(eventArgs) {
    Excel.run(function (ctx) {
        // Highlight the table in orange to indicate data changed.
        eventArgs.binding.getDataAsync({
            startRow: startRow,
            rowCount: rowCount,
            ***startColumn***: startColumn,
            columnCount: columnCount
        }, function (result) {
            // Do whatever you need with result.value.
            console.log(result.value);
            // You might want to compare and update your in-memory representation of the data. 
        });
    });
}