CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Galin Stefanov
Galin Stefanov

Reputation: 13

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.

Upvotes: 1

Views: 354

Answers (1)

Tanaike
Tanaike

Reputation: 201713

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:

Upvotes: 1

Related Questions