Trying to create a Simple Filter App Script-CodePudding

I have been trying to create a simple filter using Google Sheets App Script. That if cell C2 values contains `RAM' then Col"2" should hide all rows except 'RAM'. But its not working.

I have created a data validation in cell C2 which changes the value. Any help will be appreciated.

 function create_filter(){
        const  ss = SpreadsheetApp.getActiveSpreadsheet();
        const sheet1 = ss.getSheetByName("Filter_Sheet");
        const  range = sheet1.getRange("A5:T");
        const  filter = range.createFilter();
    
        const Filter_Criteria1 = Sheet1.range('C2').getActiveCell;
        const coll1 = 2
        
        const  add_filter =  filter.setColumnFilterCriteria(coll1,Filter_Criteria1);
    }

another try:

function create_filter(){
        const  ss = SpreadsheetApp.getActiveSpreadsheet();
        const sheet1 = ss.getSheetByName("Filter_Sheet");
        const  range = sheet1.getRange("A4:T");
        const  filter = range.createFilter();
        var range2 = SpreadsheetApp.getActiveSheet().getRange('D2');
        var range3 sheet1.getRange('C2').activate();
 
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues([cell != range2 ])
  .build();
spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(3, criteria);
};

CodePudding user response：

I believe your goal is as follows.

You want to create the basic filter that when a text of RAM is contained to the cell value, you want to show the rows.

In this case, how about the following modification?

Modified script:

function create_filter() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Filter_Sheet");
  const range = sheet1.getRange("A5:T");
  const filter = range.createFilter();
  filter.setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenTextContains("RAM").build());
}

In your 2 scripts, 2 ranges of const range = sheet1.getRange("A5:T") and const range = sheet1.getRange("A4:T") are used. In this modification, const range = sheet1.getRange("A5:T") is used. About this, please modify it for your actual situation.
When this script is run, the rows that RAM is contained at the value of column "B" are shown.

References:

Added 1:

From the following replying in the comment and your script,

I have created a data validation in cell C2 there are 10 to 11 different string in that validation including RAM. So i want to create a on edit function that whenever Cell C2 string changes script should RUN and Column B will show exact value that is available in cell C2

I thought that the sheet name is "Filter_Sheet" and the data validation is put to the cell "C2". But, when I saw your Spreadsheet, the sheet name is "Sheet1" and the data validation is put to the cell "C1". So the following sample script uses your sample Spreadsheet. When you change the sheet name, please modify Sheet1 to others.

function onEdit(e) {
  const offset = 0; // When the data validation is cell "C1" and "C2", please use 0 and 1, respectively.
  const {range, value} = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet1" || range.getA1Notation() != "C"   (1   offset)) return;
  const r = sheet.getRange(`A${4   offset}:T`);
  const filter = sheet.getFilter();
  if (filter) {
    filter.remove();
  }
  r.createFilter().setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenTextContains(value).build());
}

From your following replying,

exactly the details are different because it is just sample sheet i can modify the code later when apply to real sheet
- Please modify above script for your actual situation.

Added 2:

From your following replying,

but i am unable to edit this code. It is difficult for me to understand it, const value = sheet1.getRange('C2').getValue(); if this can be used then i can change the cell reference.

You want to run the script when the cell "C1" is edited. From your additional question, I understood like this. So, I proposed the modified script for using the event object of OnEdit simple trigger. But, when you don't want to use the event object, how about the following script?

function onEdit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const value = sheet1.getRange('C1').getValue();
  const range = sheet1.getRange("A4:T");
  const filter = sheet1.getFilter();
  if (filter) {
    filter.remove();
  }
  range.createFilter().setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenTextContains(value).build());
}