CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tony
Tony

Reputation: 1

Is there any way to select and delete only filtered(visible) entries via script?

Basically I want to apply one or more filters, then select everything that is visible and delete the selected rows , all must be done via script. However, I don't see such option anywhere (Excel has the special go to and select only visible) . For instance one session may start from row 7 , I can record the macro from row 7 and down... but if another dataset shows the visible row starting from 2. How would this be affected? Is there a simple way to select everything that is visible?

Upvotes: 0

Views: 2063

Answers (2)

Jasdeep Singh
Jasdeep Singh

Reputation: 11

Hope Cooper's answer helped

I worked on something similar and I used a bit simplified version -

var j = spreadsheet.getLastRow();
var s = SpreadsheetApp.getActive().getActiveSheet();

for (var i = j; i > 1; i--) {
    if (!s.isRowHiddenByFilter(i)) {
      s.deleteRow(i);
    }
}

The reason I went from bottom to up is that if would have gone from top to bottom then say if row 2,3,4 were visible and I deleted row 2 - then the row 3 becomes row 2 now and is skipped by the loop to delete the row.

Let me know how it goes.

Edit: Refer to the below code for a scenario where your last row is not same as last row of data you're using filter on and you want to delete data only for particular columns

var Avals = spreadsheet.getRange("B1:B").getValues();
var j = Avals.filter(String).length;
var s = SpreadsheetApp.getActive().getActiveSheet();

for (var i = j; i > 1; i--) {
    if (!s.isRowHiddenByFilter(i)) {
      s.getRange('A'+i+':J'+i).deleteCells(SpreadsheetApp.Dimension.ROWS);
      //here A is my starting column, and J is my ending column
    }
}

Upvotes: 1

Cooper
Cooper

Reputation: 64072

Try this:

I don't use filters so I've never done this before but perhaps this will give you a start.

function runOne() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var sr=2;//starting row
  var rg=sh.getRange(sr,1,sh.getLastRow()-sr+1,sh.getLastColumn());
  var vs=rg.getValues();
  var d=0;//deleted row counter
  vs.forEach(function(r,i){
    if(!sh.isRowHiddenByFilter(i+sr-d)) {
      sh.deleteRow(i+sr-d++);
    }
  });
}

isRowHiddenByFilter

deleteRow

Upvotes: 0

Related Questions