CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsfilterfiltering
user7935276
user7935276

Reputation: 239

How to auto select range from a filter without having to manually enter it?

I am wondering if there is a way to auto select the cell range based on selected filter.

Example: Sample

Currently, my workout is to manually enter the cell so I could move on with writing the codes. I hope I am making sense above.

---- Code----

function ColHtoActive() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Current');  
  var crange = sheet.getRange('A7:I7350').activate();                                
  var currentCell = sheet.setCurrentCell(sheet.getRange('H7'); 
  var hSfilter = sheet.getRange('A7:I7350').createFilter(); 
  var ccC1 = sheet.getRange('H7').activate();
  var cCriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues('Inactive']).build(); 

  sheet.getFilter().setColumnFilterCriteria(8, cCriteria);    
}

function copycolA() {
  var ss = SpreadsheetApp.getActive().getSheetByName('A');
  ss.getRange('A2307').activate();
  ss.getRange('A2307:A7155').copyTo(
      ss.getActiveRange(),
      SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
      false);
}

Upvotes: 5

Views: 3146

Answers (2)

TheMaster
TheMaster

Reputation: 50574

You can get the filtered range dimensions from getFilter().getRange(). This will copy all the filtered range:

function copycolA() {
  var sourceSheet = SpreadsheetApp.getActive().getSheetByName('Current');
  var targetSheet = SpreadsheetApp.getActive().getSheetByName('A');
  var sourceRange = sourceSheet.getFilter().getRange();
  sourceRange.copyTo(
    targetSheet.getRange('A1'),
    SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
    false);
}

To read:

Note that .getValues() or other script operations will NOT get the filtered only, but all of the values.

Upvotes: 7

xyz333
xyz333

Reputation: 768

Possibly 

function myFilter() {
  const ss      = SpreadsheetApp.getActiveSpreadsheet(),
        sht     = ss.getSheetByName("Current"),
        rng     = sht.getDataRange(),
        rawData = rng.getDisplayValues();

  let filterValues = ["Inactive"],
        col = 8, // column "H".
        out = rawData.filter(dataFilter);
        out = [rawData[0], ...out]; //Add headers to filtered data 
  
  function dataFilter(arr) {
    return filterValues.includes(arr[col-1]);
  }

  const osh=SpreadsheetApp.getActive().getSheetByName("A"); 
  osh.clear();
  osh.getRange(1,1,out.length,out[0].length).setValues(out);
}

Upvotes: 0

Related Questions