Question 260770 in Javascript Question posted in
A very good W3school tutorial can be found here.

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

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).

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search