Office-js: Difficulty working with invalid bindings

[cawise]

When a binding is made invalid, such as when the range it references is removed, an attempt to load the binding object yields an InvalidBinding error. This error is thrown during a context.sync(), which is a relatively expensive operation. Checking whether a binding is invalid requires making a sync() call solely to attempt to load one binding and catching the resulting error if there is one.

Also, since the binding object is not loaded, you cannot call its .delete() method. It appears the only way to delete an invalid binding is to use the old 2013 syntax. See this StackOverflow post:
https://stackoverflow.com/q/45586251/185544

Current Behavior

Attempting to load a binding object that is invalid throws an InvalidBinding error during context.sync().

Desired Behavior

A binding object would have an "isValid" property. An invalid binding could be loaded with this property, and the InvalidBinding error would only be thrown when attempting to load/access its range, for example. This would allow the validity of multiple bindings to be determined as a batch with one context.sync() call instead of a context.sync() for each individual binding. This would also allow the binding's .delete() method to be called.

Related improvements might include:

1. Indication within the InvalidBinding error which binding was at fault
2. Addition of a method to the parent BindingCollection object to allow the deletion of one of the bindings that can't be loaded.

Steps to Reproduce, or Live Example

This is a general issue rather than a specific scenario, but here's a ScriptLab gist that can be used to play with invalid bindings:
https://gist.github.com/cawise/91e14e6217403c22c582a449a8f785d7

Some things to try:

1. Create a binding and invalidate it. You can see it exists and check its validity but not delete it with 2016 syntax. You can delete with 2013 syntax.
2. Create many bindings and check the validity of all of them. Note how time increases dramatically due to .sync() calls.

Context

I have a document with about 400 bindings. A user alters the document and breaks one or more bindings. To avoid a showstopping error to the user, I want to detect and identify which bindings are invalid so I can indicate to the user what is broken and/or allow the application to ignore the broken bindings and proceed to function without them. Currently, looping to check all bindings individually in try-catch blocks with a context.sync() takes 60-90 seconds, which is a very unreasonable delay. Thus, I want the ability to operate around invalid bindings more easily and with much better performance.

Your Environment

• Platform: Windows Desktop
• Host: Excel
• Office version number: Version 1808 Build 10730.20386
• Operating System: Win10 v1709

[catchingyu]

team is investigating on it with 3747318 as tracking ID.

[lumine2008]

Hi @cawise , Based on our team's investigation, and confirmed with the PM of this feature, the way to address this issue should be a new feature for the binding object, we have converted this 3747318 to a feature request. So would you please provide us feedback via https://officespdev.uservoice.com/. and upvote for this feature. thanks for your support.

[cawise]

Thanks, @lumine2008, that makes sense. Could you provide the specific link to the feature? I'm not finding it.

[BeachAlmanack]

I am also having this problem

[tjcrowder]

@lumine2008 - Please do post the direct link cawise asked for. Searching for "binding" didn't seem to turn up the feature request.

[tjcrowder]

@cawise - We have this problem too. The way I'm working around it at the moment is to loop through all the bindings loading their ids, then calling sync and if it raises an invalid binding error, going through the bindings one by one removing the ones that are invalid. (Which I only know how to do with the old API because of this issue and its related Stack Overflow question -- thank you!) So when there aren't invalid bindings, it's fairly efficient, but when there are it has to slow down and deal with that. Details in this Stack Overflow answer.

[lumine2008]

Thanks @tjcrowder , Would you please create a new request in uservoice. therefore we can prioritize it based on the vote. thanks.

[tjcrowder]

@lumine2008 & @cawise - I've logged this feature request on uservoice. (@lumine2008 - A preview option before posting would be nice!!)

[cawise]

Thanks, @tjcrowder. Upvoted.

This and not supporting XLSM documents are the two primary issues driving us toward abandoning Office.js. At least this one has a workaround if you're willing to take the performance hit.

[tjcrowder]

@cawise - The hit's not too bad since you can do a batch operation and only fall back when there actually is an invalid binding, but it's definitely convoluted and the hit is there if you have one. Abandoning it for old-style Office automation, or a non-Office solution?

[cawise]

@tjcrowder I don't mean to clutter this comment thread with my stories, so I'll try not to be too verbose. I'm mostly just whining within earshot of Microsoft. :-) But since you asked...

We've had frustrations with Office.js since we started using it in 2014 or 2015 as a replacement for a rickety and unmaintainable, 15 yr old COM add-in we inherited from a vendor. This add-in mostly just transmits data in mapped fields, as well as the document itself, to and from our business line application. I was eager to see what additional features we could add with Office.js, but it still has issues with both of our two core functions. Excel supports various types of data connections out of the box, so we thought about just doing dumb data transfer with that, but they all seem to be read-only connectors without installing a third-party tool to support bidirectional connections. (Which we could explore but haven't, as there's no appetite to create another new solution after investing so much time into the Office.js add-in.) We've also considered the ability to upload a spreadsheet and scrape data from it. Our two real options are still COM or Office.js, neither of them good right now. We just hope and pray with each new project that we can convince users to accept a solution in the web app and not in Excel.

To be fair, Office.js has slowly but surely gained much more functionality over the years since we started with it. And the documentation is drastically more thorough and helpful than it used to be. If these two main issues could be fixed, I dare say I'd almost be excited to work with it again. But my confidence that this platform is adequately supported by Microsoft is low since, for example, it takes months to get replies to questions/issues.

Thank you for bringing some attention back to this issue. I'd given up on it.