Delete rows hidden due to filter

1
votes

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);
    }
      
}
1
google-apps-scriptgoogle-sheetsfilterdelete-row
In the working sheet, in sheet 2, if you look at the last column, when you run the script, if the row is hidden due to filter, it doesn't get deleted. it appears to get deleted in the other columns due the them being array formulas from sheet 1 where it copies the data over. Just thought I should add this for clarity. – Jayy Jangam
Why do you need to delete rows in sheet 2 if it is a copy using arrayformula? – Argyll
only columns 1-3 are array formulas column 4 onwards are no longer arrays and is manual data input, so the manual data input does not move, and that results in the data being in the wrong row. – Jayy Jangam
Well, someone beat me to the answer. Indeed, I can confirm that filter blocks .deleteRow(). For the future, questions that do best in attracting answers are those employing minimal examples. For example, this question can focus on "how to delete filtered rows" and share an example sheet demonstrating the effect without getting into any specific task at hand. – Argyll

1 Answers

2
votes

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.