Arrays as inputs to custom functions causing the add-in to fail loading

Question

I am trying to write custom functions for Excel which take arrays (i.e. a range of cells) as input and return either a value or an array.

Here's an example from my function.ts file:

/* global clearInterval, console, CustomFunctions, setInterval */

// OTHER FUNCTIONS

/**
 * Percent bias
 * @customfunction
 * @param Observed Observed Data
 * @param Simulated Simulated Data
 * @returns Percent bias
 */
function PBias(Observed: number[], Simulated: number[]): number {
  let sum_obs = Observed.reduce((partialSum, a) => partialSum + a, 0);
  let sum_sim = Simulated.reduce((partialSum, a) => partialSum + a, 0);

  return (100 * (sum_sim - sum_obs)) / sum_obs;
}
}

This gets built with no issues, but when I start debugging, I get an error which says my custom functions were not loaded.

If I take PBias out, other functions do get loaded and work properly.

Those functions seems to work as intended: TS playground demo

I have tried this with a fresh install of Node.js (version 18.18.0), and Yeoman Generator for Office Add-ins (version 4.3.1, using Excel Custom Functions with a Shared Runtime).

Answer 1

I was able to figure this out or at least find a workaround. It seems that ranges are considered 2D arrays. So, inputs should be defined as any[][]. I think this is a recent change (or a bug) introduced in the recent version of Yeoman, since I have seen online tutorials using number[] or any[] as input. In any case, this variation of my function gets loaded and works properly.

Here, I flatten the arrays prior to my calculations.

/**
 * Percent bias
 * @customfunction
 * @param Observed Observed Data
 * @param Simulated Simulated Data
 * @returns Percent bias
 */
export function PBias(Observed: number[][], Simulated: number[][]): number {
  let sum_obs = 0;
  let sum_sim = 0;

  sum_obs = Observed.reduce(function (a, b) {
    return a.concat(b);
  }) // flatten array
    .reduce(function (a, b) {
      return a + b;
    }); // sum

  sum_sim = Simulated.reduce(function (a, b) {
    return a.concat(b);
  }) // flatten array
    .reduce(function (a, b) {
      return a + b;
    }); // sum

  return (100 * (sum_sim - sum_obs)) / sum_obs;
}

I am still interested to see if there is a better approach or a clearer explanation for this.

Answer 2

As you have managed to find, you should use 2D array number[][] if you hope to take a range as input. That is because any range with more than 1 cell is considered as a 2D array (1 single cell can also be treated as 2D array like "[[A1]]").

This may not be a recent regression of Yeoman. Here is the public document about 1D, 2D and 3D arrays as parameters of custom functions. https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-parameter-options?tabs=javascript#repeating-single-value-parameter. In short: 1D array for repeating single values, 2D array for range, 3D array for repeating ranges.