Performance of excel custom functions - disable auto safe in Excel online?

1
votes

While creating and testing custom functions for an Office JS add in, I measured the performance for different platforms and get quite different results. I'm looking for chances to improve, in particular I would like to know if or how it is possible to deactivate auto save in Office online because that seems to be a reason for at least one of my problems.

The two main issues are:

  • Excel Desktop on Windows version is quite slow
  • Excel Online doesn't supports a large amount of functions

So Excel online will even crash if there are to many functions. It will try to safe and the communication to the server brakes down, showing a message about not being able to connect to the server.

We tested Excel's behavior using

  • a very simple function just returning the current date time value
  • a more complex function which gets information calculated on a web server

So depending on which scenario we test, the results are the same. In the more complex one Excel online quits at about 50,000 formulas and in the simple scenario it quits somewhere between 50,000-100,000 formulas.

Regarding the performance it seems as long as Excel online is running, it is as fast as Excel on MAC and both are twice as fast as Excel Desktop in Windows. Not to mention that COM and even VBA are way faster, than any Javascript add in runtime.

So is there a way to disable auto safe or am I just misguided to even try to run such large worksheets using Excel online and Office JS technology? Should such things still be done by using COM technology? I really like the compatibility of JS add ins...

1
office-jscustom-functions-excel
Hey great questions, these likely require a lot of follow ups to understand what is fully happening. Can you reach out to us (the Excel add-ins team) and we can investigate? Can start by reaching out at [email protected] and we can get you some help, and we'll reply back with notes. - Keyur Patel - MSFT
I did, thx for your cooperation - Developer

1 Answers

0
votes

Please see comment for specifics, would love to understand your scenario a bit more.

But, Excel Online has limitations as it relates to large data sets, and we typically recommend the desktop Mac/Com applications for that. Currently, the platform has been optimized to make web requests, vs client side calculations (which vba and com are better).

In terms of improving performance for web requests, the recommended pattern is to use a batching mechanism, where you queue your custom function calls. You can find a detailed sample here: https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-batching.

Also, you can't currently turn off autosave via an API but that is a good feature ask. can you list it on on our User Voice channel?

thanks!