Custom function array arguments behave erratically when a custom function appears twice in a spreadsheet formula

[--Hyde]

Hi,

Posted this as https://issuetracker.google.com/issues/144298728. Star the issue if you are affected by the same problem and want to see it fixed. Posting here as well since this may be of interest to the community.

Array arguments received by custom functions seem to get mangled when the same custom function appears twice in the same spreadsheet formula. For example, a formula like this will not give the expected result:

=getFirstElementRowByRow( "same id", transpose( getFirstElementRowByRow("same id", A1:A10) ) )

Here, the string argument "same id" does not need to actually be used by the custom function. But when that unused string argument differs, the formula gives the expected result:

=getFirstElementRowByRow( "outer function", transpose( getFirstElementRowByRow("inner function", A1:A10) ) )

Curiously, the latter formula (with "outer function", "inner function") will give the correct result when A1:A10 contains at least one null value, i.e, a blank cell or a cell with a formula like =iferror(1/0). It also seems to give the correct result when the argument is not a Nx1 array like A1:A10 but a NxM array like A1:B10.

The custom function will at times also report that it received a single value rather than a 2D array as expected.

Here's a publicly editable spreadsheet that demonstrates the issue:

https://docs.google.com/spreadsheets/d/1XOY4oilkCOyAL_PAyfxwEUKydeUw9sBCo4iYWnGtuR4/edit?usp=sharing

Cheers --Hyde

--------

Here is a custom function code sample that reliably reproduces the issue:

/**

* Gets the first element of a 2D array row-by-row.

* Takes a 2D array[numRows][numColumns] and returns a 2D array[numRows][1].

*

* @param {"inner function"} id A text string that identifies this function call (for error messages).

* @param {A1:A10} array A vertical spreadsheet range.

* @return {Object[][]} The first element of each row as a 2D array[numRows][1] with exactly one element per subarray.

* @customfunction

*/

function getFirstElementRowByRow(id, array) {

  if (!Array.isArray(array)) {

    return id + ': got the single element ' + JSON.stringify(array);

  }

  if (!Array.isArray(array[0])) {

    return id + ': got the 1D array ' + JSON.stringify(array);

  }

  var numRows = array.length;

  var result = new Array(numRows); // [Rhino]

  for (var row = 0; row < numRows; row++) {

    result[row] = new Array(1); // [Rhino]

    result[row][0] = array[row][0];

  }

  return result;

}

Follow these steps to reproduce the problem:

1. Create a new spreadsheet and paste the above script in Tools > Script editor.

2. Insert values in cells A1:A10 of a sheet.

3. Enter the formula =getFirstElementRowByRow( "outer function", transpose( getFirstElementRowByRow("inner function", A1:A10  ) ) in cell C1 of the sheet.

The expected result is the single value in cell A1, but the formula instead returns the 10 values in A1:A10.

Tested on Chrome 78.0.3904.70 (64-bit), Safari Version 13.0.3 (13608.3.10.10.1) and Opera 62.0.3331.101 on macOS 10.13.6 (17G9016).

[--Hyde]

An additional note: I strongly suspect that the issue is related to memoization, i.e., the mechanism used by the spreadsheet engine to cache custom function results when their arguments are identical. The spreadsheet engine seems to consider an argument like [[1],[2]] to be identical to an argument like [[1,2]], which is incorrect.

Here's a one-line custom function that reliably reproduces the issue:

/**

* Demonstrates that custom function array arguments are incorrectly memoized.

* Takes a 2D array[numRows][numColumns].

* Returns 'horizontal' when numColumns > 1, otherwise returns 'vertical'.

* To reproduce the issue, try this on a Google Sheet:

* A1: 1   B1: 2

* A2: 2   B2: -

* A3: =showArrayMemoizationBug(A1:B1) → gives 'horizontal'

* A4: =showArrayMemoizationBug(A1:A2) → gives 'vertical'

* A5: =showArrayMemoizationBug(A1:B1) & "/" & showArrayMemoizationBug(A1:A2)

*

* The formula in A5 should give 'horizontal/vertical', but it gives 'vertical/vertical' instead.

*

* @param {A1:A2} array A spreadsheet range of at least two cells.

* @customfunction

*/

function showArrayMemoizationBug(array) {

  return array[0].length > 1 ? 'horizontal' : 'vertical';

}

I have inserted this new demo custom function in the sample spreadsheet.

Cheers --Hyde

[Andrew Apell]

Shouldn't the function look like this:

function showArrayMemoizationBug(array) {
  return array[0][0].length > 1 ? 'horizontal' : 'vertical';
}

[--Hyde]

Hi Andrew,

Thanks, but the answer is no. array is a 2D array, and its first element array[0] is a subarray. The function checks the length of the subarray array[0] to determine if it contains more than one element, which indicates whether array is a horizontal or a vertical array (always assuming that array is at least two cells and either 1xN or Nx1).

The point is that in a formula like =showArrayMemoizationBug(A1:A2) & "/" & showArrayMemoizationBug(A1:B1) one of the instances of the custom function gets passed an incorrect argument because of memoization. There are no workarounds other than adding dummy parameters just to prevent memoization, which is not an acceptable workaround in an end-user targeted custom function add-on.

Please star issue 144298728 if you want to see this fixed.

Cheers --Hyde

On Thursday, November 14, 2019 at 6:58:20 AM UTC+2, Andrew Apell wrote:

Shouldn't the function look like this:

[...]

[Andrew Apell]

You're right about the 2D array; my mistake.

I'm taking a closer look at the code you posted... never seen this issue before and I work with a lot of custom functions daily.

Definitely something to investigate...

[Eric Koleda]

I think an even clearer way to demonstrate the problem is to use a function that just prints the input:

/**
* @customfunction
*/
function TOJSON(array) {
  return JSON.stringify(array);
}

I think you are spot on that memoization is to blame. It seems like they added an optimization to treat single rows or columns of identical values as being equivalent, which may be the case in the vast majority of built-in function (think SUM, AVERAGE, etc), but doesn't hold true for custom functions where you may care about the shape of the data.

[--Hyde]

Thanks Eric. I think it makes sense that the return values of many built-in functions would be memoized array-argument-shape-insensitively. But many other built-ins — QUERY comes to mind — would not work correctly if they were treated that way. And the return values of custom functions should definitely not be memoized array-argument-shape-insensitively.

As it happens, I first noticed this anomaly in a test case that compared a custom function formula result to a supposedly equivalent QUERY formula result. It took some time to figure out where the strange results were coming from.

I will paste your excellent most simple one-liner to the sample spreadsheet and issue 144298728.

Cheers --Hyde

[--Hyde]

Update: Google has filed a internal bug on issue 144298728 regarding incorrectly memoized custom function one-row or one-column array arguments.

Cheers --Hyde