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

admin管理员组

文章数量:1221774

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.

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.

• javascript
• google-apps-script
• google-sheets
• google-apps

Share Improve this question Follow asked Jan 2, 2016 at 7:21 Lieu Zheng HongLieu Zheng Hong 69622 gold badges1010 silver badges2222 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 15

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

本文标签： javascriptGoogle Sheets how to make returned value of custom function overflow into a rowStack Overflow