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
670 stars 96 forks source link

[Excel] Duplicating a sheet with 100K custom functions takes forever in Chrome #2031

Open sshukurov opened 3 years ago

sshukurov commented 3 years ago

Some background

Our Excel JS addin contains a range of custom functions and is used by our clients which frequently build workbooks of significant size with as many as 100K-150K formulas involved. A client reported to us about very slow performance on their workbook, and while triaging it and attempting to get to a minimal repro we suddenly discovered this issue with duplicating a large worksheet. We are continuing to investigate the original performance issue, and we plan to file it separately after a more careful study. We have to admit that such issues make our addin almost unusable for our clients.

Your Environment

Expected behavior

Worksheet can be duplicated within reasonable amount of time.

Current behavior

After 5 minutes the message about the duplication progress keeps blocking the screen.

Minimal repro

  1. Create a sample addin (Excel Custom Functions) as described here.

  2. Build and sideload the addin to the workbook below (it contains a sheet with the same =CONTOSO.ADD(1, 2) formula repeated across 100K cells: WorkbookWithManyFunctions1.xlsx

  3. Attempt to duplicate the sheet.

Additional details

A screencast showing the issue in action: https://user-images.githubusercontent.com/36232686/128533951-6508f94d-04fd-4cfe-b689-f30d92543e2f.mp4

sshukurov commented 3 years ago

I just got the following error: image

I was duplicating a sheet with ten thousand custom functions this time, and it was my 5th attempt - 4 preceding duplication actions went smoothly.

grangeryy commented 3 years ago

@sshukurov, After initial investigation, for web version, there is a limitation on maximum number(50K) of Custom Functions calculated simultaneously, and the issue you encountered is caused by hitting the limitation. We've created a task to do more investigation on it: TASK 5328306. This could be more complicated than a bug level fix effort and may take time. As a short term work around, could this customer split the workbook to several separate ones so he/she can unblock at least? Thx!

sshukurov commented 3 years ago

@grangeryy thanks for the reply. Is the limitation documented anywhere?

wh1t3cAt1k commented 3 years ago

@grangeryy thanks!

Splitting the workbook is what we recommended to the client too, and we also recommended using some conditional IF() formulas to prevent unnecessarily evaluating bulky data tables which contained the majority of the invoked custom functions. These tables were used for cross-validation only. Can you confirm IF() will help?

In the meanwhile, I suggest documenting the limitation somewhere and we'll communicate it on our end as well.

Looking forward to the online platform supporting large workbooks with custom functions!

wh1t3cAt1k commented 2 years ago

Hi @grangeryy @keyur32 !

I wanted to ping if this is something scheduled to be fixed soon?

It becomes an increasingly painful issue for our larger customers who now expect their big workbooks to work consistently across all platforms (Windows, Online, Mac).

The same workbooks that worked fine on Win / Mac suddenly break when opened in Online due to the limitation.

grangeryy commented 2 years ago

Hey @wh1t3cAt1k, this item is still tracking in our backlog. Before increasing the number of custom functions for web, we are now working on addressing payload size limit for Excel Online for add-ins (this is the pre-condition to support larger amounts of calculations) and hopefully issues around large calculation will be mitigated after the effort.

wh1t3cAt1k commented 2 years ago

That's great news @grangeryy , please keep us posted!