Excel Custom Function handling of Entity Value data broken in latest Beta release

[jim22k]

Provide required information needed to triage your issue

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Windows, Mac
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 2406 (Build 17630.20000) / 16.86 (24051422)
• Operating System: Windows, Mac

Expected behavior

1. Cell A2 contains Entity Value data
2. My custom function accepts an arbitrary number of any input type, including entity data
3. Cell B2 has function =MYFUNC(A2)
4. MYFUNC is called and produces a result

Current behavior

When MYFUNC is called with normal input (number, string, range), it works fine.
When MYFUNC is called with Entity Value data, it sits at #BUSY! forever. Logging statements within MYFUNC do not print to the console, indicating that MYFUNC is never called.

Note: On the Current Channel, everything works fine. This is a NEW problem on the Beta channel in just the last week or two.

Steps to reproduce

1. Create a custom function using a manual functions.json file

{
    "allowCustomDataForDataTypeAny": true,
    "allowErrorForDataTypeAny": true,
    "functions": [
        {
            "id": "MYFUNC",
            "name": "MYFUNC",
            "parameters": [
                {
                    "dimensionality": "matrix",
                    "name": "dependencies",
                    "repeating": true,
                    "type": "any",
                    "optional": true
                }
            ],
            "result": {}
        }
    ]
}

2. Write the function

export function myfunc(deps: any[][][]): number {
    console.log(`deps: ${JSON.stringify(deps)}`);
    return 15;
}

3. Put a 1 in cell A1

4. In cell B1, write =MYFUNC(A1)

5. Observe that the result is 15 in the grid

6. Observe that the DevTools console displays deps: [[[1]]]

7. Put "MSFT" in Cell A2

8. Click in cell A2, then Click Data -> Stocks

9. Cell A2 will change to an Entity Value data for Microsoft Corporation

10. In cell B2, write =MYFUNC(A2)

11. Observe that the result never returns (shows #BUSY!)

12. Observe that the DevTools console doesn't display anything.

13. In cell C5, write =MYFUNC(A1).

14. Observe that it also never returns (shows #BUSY!) -- the Custom Function itself is completely broken now.

Context

I have an Excel add-in which uses this (understandably) obscure corner of Custom Functions. Things were working fine last week, but suddenly stopped working this week on the Beta channel.

I noticed another change to Custom Functions which causes empty cells to be passed to an "any"-type input as null, where it was previously passed as 0. I assume this change is related to whatever broke the Entity Value data inputs that I discuss.

Useful logs

• Console errors
• Screenshots

Thank you for taking the time to report an issue. Our triage team will respond to you in less than 72 hours. Normally, response time is <10 hours Monday through Friday. We do not triage on weekends.

[microsoft-github-policy-service]

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

[shanshanzheng-dev]

Hi @jim22k We'll be looking into this problem, thanks for reporting! Can't share timelines, but will report back here if we have a suggestion for you.

[MiaofeiWang]

@jim22k Thank you for bringing this to our attention! You are correct that the issue you reported is related to a recent change we made that treats empty cells as null instead of 0. We have stopped the feature rollout and fixed the issue you raised. Your help in identifying this problem is greatly appreciated.

By the way, we would love to hear your thoughts on how this behavior change (empty cells as null instead of 0) will impact your add-in and your customers. Do you think this change will have a positive or negative effect on the scenarios you're working with? We value your feedback and would be happy to discuss this further with you.

[thordurk91]

Great to see this solved, the EVE Online addin utilizes these kind of functions into functions approach.

For the 0 -> null change in general is a positive, as :any parameters for customfunctions already have to cover many cases of potential cell types for the data(formatted etc). So null is definetly a welcome change even if the Eve functions usually dont suffer from it as ids greater than 0 as usually what users input.

[jim22k]

I am actually okay treating empty cells as null when the CustomFunction lists the parameter type as "any". I think null makes more sense than a zero in that case.

The bigger issue for me is the bug I described where passing an Entity Value to an any[][][] parameter (i.e. {dimensionality: "matrix", repeating: true} + "allowCustomDataForDataTypeAny": true) freezes and permanently breaks the custom function. Thanks for working on a fix for that!

[MiaofeiWang]

Thanks @thordurk91 , @jim22k for the valuable feedback. We will bring the change of getting null instead of 0 for empty cell afterwards.

Regarding the initial issue, I will close this thread as it has been resolved. Thanks again for reporting this.

[jim22k]

@MiaofeiWang Looks like there is still something wrong with how any[][][] parameters are handled in Custom Functions.

See #4494