CustomFunction and Data Types support in Retail Office/Office Professional Plus 2021

[thordurk91]

Users with an CF + data types add-in from the appstore are getting #VALUE errors on every function.
(Even on a function that doesn't use data types)

We also have a clientside check for the ExcelApi requirements being at least 1.16.
I have users on non-M365 with the add-in not getting clientside check errors for the ExcelApi, meaning they must have compatible Api's but are unable to use any of the functions. (They mostly do either static data fetching or jwt token based API calls and output data from either the API or a static .yaml/.json/.csv to a data type card)

If this is intended behaviour I really need a way to display this in the add-in when users are non-M365 windows desktop and knowing why wouldn't hurt.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 2306 Build 16529.20154 with Microsoft Office Professional Plus 2021 retail version
• Operating System: W10
• Browser (if using Office on the web): ______

Note: Following versions have also had reports of the same errors.

1. 2021 MSO (Vesion 2306 build 16.0.16529.20100)

This version gets a different error about not working with a retail 2019 license, more explicit. Even though it has CF 1.4 and ExcelApi 1.16

1. Office19ProPlus2019MSDNR_Retail edition

Expected behavior

To get a data type entity with the user corresponding data from the parameters in the custom function they ran.

Current behavior

User get's #VALUE on every CF

Steps to reproduce

1. Run Microsoft Office Professional Plus 2021
2. Install EVE Online add-in
3. Run any of the public non-logged in token try it out commands, fx. =EVEONLINE.INVENTORYSEARCH("Rifter", TRUE) or =EVEONLINE.TYPE(645)

Link to live example(s)

1. Link to the add-in on the app store.

Useful logs

• Console errors
• Screenshots
• Test file (if only happens on a particular file)

[ghost]

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

[thordurk91]

About 20 users on 2021 versions have reported this to us through forums/discord/tickets.

If the combination of CF + data types should only be supported on M365 I'd love to know the exact requirements and how we can client-side do a check for it.

Right now the alert to users is done by checking for this value, but the namespace etc is still injected.
Office.context.requirements.isSetSupported("ExcelApi", "1.16")

[jiju-MS]

Hey, thordurk91, data type is not supported in 2021 version as it belongs to customfunctionruntime 1.4, you can check this document for more details:
https://learn.microsoft.com/en-us/javascript/api/requirement-sets/excel/custom-functions-requirement-sets?view=word-js-preview

[thordurk91]

Hey, thordurk91, data type is not supported in 2021 version as it belongs to customfunctionruntime 1.4, you can check this document for more details: https://learn.microsoft.com/en-us/javascript/api/requirement-sets/excel/custom-functions-requirement-sets?view=word-js-preview

Hey,

I had a user with the #VALUE on a 2021 Excel issue run the following command.
Office.context.requirements.isSetSupported("CustomFunctionsRuntime", "1.4")

This returned true, and he's also getting true for
Office.context.requirements.isSetSupported("ExcelApi", "1.16")
which is my primary client side check in the add-in for compatibility.

The API requirements for customsfunctionruntime and excelapi mention "retail perpetual Office 2016 and later" as something that should be compatible.
What Excel would that pertain and how can we detect it if these isSetSupported checks aren't reliable?

[thordurk91]

These checks also seem to pass on a retail 2019 excel.
excelapi 1.16 and customfunctionruntime 1.4

These users aren't stopped from adding the addin from the store either.

[jiju-MS]

Hey, Thordurk91, we had some investigation on this issue, there is a possibility that custom function rich data can be supported on these retail excel(2019&2021), just the users need to update to new excel version which contain these functionalities to make it work, will this solution fix your current problem?

[thordurk91]

Hey, Thordurk91, we had some investigation on this issue, there is a possibility that custom function rich data can be supported on these retail excel(2019&2021), just the users need to update to new excel version which contain these functionalities to make it work, will this solution fix your current problem?

Yes, that'd be wonderful if it's possible, as far as I know most of the affected users can receive normal excel build updates within their licenses if that is what you mean by update.

[thordurk91]

@jiju-MS

Heya, do you happen to have any updates regarding this issue?

[MiaofeiWang]

Hi @thordurk91 , sorry for the late response as there is an on-going long discussion within Microsoft. We are working on a perfect solution on how to let developers get the accurate requirement set supporting info, considering this impacts released Excel builds and released requirement sets. The conclusion and solution are expected to be ready this month and we will keep you updated. Thank you.

[thordurk91]

@MiaofeiWang Heya, any news on this conclusion/solution?

[MiaofeiWang]

Hi @thordurk91 , unfortunately, due to business concern, we are not able to deliver the rich data type features to non-subscription users (including retail 2019). We will fix Office.context.requirements.isSetSupported to make it reliable when checking requirement set "CustomFunctionsRuntime". But ExcelApi 1.16 is somehow different, and we haven't found a compatible solution for all Excel builds because followed 1.17 has already been released.
As a solution, after this fix, isSetSupported("CustomFunctionsRuntime", "1.4") will return false for retail 2019 clients with the latest build (of course remains to be correct for other Excel clients). You could trust the result and show corresponding message to users. We will let you know once the build with the fix is released.

[thordurk91]

@MiaofeiWang Thanks you. We'll add the checks for 1.4 support to display the proper error.

Will having the
in the section of the manifest also properly reflect in the add-in store that the Excel in question isn't compatible? (If it's a non-subscription Excel, 2019 retail, 2021 retail) or will retail be purely reliant on the sidebar for compatibility checks.

[MiaofeiWang]

@thordurk91 I am afraid you have to rely on the taskpane to do runtime checking. As you may know, add-in store can filter out older builds which doesn't have the native code, but runtime checking (such as subscription) is not supported on store for now.

[MiaofeiWang]

Hi @thordurk91 , the change was integrated into builds later than 16.0.16924.20076, which will be available on Production by the end of October. Thanks for reporting this and providing valuable information.