How to delete all (pseudo)empty rows in google sheets

Question

I need to delete all empty rows in a sheet using google script. So far I've tried the standard approach:

function removeEmptyRows(){
  var sh = SpreadsheetApp.getActiveSheet();
  var maxRows = sh.getMaxRows(); 
  var lastRow = sh.getLastRow();
  sh.deleteRows(lastRow+1, maxRows-lastRow);
}

This works fine for truly empty rows, but fails when it comes to rows that are occupied by "invisible values" resulting from array formulas, although these formulas use IFERROR(1/0).

How do I delete all rows containing blanks?

Do you mean just rows between the last row with content and the bottom of the sheet, or any empty row? e.g. If row 1 has no contents but row 2 does, should row 1 be deleted or left in place? — Bardy, Nov 19 '16 at 18:57
use Mogsdad's code in this thread to get the "real" last row : http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row/18319032#18319032 — Serge insas, Nov 19 '16 at 19:14

Tanaike · Answer 1 · 2016-11-20T02:30:34.483

How about following script? In this script, cells with only blank (spaces) are changed to "null".

Data in all cells are put in an array.
All elements with only blanks are changed to "null" using regular expression. If cells have some symbols, please modify the regular expression.

Elements of the array are overwritten to the spreadsheet.

function removeEmptyRows() {
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getDataRange().getValues();
  for (var i in data){
      for (var j in data[i]){
          if (data[i][j].length > 0 && !/^[a-zA-Z0-9]+$/.test(data[i][j])){
              data[i][j] = null;
          }
      }
  }
  sh.getRange("A1").offset(0,0, data.length, data[0].length).setValues(data);
}

How to delete all (pseudo)empty rows in google sheets

1 Answers1