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 !
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.