I'm working on an Excel add-in using Office JavaScript API, and I'm facing an issue with writing data to multiple cells using a custom function that I add dynamically during runtime. It seems that the Excel is not recognizing the type: string[][].
Here is some code that I use to add the custom functions at runtime. It works perfectly for single cells but returns #VAlUE! when the I try to return an array:
const section = "MakeTable";
const description = "Make a table";
const excelParams = [];
const configFunctionProperties = [
{
id: section,
name: section,
description: description,
parameters: excelParams,
result: {
type: "string[][]", // change to string for single cell
},
},
];
const functionString = "async () => {
return [['first', 'second', 'third']]; // return single string for single cell
}";
Excel.run(async (context) => {
await (Excel as any).CustomFunctionManager.register(
JSON.stringify({
functions: configFunctionProperties,
}),
""
);
CustomFunctions.associate(section, eval(functionString));
await context.sync();
console.log("Custom function registered successfully!");
}).catch((error) => {
console.error("Error registering custom function:", error);
});
This works perfectly but is not done at runtime:
/**
* Get text values that spill to the right.
* @customfunction
* @returns {string[][]} A dynamic array with multiple results.
*/
function spillRight() {
let returnVal = [["first", "second", "third"]];
console.log(typeof returnVal);
return returnVal;
}
I found changing the dimensionality seems to fix the issue:
let configFunctionProperties = [
{
id: section,
name: section,
description: description,
parameters: excelParams,
result: { type: "string[][]", dimensionality: "matrix" },
},
];