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 triggers calculation of non-volatile custom functions with certain actions like expand group or insert row #3332

Open 4tti opened 1 year ago

4tti commented 1 year ago

Provide required information needed to triage your issue

Certain actions in excel trigger recalculation of non-volatile functions although they should be treated as non-dirty and they don't need any recalculation. Identified actions so far:

This is crucial for calculation performance. Imagine case when report designer hides some technical stuff (referenced by formulas) using grouping and just expanding it will trigger recalculation of the sheet. It gets even worse with complex reports when there are cross sheet references. See video below.

Your Environment

Expected behavior

Non-volatile custom functions should be recalculated only once needed or marked as dirty.

Current behavior

Non-volatile custom functions are always recalculated unnecessarily

Steps to reproduce

  1. See video below.
  2. One formula is custom function (non-volatile)
  3. The other is sum native function (non-volatile AFAIK).
  4. See the difference in behavior

Link to live example(s)

  1. https://user-images.githubusercontent.com/19533927/235941288-8c51e84a-1f62-4b19-8550-9af2308fa9f8.mp4
ghost commented 1 year ago

Thank you for letting us know about this issue. We will take a look shortly. Thanks.

gmichaud commented 1 year ago

We noticed the same and hope this can be avoided!

rundongmsft commented 1 year ago

Hi @4tti, this is a by-design behavior. Custom function is recognized as array formula by Excel even though it returns one value. In fact, the native array formula has the same behavior. You can try the following step:

  1. Insert a native array formula at G5, for example: “=COLUMN(R1)”.
  2. Insert a column at H.
  3. You will see that the formula at G5 is recalculated.
4tti commented 1 year ago

@rundongmsft sorry but your usage is not correct because simply inserting the column will CHANGE the value to be calculated and so it is really dirty.

However, when you insert in G5 "=COLUMN(H5)" and then you insert column in Z -> no calculation happens. So please check again.

rundongmsft commented 1 year ago

Hi @4tti, when you insert in G5 "=COLUMN(H5)" and then you insert column in Z the calculation happens. Since it's calculated quickly and the result doesn't change, it's hard to realize that it's recalculated. And CF has a stage of "#busy", so it is easy to observe that there is a recalculation. In fact, Deleting or inserting a row or column will trigger recalculation in Excel when the Calculation Options is "Automatic" (by default). Even if the recalculated formulas or their references are not dirty. This situation does not only happen in array formulas, but also in others (e.g. SUM). Sorry I only mentioned array formula earlier.

4tti commented 1 year ago

Ok, you are right it is happening for at least COM/VBA user defined functions, I cannot judge if it affects even native functions. However, here is one crucial impact you should consider: Each calculation of Custom Functions usually means web request and so effectively has these consequences:

  1. because of the optimizations we collect/bulk the formulas -> we don't want to flood service with thousands of requests
  2. we send the request and process it on the service
  3. we receive "updated" value.

So effectively inserting one line can - with automatic calculation - cause 10s delay for the user.

How can I explain that to the customers who complain about it? They argue that with native or COM based UDFs it is not happening (which is "true" beause it is almost invisible because of the speed).

Could we figure out some good solution for this? Because it is really painful for the customers. I proposed to use manual calculation but it has some side-effects + it needs users to have some 'knowledge' about it.

We can - as last resort - introduce switch to manual calculation in our add-in, but it is not ideal and can cause some unwanted consequences as well (+ it is behavior change).

4tti commented 1 year ago

@rundongmsft can we get some solution please? This is really painful experience for multiple customers.

jiju-MS commented 1 year ago

Hey, @4tti , as @rundongmsft mentioned, this behavior is controlled by the excel calculation system so there is nothing custom function can do about it. The only way to avoid it is to use manual calculation.

One possible way we catch up may reduce the pain of manual calculation is that you can use data change event and formular change event to monitor the change on worksheet, then you can call "calculate" api in the event callback to help user do the calculation instead of let them do it by themselves.

when user inserting row/column or deleting row/column, the data change address will be like 1:1, which is easy to recognize and filter, in that way you may avoid the issue.

May this be of some help to you.

4tti commented 1 year ago

Thanks a lot @jiju-MS, but no - we won't override excel behavior that much + how should we prevent user of changing it to automatic? Should we even prevent it?

I understand it is controlled by excel calculation engine... but I am asking you to consider this scenario (custom functions sending web requests) as one of the main reasons why Web Add-ins are perceived as extremely inefficient compared to COM add-ins. When you work with small reports (tens/hundreds of CFs) and you have good network - it is OK. But when you start having (tens of) thousands of CFs or you have a bit slow network -> it is PAINFUL.

To give whole view - the other reason is nesting - when you e.g. chain multiple CFs the calculation prolongs significantly because of the web requests and calc dependency.

One idea how to improve it -> introduce some 'immutable' type (similar to volatile) -> which would not be recalculated with such events - only when MANUALLY marked as 'dirty' Or eventually introduce range property which could drive this (i.e. SkipRecalculation or so).

This way we would be able to significantly boost the performance. Just an idea.

rundongmsft commented 1 year ago

Hi @4tti, thanks for your suggestion, it's a good idea to solve this issue. We have created a internal product backlog 8189589 to track this.

asegal-singlepane commented 7 months ago

Hello - we have the exact same issue with our custom functions which call a webservice/api. We were about to implement batching to avoid overloading our webservice and timing out calls, but realize reading this that won't really solve our issue in that people often add/delete rows etc to reports with our functions and we can't trigger 1000s of calls every time someone does that. Has anyone thought of any clever workarounds or has there been any evolution on the MS side?

asegal-singlepane commented 6 months ago

@4tti did you make any adjustments to your UDFs to improve this?

4tti commented 6 months ago

@asegal-singlepane no adjustments after reporting this bug. We have introduced batching with initial versions of our add-in + changed formulas removing volatility (As it was nightmare) and introduced 'custom calculation' - i.e. we have our own buttons to trigger calculation of our custom functions. Not nice and with this bug still ugly.

Customers are still complaining about this behavior... we don't want to hack excel, so we are still eagerly awaiting some improvements in the Calc Engine from MS.

asegal-singlepane commented 6 months ago

Thanks for the reply @4tti. We’re going to implement batching now and also looking at creating a local cache either in local storage or within the document with custom xml so that even if the sheet refreshes on a structure change (added row) the recalc is fast because it can pull from the cache and not make a network call. Thinking together with batching this will make the behavior manageable.

Get Outlook for iOShttps://aka.ms/o0ukef

From: 4tti @.> Sent: Wednesday, March 20, 2024 11:00:16 AM To: OfficeDev/office-js @.> Cc: Austin Segal @.>; Mention @.> Subject: Re: [OfficeDev/office-js] Excel triggers calculation of non-volatile custom functions with certain actions like expand group or insert row (Issue #3332)

@asegal-singlepanehttps://github.com/asegal-singlepane no adjustments after reporting this bug. We have introduced batching with initial versions of our add-in + changed formulas removing volatility (As it was nightmare) and introduced 'custom calculation' - i.e. we have our own buttons to trigger calculation of our custom functions. Not nice and with this bug still ugly.

Customers are still complaining about this behavior... we don't want to hack excel, so we are still eagerly awaiting some improvements in the Calc Engine from MS.

— Reply to this email directly, view it on GitHubhttps://github.com/OfficeDev/office-js/issues/3332#issuecomment-2009755120, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BCNHNQWHWTHKYAKJPDNKF63YZGQABAVCNFSM6AAAAAAXUPCAQSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAMBZG42TKMJSGA. You are receiving this because you were mentioned.Message ID: @.***>