Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Google Sheets: how to make returned value of custom function overflow into a row?

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.

like image 600
Lieu Zheng Hong Avatar asked Jan 02 '16 07:01

Lieu Zheng Hong


People also ask

How do I allow text to overflow in Google Sheets?

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.

How do I continue a formula pattern in Google Sheets?

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.

How do I apply a formula to an entire column in Google Sheets?

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.


2 Answers

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);
like image 191
SpiderPig Avatar answered Oct 15 '22 06:10

SpiderPig


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

like image 32
Breandán Dalton Avatar answered Oct 15 '22 07:10

Breandán Dalton