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
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
andB2: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]];
.
Post a Comment for "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"