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

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
– Rick Kirkham, Commented Jul 12, 2023 at 20:37
It is not offically supported but can be found here. When modified to return a single string, the code is able to add custom functions at runtime. — dakapitan
– dakapitan, Commented Jul 13, 2023 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
– Tim Lewis, Commented Jul 13, 2023 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
– Tim Lewis, Commented Jul 13, 2023 at 16:03
Have you tried setting excel shared runtime property into your manifest? — Kishan Vaishnani
– Kishan Vaishnani, Commented Jul 17, 2023 at 5:19

dakapitan · Accepted Answer · 2023-07-28 18:31:23Z

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

edited Jul 28, 2023 at 18:31

answered Jul 28, 2023 at 18:30

dakapitan

12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related