1

I have some problems when i use my Angular Excel Addin (Office js) in multiple opened Excel instances.

Before I describe the problem in more detail, I would like to give a short explanation of what the AddIn does:

The user can define UDF functions in Excel to fetch data from a server and display it in Excel. However, the UDF functions are not executed directly, but only when an update is triggered. After that, requests are sent to the server, the data is fetched, formatted and displayed in Excel. If the user clicks on the "Update" button, all worksheets in the workbook are iterated over and the corresponding UDF functions are searched for. The requests are sent directly to the server. Under certain circumstances, it can take some time to answer a request and to display the data in Excel (e.g. when a user has defined 100 UDF functions in 100 worksheets with each containing one UDF).

I currently have the following problems:

1) If the user has opened an Excel instance with UDF functions defined in 100 worksheets and another instance with 70 worksheets containing UDF functions, I get an "An unexpected server error occurred" error when I switch between the opened Excel instances. The update does not run through, sometimes requests are not sent at all or cannot be processed. I think the following code snippet could cause this problem:

Excel.run(async context => { const sheet: Excel.Worksheet = context.workbook.worksheets.getActiveWorksheet(); ... }

context.workbook always returns the currently open workbook or the one that is currently open in the active Excel instance. But I have not found a way to access a workbook by name or id.

Is it possible that the AddIn can only work properly in one instance or am I doing something wrong?

2) If you have started an update of a workbook via the AddIn (e.g. 100 worksheets with UDF functions) and Excel loses focus (e.g. by switching to Word or Edge), then as already mentioned under 1) not all requests are sent or processed.

I hope that some of you have already had similar problems and maybe have a solution for them.

Marco Siebert
  • 109
  • 10
  • For 2#, would you please share me the script lab gist? i would like to have a repro in my side. – Raymond Lu Apr 16 '20 at 08:03
  • There is a guideline in stackoverflow: focuses on one problem only in one post, therefore 2nd question might be lost, so maybe you could create another post for 2nd problem – Raymond Lu Apr 16 '20 at 09:24

1 Answers1

1

Thanks Marco Siebert for reporting this issue to us.

For 1# this is a known issue that we are now investigating, it is also tracking at https://officespdev.uservoice.com/forums/224641-feature-requests-and-feedback/suggestions/39781582-fix-generalexceptions-being-thrown-when-multiple-e,

To solve this issue, we need to fix for each API that used in this scenario, we have a work item 3743479 for this issue. you could also upvote this feature in user voice. besides, would you please share me the APIs that you are trying to use in multiple workbook scenario?

For your question: "context.workbook always returns the currently open workbook or the one that is currently open in the active Excel instance. But I have not found a way to access a workbook by name or id." This is by design, as JS add-in is per workbook. So we don't support to access another workbook content except addFromBase64 API which provide a capability to copy the worksheets from another workbook to the current one.

Raymond Lu
  • 2,178
  • 1
  • 6
  • 19
  • 1
    Hi, Raymound, I think you misunderstood me. I opened two instances of Excel, each with a workbook open. The problem occurs when I click update in the AddIn in instance 1 and then switch to instance 2 and update as well. The AddIn does not seem to run in separate contexts, there seems to be only one instance for both open Excel. I thought that for each Excel instance a separate AddIn instance is running, but this seems not to be the case. – Marco Siebert Apr 16 '20 at 08:46
  • I think the root cause is similar to this issue. https://github.com/OfficeDev/office-js/issues/771, this customer is using setInterval(), but the issue also occur, when API is waiting for server's update and then end user switched to another workbook. it API failed and Exception will be thrown. what's the error message in your scenario? – Raymond Lu Apr 16 '20 at 08:52
  • I just tested with 2 instances of Excel. In both I started an update of UDF functions with my add-in and while it was updating I switched between both workbooks all the time. As exceptions I get two (one in each addin): "Error: An unexpected server error occurred." and "GeneralException: Wait until the previous call is completed." – Marco Siebert Apr 16 '20 at 09:56
  • Some more information for you. I just had one Excel instance open with 200 worksheets, each containing an UDF. When I update, sometimes it runs perfectly through, another time some updates do not work (when writing data to Excel). – Marco Siebert Apr 16 '20 at 10:50
  • ERROR Error: Uncaught (in promise): GeneralException: Warten Sie, bis der vorherige Aufruf abgeschlossen ist. GeneralException: Warten Sie, bis der vorherige Aufruf abgeschlossen ist. at Anonymous function (https://appsforoffice.microsoft.com/lib/1.1/hosted/excel-win32-16.01.js:24:286584) at ZoneDelegate.prototype.invoke (https://localhost:3007/polyfills.js:397:17) at onInvoke (https://localhost:3007/vendor.js:60412:17) at ZoneDelegate.prototype.invoke (https://localhost:3007/polyfills.js:397:17) at Zone.prototype.run (https://localhost:3007/polyfills.js:156:21) ... – Marco Siebert Apr 16 '20 at 10:50
  • could you try to do nothing (don't click update), when you switch to another workbook and observe whether there is the error message or not? – Raymond Lu Apr 16 '20 at 12:57
  • I just tried the following: I started my Addin in 2 Excel instances, logged in and did nothing. Then I switched between both opened default sheets a few times. No exception is coming up. – Marco Siebert Apr 17 '20 at 13:46
  • could you do an update action in instance 1 and then switch to instance 2 (don't do anything in instance 2) and observe, see if there is any error message? – Raymond Lu Apr 20 '20 at 10:49
  • Strange, sometimes it worked, another time not. I do not understand why, I do not get an error. I did some litte changes (some more awaits) and actually it is running. I will investigate more what caused the problems and I will see if it will happen again. But one problem stays. To update two worksheets, I need to run Excel in 2 separate instances (with ALT + Excel). Otherwise, it will not work and Excel crashes. – Marco Siebert Apr 21 '20 at 09:06
  • I hope i can repro in my side, do you think if you could provide a script lab gist that we can repro this issue? – Raymond Lu Apr 21 '20 at 09:12
  • I never did this until now, I will see if I can do and let you know. But give me some time. And actually I am fighting problems again :-(. Now sometimes I get a GeneralException. I will look for it first. Let us stay in contact. – Marco Siebert Apr 21 '20 at 09:45
  • 1
    The GeneralException is fixed. I do not know why, but maybe could you try this? I have an async function that is called for each occurance of a function I have found so it could be 1-n times. async refresh(...) { await Excel.run(async context => { const worksheet = context.workbook.worksheets.getItem(worksheetName); worksheet.load(); await context.sync(); ... } } When I run that in two instances of Excel, I get a GeneralException when I switch while updating to the other instance. Changing the code to : worksheet.load('name'); works. – Marco Siebert Apr 21 '20 at 09:49
  • What's the detail error message in GeneralException? – Raymond Lu Apr 21 '20 at 09:49
  • https://learn.microsoft.com/en-us/office/dev/add-ins/overview/explore-with-script-lab this is the link to show how to use script lab, this is very useful tool. – Raymond Lu Apr 21 '20 at 10:06
  • Here some more infos about the GeneralException: {"code":"GeneralException","message":"Interner Fehler während der Verarbeitung der Anforderung.","errorLocation":"Worksheet.gridlines","statement":"worksheet.load();","surroundingStatements":["var workbook=context.workbook;","var worksheets=workbook.worksheets;","var worksheet=worksheets.getItem(...);","// >>>>>","worksheet.load();","// <<<<<"],"fullStatements":["Please enable config.extendedErrorLogging to see full statements."]} – Marco Siebert Apr 21 '20 at 10:13
  • 1
    The above exception I get when I do worksheet.load() instead of worksheet.load('name') and then switch to a second Excel while updating. – Marco Siebert Apr 21 '20 at 10:15
  • 1
    Yes, this is a known issue that we are tracking now, worksheet.load() will load all properties under the worksheet, this also includes some properties (eg: worksheet.gridline) that not well supported in 2 workbooks switch scenario. we are working on a fix now here are the APIs that has similar issue: worksheet.gridline, worksheet.showheadings, worksheetFreezePanes.getLocationOrNullObject , worksheet.add, worksheet.delete, Worksheet.activate, Range.select, Workbook.getSelectedRange, Workbook.getSelectedRanges, Workbook.getActiveCell – Raymond Lu Apr 21 '20 at 11:06