google-apps-scriptgoogle-sheets

Remove empty rows in Google Sheets


​I have the following problem with this function. It only deletes blank rows below but I want to delete the rows above.

function removeEmptyRows(){
  var sh = SpreadsheetApp.getActive();
  var sh1=sh.getSheetByName('name');
  var range=sh1.getRange('A:A');
  var maxRows = sh1.getMaxRows(); 
  var lastRow = sh1.getLastRow();
  sh1.deleteRows(lastRow+1, maxRows-lastRow);
}

I tried with the following function

function removeemptyrows(){
    var ss=SpreadsheetApp.getActive();
    var sh1=ss.getSheetByName('name');
    var range=sh1.getRange('A:A');
    var values = range.getValues();
    for( var i = values.length-1; i >=0; i-- ) {
      for( var j = 0; j < values[i].length; j++ )
        if( values[i][j] === "" )
          sh1.deleteRow(i+1)
          }
  }

but it deletes rows too slowly - one by one.


Solution

  • Here is my understanding:

    Modification point:

    Sample script:

    When you use this script, please enable Sheets API at Advanced Google services.

    function removeemptyrows() {
      var sheetName = "name"; // Please set the sheet name.
    
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheetName);
      var sheetId = sheet.getSheetId();
      var values = sheet.getRange('A10:A').getValues();
      var requests = values.reduce(function(ar, [e], i) {
        if (!e) ar.push({deleteDimension:{range:{sheetId:sheetId,dimension:"ROWS",startIndex:(i + 9),endIndex:(i + 10)}}});
        return ar;
      }, []).reverse();
      if (requests.length > 0) Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
    }
    

    References: