Office-js: GeneralException when two workbooks are opened

[zjomzjom]

Excel javascript API (both 1908 and 1909 ) throws GeneralException when workbook calls ctx.sync() and in another workbook, a cell is selected.

Expected Behavior

Selection change in the active workbook should not affect other workbooks.

Current Behavior

When some cell is selected in the second workbook, an exception is thrown in the first workbook, where ctx.sync() is called. The problem does not occur when we open a new excel workbook as a separate instance (with /x parameter).

Steps to Reproduce, or Live Example

1. open two workbooks
2. run code in Scriptlab

async function run() {
  setInterval(async ()=>{
    await Excel.run(async (ctx) => {
      ctx.workbook.worksheets.add("sheet");
      await ctx.sync();
      let worksheet = ctx.workbook.worksheets.getItem("sheet");
      worksheet.delete();
      await ctx.sync();
    });
  }, 3000);
}

1. select cell in the second workbook

Context

We have long-running jobs (downloading data and loading it into excel). During that, we want to work in the second workbook.

Your Environment

• Platform: PC desktop
• Host: Excel
• Office version number: 1909
• Operating System: Windows 10

Useful logs

[catchingyu]

@zjomzjom how do you create/open the second workbook ? Do you open the second workbook through Excel UI directly?

[zjomzjom]

@catchingyu I have tried a few ways to open a new/second workbook. No matter which way I selected, the problem still exists.

1. Creating new workbook through Excel UI
2. Loading an xlsl file through Excel UI
3. By clicking on excel icon in menu-start
4. Opening an xlsl file

[MandytMSFT]

I can repro this issue, file BUG 3710971 for track

[MandytMSFT]

@zjomzjom could you help explain a little on your scenario ? The root cause has figured out, we want to know more about your case, for example, why use setInterval to add bunch of new worksheet ?

[zjomzjom]

I have used "setInterval" method to make some async actions. I wanted to show you that there is a problem with the working of multiple workbooks parallel. It is not only a problem with "worksheet.add" method. Calling ctx.sync() with selection in other workbooks throws an exception.
Another example: Scriptlab sample snippet "Gradient", but with little modifications (to be able to change focus to the second workbook before ctx.sync) also throws GeneralException:

We have got datasets with a huge amount of documents and we want to work with these datasets in separated workbooks. For example, we want to download 50 000 documents from a certain dataset and load it to the first workbook. We can get the only chunk of data from backend per 1 call.

Steps of the algorithm are:

1. download chunk of data
2. make a grouping based on some attribute
3. check if worksheet for certain group already exists and create if not (usage of ctx.sync())
4. load data into excel (usage of ctx.sync())
5. finish/goto step 1

Or another case: show the differences between excel and backend.

1. download chunk of data
2. check the differences
3. highlight the differences (usage of ctx.sync() to change the background color of the cell)

These long-running operations may take for example 30mins. During that time, we want to work with another dataset in the second workbook and we are not able to do that, because of GeneralException when we select some cell in the second workbook.

Besides long-running operations, if you want to do some small things (color some cells) which would take for example 10 seconds, you cannot change active workbook during that operation.

[edwardcawley]

We're also having this issue. JS which takes a minute or two to run as it is inserting many sheets with lots of data. In the mean time the user decides to do some other Excel work and opens another book, this crashes the JS.

Not always at the same line. It seems, as others have pointed out, that the context.sync() breaks when another book is being worked on, it could be any .sync().

Good to know about forcing another instance of Excel though as a workaround.

[catchingyu]

Currently, this is a known gap for our Excel JS API working on multiple worksheets in the same process. The best workaround currently is to force to open another instance/process of Excel to solve this problem.

[zjomzjom]

Even if you force Excel to open in the new instance, opening files through the Windows Explorer still causes the problem:

https://docs.microsoft.com/en-us/office/troubleshoot/excel/force-excel-to-open-new-instance

"This method works only when you use the Excel icon to open the application. If you use File Open within the Excel application or double-click a file in Windows Explorer, the files will still open in the same instance as designed."

And

"There are some known issues in opening each Excel spreadsheet in its own instance. For example, see You cannot paste any attributes into a workbook in another instance of Excel. Therefore, you cannot copy any one of the following elements:
Formulas
Values
Formats
Comments"

@catchingyu, @MandytMSFT, @kbrandl could you tell me whether you will fix this issue soon?

[mitoihs]

I was going to submit another issue, but it's very similar to mine, so I'll just post it there:

We're developing Excel addin. Sometimes, when our addin is currently working and I'm opening another Excel workbook, I'm getting:

GeneralException: There was an internal error while processing the request. at RuntimeError (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:10059:5) at ClientRequestContext.prototype.processRequestExecutorResponseMessage (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13147:5) at Anonymous function (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13054:5) at run (http://localhost:8080/app.js:49737:13) at Anonymous function (http://localhost:8080/app.js:49750:30) at flush (http://localhost:8080/app.js:45586:9)

Expected Behavior

No error or at least more descriptive message about what & why happened.

Current Behavior

GeneralException

Steps to Reproduce, or Live Example

It's hard to reproduce, it happens rarely. In our code it's happening like once for 20-30 new workbooks opened. I'll try to find out better repro and will provide it if/when found.

We're not opening/closing workbooks using office-js. We're just opening new Excel instances using Windows Start menu.

Context

We're trying to keep addin running stable no matter if user is currently opening/closing any workbooks. Our addin downloads and puts lots of data into Excel. Also we use ctx.workbook.worksheets.onAdded / ctx.workbook.worksheets.onDeleted events to keep track of already existing worksheets in our addin.

Your Environment

• Platform [PC desktop, Mac, iOS, Office Online]: PC desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 1912 (Build 12325.20298)
• Operating System: Windows, Version 1909

So addin is running on Edge's WebView.

Useful logs

• [x] Console errors

GeneralException: There was an internal error while processing the request. at RuntimeError (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:10059:5) at ClientRequestContext.prototype.processRequestExecutorResponseMessage (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13147:5) at Anonymous function (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13054:5) at run (http://localhost:8080/app.js:49737:13) at Anonymous function (http://localhost:8080/app.js:49750:30) at flush (http://localhost:8080/app.js:45586:9)

Surrounding statements:

var workbook=context.workbook;
var worksheets=workbook.worksheets;
// >>>>>
worksheets.load(["*"]);
// <<<<<

• [x] Screenshots
  
• [ ] Test file (if only happens on a particular file)

[lumine2008]

Thank mitoihsThis is a known gap for our Excel JS API working on multiple worksheets，but there is a workaround that you could try is to open a new excel workbook as a separate instance.

[mitoihs]

Thank you for the workaround. We can use it locally, but we cannot demand that our users will "hack" registry. (if I understand it right, the workaround is to use this)

[lumine2008]

Correct, this is the workaround

[mitoihs]

And is there an issue to follow about that "known gap"? Because I see that one is closed.

[lumine2008]

it's in our backlog, the effort is not small, I would suggest that you could submit your request in uservoice and upvote for this feature for Excel online at https://officespdev.uservoice.com/

[mitoihs]

According to your suggestion, I've submitted UserVoice request

[zjomzjom]

@lumine2008 As I wrote above, running in separated instances is not a sufficient workaround, because if you open Excel file through the explorer, it is being loaded to an existing instance. Additionally, you cannot copy formulas, values, formats, and comments through instances.

[zjomzjom]

Hi @lumine2008, could you tell me the status of this task? When you plan to release the bugfix?

[extremeandy]

Are there any other workarounds for this? Our users are encountering this error and the suggested workaround is not suitable given that we have many clients using our add-in.

[lumine2008]

Thanks for the patient, this issue was fixed and check-in, but it is in the rolling out stage,
You could try insider build (Build number >= 13127) or semi-annual build in 2021 Jan for these 2 APIs

• worksheets.add()
• worksheet.load()

You could try insider build (Build number >= 13328) or semi-annual build in 2021 July for this API

• worksheet.delete()

You could have validate and let us know if they can unblock your scenario or not. thanks.

[extremeandy]

That's great news, thanks @lumine2008.

Unfortunately we don't have control over which version of Excel our users are running so likely scenario is it will be 1-2 years before this fix makes it into their systems. So we still need a workaround until then.

We don't even have any way of identifying whether the user opened another workbook; at least if we did we could warn the user. For now we are just forced to show them an error message and suggest they try closing and re-opening the add-in; not a great user experience.

[mitoihs]

Hi @lumine2008 ! Is that fix about all cases of GeneralException being thrown during various office-js operations or just when those 3 worksheet APIs are used?

[lumine2008]

Hi @mitoihs , Thanks for your question. The general exception issue in this scenario is caused by 2nd workbook is opened, so the fix is to let these APIs can be run successfully when 2nd workbook is opened.

[zjomzjom]

@lumine2008 unfortunately, we have found functions that are not working when multiple workbooks are opened and the workbook has no focus:
function worksheet.activate();
setting the new value of worksheet.position;
functions defined in worksheet.freezePane.

Is there any chance to fix those functions? If it would be hard to fix, maybe you could introduce a mechanism similar to 'delayForCellEdit' that would wait until the workbook will get focus again. Another helpful mechanism would be a flag that could indicate whether the workbook is focused or not and maybe a new event firing when a workbook focus has changed.

[lumine2008]

Thanks @zjomzjom ,

For worksheet.activate() and worksheet.freezePane(), we found a high risk to fix this issue, especially for worksheet.activate(), it will change the focus, so we decide to throw a more meaningful error code InactiveWorkbook.
because when workbook is inactivate, if you call worksheet.activate sucessfully, it will activate the workbook in the background. which is a wired user experience. so we decide not to support when it is running in inactivate workbook.

worksheet.position is a new issue to us. I have created a bug to track this issue #4856133

PS. would you please share us your scenario of using worksheet.activate() and worksheet.freezePane() when 2nd workbook is opened?