Script to delete a dynamic range of rows

Question

I'm looking for a script that allows me to delete a dynamic range of rows. Basically I have a script that adds rows to the bottom of a list but I want another script that will delete all rows in the list. The bottom of the list is not the last row on the sheet.

The list will always start on row 12. To get to the end of the list I have a hidden column (Column A) with "end of list" so I know where the list ends.

Answer 1

This is how such a script can work. The variable startRow is fixed at 12 here - an alternative option is to take it from the current selection, which would be startRow = sheet.getActiveRange().getRow();

The script then searches column A for "end of list" text, and then deletes the required number rows.

function deleteRows() { 
  var startRow = 12;
  var sheet = SpreadsheetApp.getActiveSheet();
  var colA = sheet.getRange('A:A').getValues().map(function (row) {
    return row[0];
  });
  var endOfList = colA.indexOf('end of list') + 1;
  if (endOfList > -1) {
    sheet.deleteRows(startRow, endOfList - startRow);
  }
}

Note that this is really deletion, not merely clearing out the rows. To clear the content but leave the rows, replace the line with sheet.deleteRows by

    sheet.getRange(startRow, 1, endOfList - startRow, sheet.getLastColumn()).clear();