#NAME issue for custom functions at random times on loading a workbook about office-js

[wh1t3cAt1k]

FYI our support team just mentioned that over the last months we received tens of tickets with the functions stuck in the #NAME state...

[gmichaud]

@demyte we also face this issue in production and it negatively affects the user experience. So far we have not been able to isolate the issue or identify replication steps. On a related note, have you faced an issue where files that have been impacted by the #NAME error have an issue where custom functions then show a #VALUE! or similar error and completely fail to refresh (e.g. the custom function doesn’t get called at all). I documented this problem internally yesterday so that we can file it here, but the starting point was basically when functions failed to register. The only workaround on affected files is to either go to formula bar and press enter on each formula, or do a full recalculation followed by a Calculate Sheet on each affected sheet — something users can’t be expected to figure out on their own let alone do reliably on workbook containing 100+ sheets and hidden sheets.

[demyte]

@gmichaud Yes, we also get that from time to time (not as frequent as this current issue) and have no other way to resolve it beyond what you note.

I agree that it does seem either partially or fully related to this issue of the functions not correctly loading that we are seeing, so hopefully if we can get a resolution to one, we get it for both. 🤞

[gmichaud]

@demyte thanks for your reply, good to know that it’s not just us experiencing this problem. We have saved a file in this state and will create an issue on GitHub in the coming days to document the problem where custom-function cells no longer recalculate.

[MiaofeiWang]

Hi @demyte , thanks for reporting this issue. I am not able to reproduce this issue on my side. Could you please help provide a screen recording if the sample file/data is not convenient to share? I think you are right that it may be related to loading of Office JS files.

[demyte]

Hi @MiaofeiWang

It is not something we have been able to create a reproducible scenario for at all as it is so random.

When we get a support call from a user, we are in triage mode and our aim is to get them back and working again asap.

---

Some thoughts...

The only consistent thing about it is that when it occurs it is on the load of a workbook.

It does not seem to be workbook related as on a reinstall of the add-in and reload of the same workbook, it's all fine.

The fact that when it is in this state and we (as shown above) go and re-associate the custom functions callback it shows as it already being associated, feels to me like there could be some race condition in the OfficeJS loading of the functions.json into Excel and the calls to CustomFunctions.associate(...) that is occasionally being triggered so the CustomFunctions runtime thinks they are loaded but Excel doesn't?

I know there is an internal registration call that ScriptLab uses (CustomFunctionManager.register(jsonMetadataString, code);) to just-in-time register functions into the runtime, could we use that to force reload our functions into the runtime?

---

We are really invested in getting this issue resolved for our users, as along with @gmichaud #4502, they are real confidence/trust breaking issues for our product, that, from what we know, are not to do with us, but with the OfficeJS implementation.

Please let me know if there is anything I can do to help get a resolution for this.

[gmichaud]

@MiaofeiWang we don't have any replication steps either and also get support requests for this issue every month. It's usually urgent and we don't have time to do much investigation as we have to get the customer's machine up and running again. I definitely affect the confidence/trust in our solution. Reinstalling the add-in and/or clearing the add-in caches fixes it. Is there a folder, log or registry hive that we should backup for you to be able to investigate this the next time it happens?

[MiaofeiWang]

Thanks @demyte @gmichaud for the information. Agree that we are dealing with a race condition, which inherently makes it challenging to consistently reproduce the scenario. Currently, we lack specific logs that would allow us to trace each step that occurs.

@gmichaud could you please clarify if the #NAME issue of your add-in also occurs on streaming functions?

Regarding the dynamic registration feature used by ScriptLab, it employs a specialized method that is not yet broadly available for other add-ins. We apologize for any inconvenience this may cause. Our team is actively exploring the feasibility and necessary efforts to make this feature accessible to other add-ins in the future.

[gmichaud]

@MiaofeiWang we don’t use streaming functions so I can’t comment on that unfortunately

[MiaofeiWang]

@gmichaud , thanks. May I know if there are entities (instead of primitive types like boolean, number and string) in the file that are output by your functions when the #NAME issue happens during workbook loading? Recently we fixed an issue that loading workbook triggers functions (with entities as output) unexpected re-calculation. We expect this fix will resolve some of the #VALUE! issues.

[gmichaud]

@MiaofeiWang we don’t return entities, but an important detail - our functions are dynamic-aware and can return either a single scalar value or an array.

[wh1t3cAt1k]

@gmichaud @MiaofeiWang correction, actually we do return FNVs (for ISO-formatted dates) which are "kind of" "entities" because they are not simple booleans/numbers/strings. And we have allowCustomDataForDataTypeAny enabled in our manifest.

Our bug is exactly about #VALUE errors #4502 - @MiaofeiWang if the fix is already delivered what is the ETA (or ideally a build number) of the deployment so we can test? 🙏

[demyte]

@MiaofeiWang Is there any extra detailed platform logging we can enable to assist with this?

As a dev, I understand completely how hard these issues are to lock down to a cause, but we really need Microsoft to get this resolved and provide a robust experience here.

Also, we use allowCustomDataForDataTypeAny:true for our functions and some of them return entities and others just numbers/text. When the #NAME issue occurs, it's for all of our functions.

[MiaofeiWang]

@wh1t3cAt1k Sure. The re-calculation issue was fixed for Excel on Windows with build >= 16.0.17723.20000. This will prevent from some unexpected re-calculation which may reduce possibility of #VALUE issue. But I have no enough confidence that this can resolve the issue of #4502 .

@demyte Totally understand your feeling. We are investigating the possible root cause of #NAME issue on streaming functions. But unfortunately no luck as we are not able to reproduce this even for one time. So one screen recording may be able to help us.