Delete empty rows from spreadsheet via script

Question

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

When activating the script it returns an error saying:

These lines are out of range (Line 5)

My Spreadsheet:

Answer 1

In your case, I think that when maxRows-lastRow is 0 or less than 0, such error occurs. So please confirm whether there are the values to the bottom row of the sheet. If there are not values to the bottom row of the sheet, can you provide the sample Spreadsheet for replicating your issue? I would like to confirm it.

As a modification point for avoiding the error, how about the following modification?

From:

sh.deleteRows(lastRow+1, maxRows-lastRow);

To:

if (maxRows-lastRow > 0) {
  sh.deleteRows(lastRow+1, maxRows-lastRow);
}

Other pattern:

Or, how about removing the empty rows using the data region from the 1st row as follows?

function removeEmptyRows(){
  var sh = SpreadsheetApp.getActiveSheet();
  var lastRowOfDataRegion = sh.getRange("1:1").getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow();
  var maxRows = sh.getMaxRows(); 
  if (maxRows-lastRowOfDataRegion > 0) {
    sh.deleteRows(lastRowOfDataRegion+1, maxRows-lastRowOfDataRegion);
  }
}

Reference:

• getDataRegion(dimension)

If I misunderstood your question, I apologize.

Answer 2

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