How to grab all non-empty cell data in row - Google Sheets Script Editor
Solution 1
Is this useful for you?
When there are abc | 123 | 987 | efg
, blah| cat | 654
and abc | | 987 | efg
at row 1, row 2 and row 3, myFunction(1)
, myFunction(2)
and myFunction(3)
return [abc, 123.0, 987.0, efg]
, [blah, cat, 654.0]
and [abc, , 987.0, efg]
.
function myFunction(row){
var ss = SpreadsheetApp.getActiveSheet();
var values = ss.getRange(row, 1, 1, ss.getLastColumn()).getValues();
var c = 0;
for (var c = values[0].length - 1; c >= 0; c--){
if (values[0][c] != "") break;
}
values[0].splice(c + 1, values[0].length - c - 1);
return values[0];
}
Solution 2
This is easier to achieve without a script, with the formula =filter(1:1, len(1:1))
returning all values in nonempty cells in row 1, etc.
From a script, you can do something like this:
function flat_nonempty() {
var range = SpreadsheetApp.getActiveSheet().getRange("A:A"); // range here
var values = range.getValues();
var flat = values.reduce(function(acc, row) {
return acc.concat(row.filter(function(x) {
return x != "";
}));
}, []);
Logger.log(flat); // flat list of values, no blanks
}
The range here can have one row or multiple rows.
Solution 3
Adapted from https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()
You can loop and create your array directly if you do want an array. Here I am illustrating creating for all the rows, but if you know the row you want, you could do without the outer loop (and just set i to the desired row).
function rowArr() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// This represents ALL the rows
var range = sheet.getDataRange();
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
var row = [];
for (var j = 0; j < values[i].length; j++) {
if (values[i][j]) {
row.push(values[i][j]);
}
}
Logger.log(row);
}
}
chakolatemilk
Computer Science student. Learning more from this website than I do at school. Thanks to everyone who has ever helped me in any problem I've had!
Updated on June 05, 2022Comments
-
chakolatemilk almost 2 years
I'm not sure if this is even possible, and to be quite honest, I haven't tried many things because I wasn't sure where to even start. I'm using the Script Editor from Google Sheets, btw. I know there are SpreadsheetApp.getRange() and another to get the values or something like that. But what I want is a bit specific.
Is there a way to grab all the cell data in a given row and put it into an array? The rows will vary in size, that's why I can't do an exact range.
So for example, if I were to have rows have these values:
abc | 123 | 987 | efg blah| cat | 654
I want to be able to grab those values and place them into an array like ["abc", "123", "987, "efg"]. And then if I run the function on the next row, it'd be ["blah", "cat", "654"].
Actually, it can be placed into any data type as long as there's a delimiter I'd be able to use.
Thank you in advance!
-
chakolatemilk over 6 yearsThank you! This is exactly what I was looking for.