Deleting ALL empty rows in a Google Spreadsheet

Question

I've just started using Google Apps script to manage some sheets for a project i'm working on, I am new to Javascript so please go easy if there are any howlers in my code!.

We have and app called forms2mobile that captures data and drops it into a Google spreadsheet. It actually drops different data into different sheets depending on which part of the app you use.

I've hacked together a script that pulls all data from one sheet (source), and drops only certain columns into a second sheet (destination). It then deletes all rows from the source, and any blank rows from the destination.

The problem I have is with deleting blank rows from the destination. Typically the destination will have empty rows at the bottom, and the code I have will only delete empty rows within the range that contains data. So i'm always left with empty rows at the bottom.

The destination sheet will then be used as a data source for forms2mobile, which of course isn't happy with empty rows.

I've found the class getMaxRows() but i'm not sure how to implement it. If anyone could make any suggestions that would be great.

Cheers Paul

    function NEW_copyColumnNumbers( ) {
    var spreadsheet_source = SpreadsheetApp.openById('1a89ZIUcy-8168D1damCV3Q9Ix0arQn9jGS6pgp');
    var spreadsheet_target = SpreadsheetApp.openById('1GQiLt9utSH_6CV__oJwmcLOkI4E9iNIRPWU7Xr');
    var range_input = spreadsheet_source.getRange("A2:CC407");
    var range_output = spreadsheet_target.getRange("A"+(spreadsheet_target.getLastRow()+1));
    var keep_columns = [66,66,10,11,12,13,14,23,26,31,69,71,74,75,80];

    copyColumnNumbers(range_input, range_output, keep_columns);
    clearEmptyRows();
    clearSourceData();
}
function copyColumnNumbers( range_input, range_output, columns_keep_num ) {
    // Create an array of arrays containing the values in the input range.
    var range_values = range_input.getValues();    
    // Loop through each inner array.
    for ( var i = 0, row_count = range_values.length;  i < row_count; i++ ) {
        // Loop through the indices to keep and use these indices to 
       // select values from the inner array.
        for ( j = 0, col_keep_count = columns_keep_num.length; j < col_keep_count; j++ ) {
              // Capture the value to keep
              var keep_val = range_values[i][columns_keep_num[j]];
              // Write the value to the output using the offset method of the output range argument.
              range_output.offset(i,j).setValue(keep_val);
        }
    }
}
function clearEmptyRows() {
  var ss = SpreadsheetApp.openById('1GQiLt9utSH_6CV__oJwmcLOkI4E9iNIRPWU7Xr');
  var s = ss.getActiveSheet();
  var values = s.getDataRange().getValues();
  nextLine: for( var i = values.length-1; i >=0; i-- ) {
    for( var j = 0; j < values[i].length; j++ )
      if( values[i][j] != "" )
        continue nextLine;
    s.deleteRow(i+1);
  }
  //I iterate it backwards on purpose, so I do not have to calculate the indexes after a removal
}
function clearSourceData() {
  var ss = SpreadsheetApp.openById('1a89ZIUcy-8168D1damCV3Q9Ix0arQn9jGS6pgp');
  var s = ss.getActiveSheet();
  var data = s.getDataRange().getValues();
  for(var n =data.length+1 ; n<0 ;  n--){
    if(data[n][0]!=''){n++;break}
  }
  s.deleteRows(2, (s.getLastRow()-1));
}

Answer 1

This is how it works :

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

Note : you can handle columns the same way if necessary using getMaxColumn(), getLastColumn() and deleteColumns(number, howMany)

EDIT

by the way, here is also another way to delete empty rows in a spreadsheet... if you combine both it will "clean" your sheet entirely !

function deleteEmptyRows(){ 
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getDataRange().getValues();
  var targetData = new Array();
  for(n=0;n<data.length;++n){
    if(data[n].join().replace(/,/g,'')!=''){ targetData.push(data[n])};
    Logger.log(data[n].join().replace(/,/g,''))
  }
  sh.getDataRange().clear();
  sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
}

Demo sheet in view only - make a copy to use

Answer 2

Script to removeEmptyRows and removeEmptyColumns in Google Sheets. It puts together everything Serge and apptailor mentioned previously. Here is a sample sheet with the script included File > Make a copy... to edit a copy of the sheet. Also a video that shows you how to use this sheet.

//Remove All Empty Columns in the Entire Workbook
function removeEmptyColumns() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]
var maxColumns = sheet.getMaxColumns(); 
var lastColumn = sheet.getLastColumn();
if (maxColumns-lastColumn != 0){
      sheet.deleteColumns(lastColumn+1, maxColumns-lastColumn);
      }
  }
}

//Remove All Empty Rows in the Entire Workbook
function removeEmptyRows() {
var ss = SpreadsheetApp.getActive();
var allsheets = ss.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]
var maxRows = sheet.getMaxRows(); 
var lastRow = sheet.getLastRow();
if (maxRows-lastRow != 0){
      sheet.deleteRows(lastRow+1, maxRows-lastRow);
      }
  }
}

Answer 3

Just a quick note, I added this "if" statement to keep Serge insas's code from throwing an error if there is no empty bottom row when you are trying to remove empty rows.

Place this if around the last line function removeEmptyRows() and it will not throw an error:

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

Answer 4

Removing all empty lines (bottom-up)

before

after

function isEmptyRow(row){
  for (var columnIndex = 0; columnIndex < row.length; columnIndex++){
    var cell = row[columnIndex];
    if (cell){
      return false;
    }
  }
  return true;
}

function removeEmptyLines(sheet){
  var lastRowIndex = sheet.getLastRow();
  var lastColumnIndex = sheet.getLastColumn();
  var maxRowIndex = sheet.getMaxRows(); 
  var range = sheet.getRange(1, 1, lastRowIndex, lastColumnIndex);
  var data = range.getValues();
  sheet.deleteRows(lastRowIndex+1, maxRowIndex-lastRowIndex);

  for (var rowIndex = data.length - 1; rowIndex >= 0; rowIndex--){
    var row = data[rowIndex];

    if (isEmptyRow(row)){
      sheet.deleteRow(rowIndex + 1);
    }
  }

}


function removeEmptyLinesFromAllSheets(){
  SpreadsheetApp.getActive().getSheets().forEach(removeEmptyLines);
}

---

Removing only empty lines from below and above the data

before

after

function isEmptyRow(row){
  for (var columnIndex = 0; columnIndex < row.length; columnIndex++){
    var cell = row[columnIndex];
    if (cell){
      return false;
    }
  }
  return true;
}

function getFirstNonBlankRowIndex(data){
  for (var rowIndex = 0; rowIndex < data.length; rowIndex++){
    var row = data[rowIndex];

    if (!isEmptyRow(row)){
      return rowIndex;
    }
  }
  return 0;
}

function removePaddedEmptyLines(sheet){
  var lastRowIndex = sheet.getLastRow();
  var lastColumnIndex = sheet.getLastColumn();
  var maxRowIndex = sheet.getMaxRows(); 
  var range = sheet.getRange(1, 1, lastRowIndex, lastColumnIndex);
  var data = range.getValues();
  var firstRowIndex = getFirstNonBlankRowIndex(data);  
  sheet.deleteRows(lastRowIndex+1, maxRowIndex-lastRowIndex);
  sheet.deleteRows(1, firstRowIndex);
}


function removePaddedEmptyLinesFromAllSheets(){
  SpreadsheetApp.getActive().getSheets().forEach(removePaddedEmptyLines);
}

Answer 5

I have tried this piece of code and it works good, you may take a look and try it:

function DeleteBlankRows(){
    var sh = SpreadsheetApp.getActiveSheet();
    var maxRows = sh.getMaxRows();
    var lastRow = sh.getLastRow();
    for (var Raw = 1; Raw < sh.getLastRow() ; Raw++)
    {
        if( sh.getRange('A'+Raw).getValue() == '')
        {
            sh.deleteRow(Raw) //deleteRows(lastRow+1, maxRows-lastRow);
        }
    }  

Answer 6

This works perfectly for me.

function removeEmptyRows(){
      var spreadsheet = SpreadsheetApp.openById("IDOFYOURSPREADSHEETFOUNDINURL");
      var sh = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
      var maxRows = sh.getMaxRows(); 
      var lastRow = sh.getLastRow();
      sh.deleteRows(lastRow+1, maxRows-lastRow);
    }

Answer 7

This version allows you to specify top rows you don't want removed and also to ignore columns after ignoreAfterCol in case you don't want some columns considered when you are looking for blanks:

function removeEmptyLines(sheet,ignoreFirstRows,ignoreAfterCol){
  sheet=ss.getSheetByName('Sheet12')
  //get data and boundaries
  var allData = sheet.getRange(1,1,sheet.getMaxRows(),ignoreAfterCol).getValues();
  var sheetLength = allData.length;
  while(allData[allData.length-1].toString().replace(/,/g,'')=='') allData.pop();
  var lastPopulatedRow = allData.length;

  //delete empty rows from end
  var rowsToDeleteFromEnd = sheetLength - lastPopulatedRow; 
  if(rowsToDeleteFromEnd > 0) sheet.deleteRows(lastPopulatedRow+1,rowsToDeleteFromEnd);

  //iterate through rows and delete blanks one by one
  for(var i=lastPopulatedRow-1; i>ignoreFirstRows; i--){
    if(allData[i].toString().replace(/,/g,'')=='') sheet.deleteRow(i+1);
  }
}