Google Script to Delete a Range of Empty Rows

user_19018358 0
Viewed 123

I need to use a button to delete empty rows in the range A1:A25 on Sheet1 in google sheets. Column A will be the reference column for empty cells. I cannot seem to find a simple answer on how to define the Range for the deletion of empty rows. I do not want it to delete empty rows below row 25.

// Deletes any row whose first column is blank
// WARNING: Assumes any valid row has data in column 1
function deleteBlankRows() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("Sheet1");
  var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();

  for (var i = lastRow; i > 0; i--) {
    var range = sheet.getRange(i,1); 
    var data = range.getValue();
    if (data == '') {
      sheet.deleteRow(i);
    }
  }
}
google-apps-script google-sheets
1 Answers

I believe your goal is as follows.

  • You want to delete rows when the column "A" is empty.
  • As a condition, you don't want to delete rows after row 25.

When your script is modified, how about the following modification?

From:

var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();

To:

var lastRow = 25;
  • In this modification, when the column "A" of row 25 is empty, that row is deleted. When you want to leave row 25, please modify it to var lastRow = 26;.

Note:

  • In your script, when deleteRow() is run in the loop, when the process time is long, how about the folloging modified script? This modified script deletes the rows with the empty of column "A" before the row 25. If you want to leave row 25, please modify var v1 = values.splice(0, 25); to var v1 = values.splice(0, 26);.

      function deleteBlankRows() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName("Sheet1");
    var range = sheet.getDataRange();
    var values = range.getValues();
    var v1 = values.splice(0, 25);
    var res = [...v1.filter(([a]) => a.toString() != ""), ...values];
    range.clearContent();
    sheet.getRange(1, 1, res.length, res[0].length).setValues(res);
  }
    • In this case, when you want to delete rows after row 25, please modify var res = [...v1.filter(([a]) => a.toString() != ""), ...values]; to var res = [...v1, ...values.filter(([a]) => a.toString() != "")];.

Added:

From your following reply,

The change to last row worked great. I, however, now need to stop the deletion at row 11. The code I have deletes all the way to row 1 (I know that is what is in my original post). Things changed in the last few minutes.

When you want to delete rows from 11 to 25 and when you want to modify your script, how about the following modification?

From:

var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();

for (var i = lastRow; i > 0; i--) {

To:

var lastRow = 25;
for (var i = lastRow; i >= 11; i--) {

  • In this modification, the rows are deleted from 11 to 25.

  • If you will use my 2nd modification, how about the following modification?

      function deleteBlankRows() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName("Sheet1");
    var range = sheet.getDataRange();
    var values = range.getValues();
    var v2 = values.splice(0, 25);
    var v1 = v2.splice(0, 10);
    var res = [...v1, ...v2.filter(([a]) => a.toString() != ""), ...values];
    range.clearContent();
    sheet.getRange(1, 1, res.length, res[0].length).setValues(res);
  }
user_71086530
Related