Listen to the value change of multi ranges with debounce

Question

Currently, I use the following code to listen to the change of Sheet1!A1:B2:

function addEventHandler() {
    Office.context.document.bindings.addFromNamedItemAsync("Sheet1!A1:B2", "matrix", { id: "myBind" }, function (asyncResult) {
        Office.select("binding#myBind").addHandlerAsync(Office.EventType.BindingDataChanged, onBindingDataChanged2016);
    })
}

function onBindingDataChanged2016(eventArgs) {
    Excel.run(function (ctx) {
        var foundBinding = ctx.workbook.bindings.getItem(eventArgs.binding.id);
        var myRange = foundBinding.getRange();
        myRange.load(["address", 'values']);
        return ctx.sync().then(function () {
            console.log(JSON.stringify({ "address": myRange.address, "value": myRange.values }));
            // costly reaction
        })
    })
}

Because my reaction to the change is quite costly, I want to undertake it only when it is really necessary. I have two questions:

1) If I want to listen to multi ranges, is it possible to define only ONE listener for "Sheet1!A1:B2, Sheet1!A9:B10, Sheet1!A100:B120"? Do I have to add ONE handler for EACH range?

2) Is it possible to express I listen only to the change of VALUES, rather than formats, etc.?

Optional question:

Is it possible to specify a debounce somewhere? For example,

1. we initialise a clock with 0

2. if a listener is triggered, we record the binding id of the change, and set the clock to 0

3. when the clock reaches 1 second (ie, it has been quiet for 1 second), we react to all the recorded changes (ie, load all the changed ranges and undertake the costly reaction)

Forkey

Answer 1

1. Office JS has no event handler that allows for listening to multiple bindings, much like how HTML has no way to listen to multiple DOM nodes simultaneously. Even if there were such an API function, it would have to create multiple listeners internally, so you'd gain no performance advantage.

2. Unfortunately, there's no event type available that discriminates between numeric changes and formatting changes.

3. You can debounce to great benefit, however!

Let's assume you've got some function debounce(func, wait, immediate = false) available.*

Simply wrap the on change function in a debounce() call.

function addEventHandler() {
    Office.context.document.bindings.addFromNamedItemAsync("Sheet1!A1:B2", "matrix", { id: "myBind" }, function (asyncResult) {
        Office.select("binding#myBind").addHandlerAsync(
          Office.EventType.BindingDataChanged,
          debounce(onBindingDataChanged2016, 5000)
        );
    })
}

This will debounce all calls to onBindingDataChanged2016. If you want to debounce per specific binding id, things get a little trickier. You'd have to create your own debounce function, which tracks the timeouts per binding id:

function debounceByBindingId(func, wait, immediate) {
  var timeouts = {};
  return function() {
    var context = this, args = arguments;
    var eventArgs = arguments[0];
    var bindingId = eventArgs.binding.id;

    var later = function() {
      timeouts[bindingId] = null;
      if (!immediate) func.apply(context, args);
    };
    var callNow = immediate && !timeout;
    clearTimeout(timeouts[bindingId]);
    timeouts[bindingId] = setTimeout(later, wait);
    if (callNow) func.apply(context, args);
  };
};

*As always in JavaScript land, there are too many options to choose from!

• https://davidwalsh.name/javascript-debounce-function
• http://underscorejs.org/#debounce
• https://www.npmjs.com/package/debounce
• https://www.npmjs.com/package/javascript-debounce
• https://www.npmjs.com/package/js-debounce

Wo

For another debouncing example (not with document events, but external button-click events), see this gist: gist.github.com/Zlatkovsky/0dd0e7281e70af25d67bdb55cd4d7e4b. You can load it directly into Script Lab to run it with just a few clicks. – Kunin