Excel AddIn Custom Function - Unable to Dynamically Write Data to Multiple Cells

Question

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;
}

Answer 1

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" },
   },
];