All geek questions in one place

Listen to the changing value of multiple ranges with debounce

I am currently using the following code to listen for a change in 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 }) }) }

Since my reaction to change is quite expensive , I want to take it upon myself only when it is really necessary. I have two questions:

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

2) Can I express I listen only to the change of VALUES , not formats, etc.?

Additional question:

Is it possible to specify debounce somewhere? For instance,

  • we initialize the clock from 0

  • If the listener is running, we record the binding id change and set the clock to 0

  • when the clock reaches 1 second (i.e., it was quiet for 1 second), we respond to all recorded changes (i.e., load all changed ranges and take on a costly response)

+6
source share
1 answer

  • Office JS does not have an event handler that allows you to listen to multiple bindings, just as HTML cannot listen to multiple DOM nodes at the same time. Even if such an API function existed, it would have to create many listeners internally, so you would not get performance benefits.

  • Unfortunately, there is no event type that distinguishes between numeric changes and formatting changes.

  • You can give up big benefits, however!

Suppose you have a debounce(func, wait, immediate = false) function debounce(func, wait, immediate = false) . *

Just wrap the change function when calling debounce() .

 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 cancel all calls to onBindingDataChanged2016 . If you want to debut with a specific binding identifier, things get a little more complicated. You will need to create your own debounce function, which tracks timeouts for one binding identifier:

 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 the land of JavaScript, there are too many options to choose from!

+1
source

Source: https://habr.com/ru/post/1015713/

More articles:


All Articles