Search code examples
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:

    • You want to delete the rows that the cell of column "A" is empty in the range from 10 row to bottom of sheet.
    • You want to achieve this using Google Apps Script.
    • You want to reduce the process cost.

    Modification point:

    • In this case, I would like to propose to use Sheets API. When Sheets API is used, the rows can be deleted by one API call, even when the rows are discreted.

    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());
    }
    
    • In this case, the sample script is almost the same with the below script of https://stackoverflow.com/a/60613983/7108653 . As 2 modification parts, in your case, you want to delete the rows of empty cell at the column "A". So 'C6:C' + sheet.getLastRow() and if (e) were modified to 'A10:A' and if (!e), respectively.

    References: