Delete Google Sheets Rows Effectively With a Script

Google Apps Script has the method deleteRow(), and as its name suggests, deletes a given row. 

Great. But if you go ahead and use this method inside an array of non-consecutive rows, you’ll encounter an unexpected behavior: the rows being deleted are not those you’ve meant to delete. Oh no. 

Why does it happen?

The method deleteRow() accepts one argument – rowPosition. So when we wish to delete row no 1, we can just go ahead and apply the method on the sheet with sheet.deleteRow(1). 

When we delete a row, the number of rows in the sheet changes dynamically. Meaning, when row 1 was deleted, the once upon a time row 2 became the new row 1. 

What does it mean for our scripts?

Let’s say that we wish to delete all the rows that say “Delete me” on column B:

Index	A	B
1	Row 1	Delete me
2	Row 2	Keep me
3	Row 3	Keep me
4	Row 4	Delete me
5	Row 5	Delete me

First, we iterate over the column values and find the “delete me” rows. When found, we add the row index to a “delete_me_rows”  array:

function deleteRows() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  let all_rows = sheet.getMaxRows()
  let all_values = sheet.getRange('B:B').getValues().flat()
  let delete_me_rows = [];
  all_values = all_values.filter((element, index) => {
    if (element === 'Delete me') {
      delete_me_rows.push(index + 1)
    }
  });
}

Now that we have an array of the relevant rows, let’s delete them. First, let’s try with a regular loop:

for (let r = 0; r < delete_me_rows.length; r++) {
  sheet.deleteRow(delete_me_rows[r]);
}

And here’s what happens:

See that we were left with “delete me” rows? That’s because whenever a row is being deleted, the row indices change dynamically. So our second element in the array (row 4) is not row 4 anymore. 

To overcome this, we will iterate over the delete_me_rows indices array backwards: from the last row to the first one. Here’s our new loop and how it looks like on the spreadsheet:

for (let r = delete_me_rows.length - 1; r >= 0; r--) {
  sheet.deleteRow(delete_me_rows[r]);
}

Notice that now we are left with only the “keep me” rows. On Column A, you can see the original row index and the row index after the script was run.

Here’s the complete code:

function getRowsToDelete() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  let all_rows = sheet.getMaxRows()
  let all_values = sheet.getRange('B:B').getValues().flat()
  let delete_me_rows = [];
  all_values = all_values.filter((element, index) => {
    if (element === 'Delete me') {
      delete_me_rows.push(index + 1)
    }
  });
  for (let r = delete_me_rows.length - 1; r >= 0; r--) {
    sheet.deleteRow(delete_me_rows[r]);
  }
}