[SOLVED] Is there a way to use Excel.run() inside Custom Function?

Is there a way to use Excel.run() inside Custom Function?

As a result of my Custom Function I want to fill few worksheet cells, taking cell that the function was invoked as a base cell. I have to return some complex data, that wouldn't fit in one cell.

So the question is: is there a way to use Excel.run() inside the Custom Function? Or is there a way to return more complex data (objects, array of objects) as the return value of Custom Function?

This is simple example of what I'm trying to do, but it doesn't work:

/**
 * Adds two numbers.
 * @customfunction
 * @param first First number
 * @param second Second number
 * @returns The sum of the two numbers.
 */
async function add(first: number, second: number): Promise<void> {
  return Excel.run(async context => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const range = worksheet.getRangeByIndexes(0, 0, 1, 4);
    range.values = [[1, 2, 3, 4]];

    return context.sync();
  })
}
CustomFunctions.associate("ADD", add);

Solution

It is not yet possible to work with the Excel object model from a custom function. For your scenario, as I understand it, you can return multiple values by returning a matrix of the type number[][] and enter the formula in the Excel grid as an array formula (using Ctrl+Shift+Enter). Once the dynamic arrays feature is available broadly, an array formula will no longer be needed, and the result will automatically "spill" to the adjacent cells. Dynamic arrays is currently in preview in the Office Insider builds.