2

Teaching myself how to code in Google Apps Script and I can't figure out how to ignore blank rows in the array, based on getRange A1 notation.

This other post works if my range is only one column, but I'm trying to do it if a whole row is blank, not just the value in one column.

How to ignore empty cell values for getRange().getValues()

function myFunction() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("sample");
 var data = sheet.getRange("A1:E").getValues();
 var filtered = data.filter(String);
Logger.log(filtered);
}

In the code sample above, my log still shows all of the blank rows after the populated rows. If I change the A1 notation to A1:A, then it works, but I want to filter based on the whole row.

Thanks for helping a newbie.

Clayton
  • 21
  • 1
  • 3

1 Answers1

6

Try this:

function myFunction() {
 var ss = SpreadsheetApp.getActive();
 var sheet = ss.getSheetByName("sample");
 var data = sheet.getRange(1,1,sheet.getLastRow(),5).getValues();
 var filtered = data.filter(String);
 Logger.log(filtered);
}

You could do it this way too.

function myFunction() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Sheet2");
 var data = sheet.getRange("A1:F").getValues();
  var filtered = data.filter(function(r){
    return r.join("").length>0;
  });
Logger.log(filtered);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks for your help Cooper. Tried the second option and it works great. When writing scripts, is it better practice to use the getRange notation in your first example instead of the A1 notation in the second example? I just did it that way because I'm more used to spreadsheet-based range references, but I can update my thinking if it's a bad way of going about it. Thanks again. – Clayton Jul 03 '19 at 21:18
  • It's up to you. I'm a programmer so I avoid the use of cell functions except in very rare situations. I like the first technique because generally that A1 notation returns all of the rows all the way down to maxRows() so then they often have to be removed some how and that just takes more time. – Cooper Jul 03 '19 at 21:24
  • Great thanks. I'll try to start using the other notation. – Clayton Jul 05 '19 at 13:18