On Google Sheets Custom Functions With A Range As An Argument When Trying To Return A Value In That Argument As A 2d Array, That Value Isn't Printed

If I call the following function from a Google Sheets cell by typing '=chart(A2:A5, B2:B5' nothing is printed, not even an error message. What I expect to be printed are two rows,

Solution 1:

For your question, please be careful the following rule for the custom function.

• As the returned values of the custom function, those are number, string, boolean, 1 dimensional array, 2 dimensional array.

• When =chart(A2:A5, B2:B5) is put to a cell, at Google Apps Script side, A2:A5 and B2:B5 are 2 dimensional arrays including the cell values, respectively.

From above rule, I think that the reason of your issue is that 2 dimensional array is put to []. In this case, it's 3 dimensional array. I would like to answer for each script as follows.

In your situation, it supposes that =chart(A2:A5, B2:B5) is put to a cell for each script.

Answer 1:

About the following script,

function chart(range, range2){return[range, range2];
}

I think that the reason of your issue is that when =chart(A2:A5, B2:B5) is put to a cell, A2:A5 and B2:B5 are 2 dimensional array. For example, A2:A5 is like [['a2'],['a3'],['a4'],['a5']]. By this, [range, range2] is 3 dimensional array. When the array is used as the returned value for the custom function, 1 and 2 dimensional arrays can be used.

When you want to put the values like [range, range2], it is required to merge range and range2 as follows.

functionchart(range, range2){
  return range.concat(range2);
}

If you want to put range, range2 to 2 rows, the script is as follows.

function chart(range, range2){
  return [range.flat(), range2.flat()];
}

Answer 2:

About the following script,

functionchart(range, range2){
  vehicles = ["airplane", "motorcycle", "spaceship"]
  numbers = [43, 29, 11]
  return [vehicles, numbers];
}

In this case, vehicles and numbers are 1 dimensional array. By this, [vehicles, numbers] is 2 dimensional array, and each values are put to the cells for each row. It seems that even when 3 dimensional array is returned, no error occurs.

Answer 3:

About the following script,

function chart(range, range2){
  vehicles = ["airplane", "motorcycle", "spaceship"]
  return [range, vehicles];
}

In this case, range and vehicles are 2 dimensional array and 1 dimensional array. By this, range of [range, vehicles] cannot be put to the cells, while vehicles is put.

Answer 4:

About the following script,

function chart(range, range2){
  vehicles = ["airplane", "motorcycle", spaceship, "submarine"]
  numbers = [1, 2, range[0], 3]
  return [vehicles, numbers];
}

In this case, when spaceship is declared at elsewhere, range[0] is 1 dimensional array like [1, 2, [#, #, #,,,], 3]. Because range is 2 dimensional array. It seems that when the elements are the number and 1 dimensional array, the 1 dimensional array is not put. For example, when [1, 2, range[0], 3] is modified to [1, 2, range[0][0], 3], range[0][0] can be put.

Note:

• At the custom function, it seems that when the array is 2 dimensional array, each column length is not required to be the same like const sample = () => [[1, 2, 3], [1], [, 2, 3], [1, 2]];.

Reference:

• Custom Functions in Google Sheets