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.