Excel Custom Function is not shown

Question

I am creating an Excel custom function inside an office add-in. My starting point was Visual Studio 2017 scaffolding.

In my manifest I have

<FunctionFile resid="Contoso.DesktopFunctionFile.Url" />

where

<bt:Url id="Contoso.DesktopFunctionFile.Url" th:DefaultValue="${baseUrl+'/Functions/FunctionFile.html'}" />

The FunctionFile.html is the file generated by Visual Studio

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <title></title>
    <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.9.1.min.js" type="text/javascript"></script>
    <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
    
    <script src="FunctionFile.js" type="text/javascript"></script>
</head>
<body>
    <!-- NOTA: il corpo è intenzionalmente vuoto. Dal momento che viene richiamato tramite un pulsante, non esiste interfaccia utente di cui eseguire il rendering. -->
</body>
</html>

And in FunctionFile.js I put

(function () {
    Office.initialize = function (reason) {
        function sum(var1, var2){
            return  var1+var2;
        }
    };
})();

Excel Ignores function "sum" but other features of my plugin work. Is FunctionFile.js inside Office.initialize the correct place to define custom functions?

Where can I set the namespace for my functions?

Roberto Petrilli Roberto Petrilli · Accepted Answer · 2021-04-23T16:28:38

After some test I found this workaround

My manifest file look like this

    <AllFormFactors>
      <ExtensionPoint xsi:type="CustomFunctions">
        <Script>
          <SourceLocation resid="Functions.Script.Url"/>
        </Script>
        <Page>
          <SourceLocation resid="Functions.Page.Url"/>
        </Page>
        <Metadata>
          <SourceLocation resid="Functions.Metadata.Url"/>
        </Metadata>
        <Namespace resid="Functions.Namespace"/>
      </ExtensionPoint>
    </AllFormFactors>

AllFormFacotrs goes between

<Host xsi:type="Workbook">
  ...
</Host>

functionFile.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <meta http-equiv="Expires" content="0" />
    <title></title>
    <script src="https://appsforoffice.microsoft.com/lib/1.1/hosted/custom-functions-runtime.js" type="text/javascript"></script>
    <script src="functionFile.js" type="text/javascript"></script>
</head>
<body>
</body>
</html>

funciton.json is something like

{
"functions": [
  {
    "description": "Calculates the volume of a sphere.",
    "id": "SPHEREVOLUME",
    "name": "SPHEREVOLUME",
    "parameters": [
      {
        "name": "radius",
        "type": "number"
      }
    ],
    "result": {}
  }
]
}

And at last the real code goes ender functionFile.js

var funcs = {};

funcs['SPHEREVOLUME'] = function(raggio){
    return 444*raggio;
};

//ugly code
! function(e) {
    var t = {};

    function n(o) {
        if (t[o])
            return t[o].exports;
        var r = t[o] = { i: o, l: !1, exports: {} };
        return e[o].call(r.exports, r, r.exports, n), r.l = !0, r.exports
    }
    n.m = e, n.c = t, n.d = function(e, t, o) { n.o(e, t) || Object.defineProperty(e, t, { enumerable: !0, get: o }) }, n.r = function(e) { "undefined" != typeof Symbol && Symbol.toStringTag && Object.defineProperty(e, Symbol.toStringTag, { value: "Module" }), Object.defineProperty(e, "__esModule", { value: !0 }) }, n.t = function(e, t) {
        if (1 & t && (e = n(e)), 8 & t) return e;
        if (4 & t && "object" == typeof e && e && e.__esModule) return e;
        var o = Object.create(null);
        if (n.r(o), Object.defineProperty(o, "default", { enumerable: !0, value: e }), 2 & t && "string" != typeof e)
            for (var r in e) n.d(o, r, function(t) { return e[t] }.bind(null, r));
        return o
    }, n.n = function(e) { var t = e && e.__esModule ? function() { return e.default } : function() { return e }; return n.d(t, "a", t), t }, n.o = function(e, t) { return Object.prototype.hasOwnProperty.call(e, t) }, n.p = "", n(n.s = 1)
}(
    {
        1: function(e, t) {
            Object.entries(funcs).forEach(([key, value]) => {
                CustomFunctions.associate(''+key, value);
            });
            //CustomFunctions.associate("SPHEREVOLUME", funcs['SPHEREVOLUME']);
        }
    }
);

What "ugly code" is a piece of code generated by yo I cannot clean up.

In this way the function

is visible.

Excel Custom Function is not shown

1 Answers