Office-js-docs-pr: Excel.CustomFunctionDimensionality.matrix is not a valid type

[chucklay]

Hi,

It seems like this page is outdated. In the "Range Parameters" section, it notes that if a range is passed as a parameter, its type will be Excel.CustomFunctionDimensionality.matrix. While using this type, a function file written in TypeScript will fail to compile, due to Excel not having an exported CustomFunctionDimensionality member. In addition, attempting to set the type of the parameter to any[][] (similar to what is done in the JS example) will cause my custom function to fail due to an "Unliftable Array", which leaves me confused: is there any way to use a range as a parameter in a custom function written in typescript?

ScriptLab Gist demonstrating the missing CustomFunctionDimensionality (not mine, but it illustrates the issue): https://gist.github.com/maninweb/8754249beff8460abeacd6a323fddd2e

ScriptLab Gist demonstrating the "Unliftable Array" issue: https://gist.github.com/chucklay/59da1f83ae283c5685c8f6ba058cbf4a

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 17035fc0-ca69-03f2-fe37-8189ff870cc7
• Version Independent ID: 2d43e719-043b-7a62-04bb-66b2cce7bf1d
• Content: Options for Excel custom functions - Office Add-ins
• Content Source: docs/excel/custom-functions-parameter-options.md
• Product: excel
• Technology: add-ins
• GitHub Login: @o365devx
• Microsoft Alias: o365devx

[alison-mk]

Hi @chucklay, thank you for taking the time to submit this issue! I will investigate and update the documentation, and then report back here.

Cheers,
Alison

[chucklay]

Hi, any news on this? This has officially prevented us from publishing, as our workaround (pass the range in as a string) was ignored by Microsoft's testers, who passed the range in as a range, and failed our add-in due to the "unliftable array" issue.

[alison-mk]

Oh no, @chucklay! I'm sorry to hear this is blocking you from publishing. Thank you for letting me know. I'm consulting @mscharlock internally so our team can get you an answer as quickly as possible.

[davidchesnut]

Hi @chucklay

I did some investigation into this to see if I could get you unstuck.

1. Thanks for reporting the doc issue. I don't believe the article is correct so we'll work to fix that.
2. To get this working, you need to configure the functions.json file to use the dimensionality property. Excel doesn't understand TypeScript. It figures out what to pass your custom function based on the functions.json file.

Basically you need to be sure it reads like this:

 {
            "description": "Returns the number of distinct data values in the provided range",
            "id": "DISTINCT",
            "name": "DISTINCT",
            "parameters": [
                {
                    "description": "The range to compute",
                    "dimensionality": "matrix",
                    "name": "range",
                    "type": "any"
                }
            ],
            "result": {
                "type": "number"
            }
        },

Now what this means is that Excel will always pass you an array. If you pass a string, it will just pass that same string to your function inside an array. From the user's perspective they can use strings and range values. But in your code you always get passed an array and you have to figure out if the source was a string or a range. I took a try at this with a sample I link at the end. But I don't know if there's a foolproof way to 100% always be correct on what the source type was for the parameter.

For example =contoso.distinct("a1") passes ["a1"] as the range value.
=contoso.distinct(a1) passes whatever is in cell one inside the array. So if a1=9 you get [9] for range. If a1="a1" you get ["a1"] as the range.

To set the dimensionality to matrix in the JSON, use the following JSDoc.

 /**
    * Returns the number of distinct data values in the provided range
    * @customfunction
    * @param {any[][]} range The range to compute
    * @returns The count of unique values in this range
    */

If you try to set it to any[][] | string, it will lose the dimensionality property. If you manually configure the JSON to use dimensionality of matrix, and still use string | any[][] in the TypeScript, you'll never see a string passed. Excel only sends an array once it sees dimensionality of matrix.

If you want to look into configuring the JSON manually with more details on the dimensionality, see Metadata for custom functions
BTW: if you want to use Excel.RequestContext or other OfficeJS APIs from a custom function, don't forget you must configure your add-in to use shared runtime. See Configure your Excel add-in to use a shared JavaScript runtime

BTW: I modified your sample to support passing strings and arrays from Excel. You always just check the array that is actually passed. But I can't guarantee it works for all cases (like when a value looks like a range which might confuse this algorithm). You can see the code sample here: https://github.com/davidchesnut/cftest.

Hope this helps,
David

[chucklay]

Hi @davidchesnut,

Your advice has gotten things straightened out, thanks! Yeah, the documentation should definitely make note of this, especially since all of the other typescript examples on that page let the functions.json file generate typings based on what types are defined in the method declaration. Your advice on allowing multiple typings on function args is appreciated too, although in this case that was primarily there as a workaround. ;)

Thanks again!
Charlie

[alison-mk]

Hi @chucklay, very glad to hear that David's advice was helpful! I'm still investigating how to best address the documentation problem, and I will leave this issue open until I've made the necessary changes to the docs. Thanks again for taking the time to report the issue, and glad we were able to offer guidance in the interim!

[alison-mk]

Hi @chucklay, I've updated this documentation article to remove the outdated information you identified (thanks!) and clarified the Range parameters subsection in the article. The update should be live soon, so I'll close this issue now. Thanks again for reporting the issue. -Alison