How to grab all non-empty cell data in row - Google Sheets Script Editor

Question

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!

score 5 · Answer 1 · answered Jan 02 '18 at 03:38

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.

Elsa · Accepted Answer · 2018-01-02T05:13:20.427

4

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];
}

edited Jan 02 '18 at 05:13

answered Jan 02 '18 at 05:04

Elsa

654
1
8
21

Thank you! This is exactly what I was looking for. – chakolatemilk Jan 02 '18 at 15:30

score 2 · Answer 3 · edited Dec 09 '19 at 21:07

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);
  }
}

How to grab all non-empty cell data in row - Google Sheets Script Editor

3 Answers3

Linked