Excel API call with custom function

Question

I'm trying to create a custom function calling method from Excel.js API. I have followed the Excel custom function tutorial.
But I always obtained the error value #VALUE! on the worksheet, and this error on the debug:

Verbose Runtime [Console] [Log] Unexpected CustomFunctions [Execution] [End] [Failure] [ExceptionThrown] Function=EXTRACTFORM ReferenceError: 'Excel' is not defined {}     
Unexpected  CustomFunctions [Execution] [Async] [End] [Failure] Function=EXTRACTFORM, Workbook=Book1.xlsx

I'm using the following code:

For the description file:

{
  "functions": [
    {
      "id": "EXTRACTFORM",
      "name": "EXTRACTFORM",
      "description": "Extract formData from relaunch button",
      "result": {
        "type": "string",
        "dimensionality": "scalar"
      },
      "parameters": [
        {
          "name": "address",
          "description": "",
          "type": "string",
          "dimensionality": "scalar"
        }
      ]
    }
  ]
}

js source:

function run(address) {
    var context = new Excel.RequestContext();
    var range = context.workbook.worksheets.getActiveWorksheet().getRange(address);
    range.load();
    return context.sync()
        .then(function() {
            return range.values[0][0];
        });
}
CustomFunctions.associate("EXTRACTFORM", run);

And 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>"Custom Functions Upgrade Test"</title>
      <script src="https://appsforoffice.microsoft.com/lib/1.1/hosted/custom-functions-runtime.js" type="text/javascript"></script>
      <script src="./extractForm.js" type="text/javascript"></script>
    </head>
    
    <body>
        <h1>"Custom function"</h1>
    </body>
</html>

Thank you for your help !

score 0 · Answer 1 · answered Sep 13 '20 at 11:09

0

In order to make custom function to call Excel.js API method, you need to configure your add-in to use shared runtime. And please note there are some limitations to call Office.js through a custom function.

answered Sep 13 '20 at 11:09

xiaochun

114
5

I have configure my add-in like described in the tutorial "configure your add-in to use shared runtime". The error "Excel' is not defined {}" seems to be resolved. But I always have the error : "Unexpected CustomFunctions [Execution] [Async] [End] [Failure] Function=EXTRACTFORM, Workbook=Book1.xlsx" and the result display on the sheet is "#NA". – Sami.S Sep 14 '20 at 08:24
The reason the result display "#NA" is due to the failure of locating functions.js. You can reference it in "taskpane.html". – xiaochun Sep 15 '20 at 08:07
Adding in "taskpane.html" – xiaochun Sep 15 '20 at 08:17

Excel API call with custom function

1 Answers1