Delete rows hidden due to filter

Question

I need help with deleting rows that are hidden due to user filter.

I have created a dummy sheet with data linked below. What I want: When I delete a row in sheet 1, the same row should be deleted in sheet 2. (in the actual spreadsheet, sheet 1 are form entries, which are copied over to sheet 2 using array formulas for data manipulation).

When I use the formula, the row gets deleted in sheet 1, however in sheet 2 it doesn't get deleted, because it is hidden due to a filter set by user.

I tested this by removing the filter and it works. I need to find a way to delete the row without having to remove the filter. Please find the working files below and the code snippet.

Google Spreadsheet: https://docs.google.com/spreadsheets/d/1uiJzzJ9TvsJBLT6_i1S3JbXAi0zUl8wvk1uuIZ9_LdA/edit?usp=sharing

function deleteRow() {

    var sheet = SpreadsheetApp.getActiveSheet();
    var sheetName = sheet.getName();
    
    if(sheetName == "Sheet 1"){
    var eocDataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 2")
    var rowPos = sheet.getActiveRange().getRow();
    var numRows = sheet.getActiveRange().getNumRows();

    eocDataSheet.deleteRows(rowPos, numRows);
    sheet.deleteRows(rowPos, numRows);
    }
      
}

Answer 1

I am afraid you have to get all filtered rows, check if the rows you intend to delete intersect with the filtered rows, get existing filters, un-filter temporarily, delete the rows in question, and re-apply the previous filters.

How to get all filtered rows

See this example page. Subsection Get filtered rows.

How to get existing filters

See .getColumnFilterCriteria() here.