Ignore empty rows from a loop in Google Apps Script

Question

I'm new to Google Apps Script and I'm trying to ignore the empty rows from a for loop, but I'm still getting the empty rows in my log. Here are my codes,

function getNonEmptyRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet1 = ss.getSheetByName("Bldng4");

    var lr = sheet1.getLastRow() - 17;

    for (var i = 1; i < lr; i++) {
        var singleRow = sheet1.getRange(i, 1, 1, sheet1.getLastColumn()).getValues();
        if (singleRow.length > 0) {
            Logger.log(singleRow);
        }
    }
}

How can I get the only non empty rows from the loop?

Victor Vasconcelos · Accepted Answer · 2022-08-22 16:17:29Z

3

var range_data = 
          sheet1.getRange("A2:A") //Column Range
          .getValues() //Get array from range values
          .filter(array=>array != '') //Filter non-empty values

edited Aug 22, 2022 at 16:17

answered May 27, 2021 at 17:03

Victor Vasconcelos

464 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chrwahl Over a year ago

Hi. This answer is probably right, but it would be a good idea to explain what the code does.

Alexey · Accepted Answer · 2017-01-12 14:04:01Z

I was looking for a solution to a similar problem and here is what I did: first, I found this tutorial on how to create google apps script to eliminate duplicate rows. next, i modified it like this to eliminate empty rows:

function removeEmptyRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("settings");
  var data = sheet.getRange("A2:D").getValues();
  var newData = new Array();
  for(i in data){
    var row = data[i];
    var empty = false;
    for(i in data){
      if(row.toString() == ",,,"){
        empty = true;
      }
    }
    if(!empty){
      newData.push(row);
    }
  sheet.getRange(2, 6, newData.length, newData[0].length).setValues(newData);
};

As you can see, it takes A2:D range, removes empty rows and then pastes filtered data without empty rows into range F2:I. You can try to use this script, but you may need to adjust it to "width" of your array. To do so change the number of commas in the following string:

if(row.toString() == ",,,"){

edit: I modified script a bit to automatically adjust to width of your array:

function removeEmptyRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("settings");
  var range = sheet.getRange("A2:C");
  var data = range.getValues();
  var dataWidth = range.getWidth();
  var newData = new Array();
  if (dataWidth<=1) {
    var stringToCompare = '';
  }else{
    var stringToCompare = ',';
    for (var i=0;i<dataWidth-2;i++) stringToCompare+=","
  };
  for(i in data){
    var row = data[i];
    var empty = false;
    for(i in data){
      if(row.toString() == stringToCompare){
        empty = true;
      }
    }
    if(!empty){
      newData.push(row);
    }
  }
  sheet.getRange(2, 6, newData.length, newData[0].length).setValues(newData);
};

Serge insas · Accepted Answer · 2015-11-22 11:55:35Z

0

The getValues() method you are using always return a 2 dimensions array, whatever the contents of the cells might be. There are several ways to get the cells content, one of them is to stringify the range content (convert matrix to single string) , remove the commas (and eventually "invisible" spaces) and check the length of the resulting string.

replacement code could go like this :

var singleRow = sheet1.getRange(i, 1, 1, sheet1.getLastColumn()).getValues().toString().replace(',','').replace(' ','');

That said, this code is very inefficient because it uses SpreadSheetApp service in each loop iteration which is particularly slow. You'll find better approches in the documentation about best practices.

answered Nov 22, 2015 at 11:55

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Collectives™ on Stack Overflow

Ignore empty rows from a loop in Google Apps Script

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related