search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
686 stars 95 forks source link

Excel custom functions in shared runtime display #BUSY! indefinitely until another unrelated action is performed from the TaskPane #1395

Closed cghaddar closed 4 years ago

cghaddar commented 4 years ago

Almost consistently, a custom function in shared runtime will remain in #BUSY! state for over a minute or until another unrelated action is performed in the Taskpane. Normal computing time should be a fraction of a second. Often just opening a closed Taskpane is sufficient to trigger the function to display the result. This seems to happen for async custom functions using office.js api.
I can produce this behavior even with this simple function added to CONTOSO custom functions tutorial

/**

I also noticed that if I evaluate a second call to the same custom function in another cell, the first cell changes from #BUSY! to #Value! error with the following message:
"Excel is in cell-editing mode. Please exist the edit mode by pressing ENTER or TAB or selecting another cell, and then try again".

Expected Behavior

Current Behavior

Steps to Reproduce, or Live Example

Insert the above custom function in the standard tutorial described here https://docs.microsoft.com/en-us/office/dev/add-ins/tutorials/share-data-and-events-between-custom-functions-and-the-task-pane-tutorial

Context

Unfortunately, I have not been able to work around this frustrating behavior which is delaying the release of my add-in. Any help is greatly appreciated.

Your Environment

Useful logs

xiaochunms commented 4 years ago

@cghaddar I tried the same locally, however I could not get it reproed. Is it possible to share a screen record that I may know the difference with operation of mine? And may you please to output the range value by adding " console.log(JSON.stringify(range.values));" after "await context.sync()" and before "return range.value[0][0]"? You can see the output in console of F12 developing tool.

cghaddar commented 4 years ago

@xiaochunms I am attaching the complete basic project src which produces the problem consistently. I have also captured a screen video of the behavior. In the video I set A1 to 1, then I get its value with =CONTOSO.GETRANGEVALUE("A1"). You can see that it remains #BUSY! until I open the TaskPane. It will do the same thing on repeated evaluation. Excel add-in 418ab57f-10bc-48cf-834e-b48051bf875e.xlsx - Excel 2020-09-22 08-15-11.zip

I also tried returning JSON.stringify(range.values); instead of range.value[0][0]; but it made made no difference. I have not figured out how to open the console in Excel yet. Would appreciate some clear instructions here. TestProject.zip

xiaochunms commented 4 years ago

@cghaddar Thanks for the efforts to share the screen video and the test project. I think return "range.value[0][0]" is fine as I get the result by just returning it. When you run custom functions in a shared runtime with the task pane, your add-in will run in a Microsoft Internet Explorer 11 browser instance. So the console.log() will output to browser debug console. The article shows how to debug add-in using F12 developer tool,

cghaddar commented 4 years ago

@xiaochunms. Hopefully you will be able to produce the same behavior. A fix or workaround would be immensely helpful as this odd behavior is blocking release of my actual add-in. Other developers seem to have also run into same problem. Here is a recent report I found on stackoverflow. https://stackoverflow.com/questions/63461391/async-custom-functions-in-excel-add-in-always-return-busy-error

xiaochunms commented 4 years ago

@cghaddar may you please try the workaround: add <script> MutationObserver=null; </script> before importing office.js/custom-functions-runtime.js? One of my colleagues told me that it did fix the similar issue before.

cghaddar commented 4 years ago

@xiaochunms I added at first but I am seeing mixed results with this change. It seems to have fixed the demo project with one async call in the sheet. However, with multiple async calls on the same worksheet, the effect is mixed. Some evaluations returned results promptly while others remained in #BUSY state till I interacted with the task pane. It seems to have some sort of effect but does not seem to quite fix the problem.

xiaochunms commented 4 years ago

@cghaddar good to know adding "" works for your demo project. Regarding to the mutilple async calls on the same worksheet issue, may you please share your project also here? And may you please help to record another screen record for the new issue? In your interaction with the taskpane, could you please don't close the taskpane, instead please just keep the taskpane there and move the mouse right to the taskpane from Excel?

cghaddar commented 4 years ago

@xiaochunms I have tried to produce the same behavior using the test project with multiple calls in sheet but it is always working now as expected . In my own add-in I have also seen noticeable improvement after clearing office cache. The BUSY state only lasts for a few seconds without a need to open the taskpane which is significant improvement compared to how it was. Will post again If I run into a reproducible case but for now this issue seems resolved with this workaround. I was considering adding a fake refresh button on the taskpane but this is unnecessary now. Thanks much for finding this workaround.

higearappdev commented 3 years ago

Hello all,

Just putting this out here in the hopes it may be useful for someone else

I ran in to the same issue where #busy is shown indefinitely while using the shared runtime. In my case I had included the import in my task pane.html. Simply removing that line solved it for me.