0

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;
}
Tim Lewis
  • 27,813
  • 13
  • 73
  • 102
dakapitan
  • 1
  • 2
  • The Office.js reference documentation doesn't mention any Excel.CustomFunctionManager API. Where are you seeing this? Also, I'm not aware of any way to add custom functions at runtime. Since there is no way to update the functions.json file at runtime, it seemsd unlikely that there could be any way. – Rick Kirkham Jul 12 '23 at 20:37
  • It is not offically supported but can be found [here](https://stackoverflow.com/questions/66875534/what-is-the-process-involved-in-registering-custom-functions-at-runtime). When modified to return a single string, the code is able to add custom functions at runtime. – dakapitan Jul 13 '23 at 14:29
  • Do not add "RESOLVED" and the solution to your question. There is an answers section below, please post it there. – Tim Lewis Jul 13 '23 at 15:31
  • I've rolled this back for now, you can move the content to an answer by clicking the "edited just now" link above my profile/picture to view revisions. – Tim Lewis Jul 13 '23 at 16:03
  • Have you tried setting excel shared runtime property into your manifest? – Kishan Vaishnani Jul 17 '23 at 05:19

1 Answers1

0

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" },
   },
];
dakapitan
  • 1
  • 2