I have written a custom function which returns a simple array. (it is a simple dirty 3D lookup over multiple sheets). Here is the code if it helps:
function get3DCellValues(startSheet, endSheet, cell) {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sum = 0;
var cellValues = [];
for (var i = (startSheet); i < endSheet; i++ ) {
var sheet = sheets[i];
var val = sheet.getRange(cell).getValue();
cellValues.push(val);
}
//Logger.log(cellValues);
return cellValues;
}
The problem is that when I return cellValues, the values overflow down the column. But I want it to overflow rightward through the row instead. Is there a way to do so? Thank you.
Google's guide has this to say about custom functions returning values:
Every custom function must return a value to display, such that:
If a custom function returns a value, the value displays in the cell the function was called from. If a custom function returns a two-dimensional array of values, the values overflow into adjacent cells as long as those cells are empty
But this doesn't seem to be helpful to me.
Now, click on the Format menu to display the list of formatting options we can apply to the selected cell. This time, move your mouse pointer over Wrapping, and you will see the list of commands under it. From the list, click Overflow. You'll notice that the selected text will now overflow to the adjacent cells.
Use autofill to complete a series Highlight the cells. You'll see a small blue box in the lower right corner. Drag the blue box any number of cells down or across. If the cells form a series of dates or numbers, the series will continue across the selected cells.
The quickest and easiest way to apply a formula to an entire column is to: Click the column header for the column you want to apply the formula to. Type the formula you wish to use into the FX bar and press enter. Press Ctrl+D on your keyboard Ctrl+Enter works too.
Each entry in the array represents one row.
e.g. [[1,2],[3,4]]
would be two rows [1,2]
and [3,4]
.
[1,2,3,4]
is interpreted as [[1],[2],[3],[4]]
, so it's 4 rows with one value each.
If you want only one row you could write [[1,2,3,4]]
.
So you'd have to change your code like this
...
var cellValues = [[]];
...
cellValues[0].push(val);
SpiderPig's very clear answer helped me immensely.
If you always want to return just one row, then you can also write your code as
...
var cellValues = [];
...
cellValues.push(val);
...
return [cellValues];
This will return an array that contains your cellValues array as its first and only entry
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With