How to Create Further Filters using App Script-CodePudding

I have been trying to filtering the columns using Google Apps Script but i am unable to get that how the filter will work for second criteria.

I want to create a filter which will work for entire data validation that is available in the attached sheet.

If i selected the position from cell C2 Head Of Product Design then position column will be shown rows with Head Of Product Design and then i select the Stage Name from cell D2 Profile review. then two filter will be created 1 for C2 and other for D2 and same goes for all.

Your help will be much appreciated.

However first onEdit is working but second is not working i do not know why. Then i need to create filters ahead. But i am stuck on second 1.

Your help will be appreciated.

Sheet

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

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

CodePudding user response：

In your situation, how about the following modification?

Modified script:

function onEdit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const [c2, d2] = sheet1.getRange('C2:D2').getValues()[0];
  const range = sheet1.getRange("A5:T");
  const filter = sheet1.getFilter();
  if (filter) {
    filter.remove();
  }
  range.createFilter()
  .setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenTextContains(c2).build())
  .setColumnFilterCriteria(4, SpreadsheetApp.newFilterCriteria().whenTextContains(d2).build());
}

In this modified script, your onEdit and onEdit2 are merged.

Added:

From your following new question,

I have multiple sheets with different data but each sheet has same data validation cells and Columns where filter will apply. So your above code is being used in Sheet1, then i add same code for Sheet2 But Sheet2 code is not working. I did not changed the code name that is onEdit for both sheets

When the Sheet1 is edited script should work for Sheet1 if Sheet2 is added scripts should work for Sheet2

In this case, how about the following sample script?

Sample script:

function onEdit() {
  const sheets = ["Sheet1", "Sheet2"];
  const sheet = SpreadsheetApp.getActiveSheet();
  if (!sheets.includes(sheet.getSheetName())) return;
  const [c2, d2] = sheet.getRange('C2:D2').getValues()[0];
  const range = sheet.getRange("A5:T");
  const filter = sheet.getFilter();
  if (filter) {
    filter.remove();
  }
  range.createFilter()
  .setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenTextContains(c2).build())
  .setColumnFilterCriteria(4, SpreadsheetApp.newFilterCriteria().whenTextContains(d2).build());
}

When this script is run, when the active sheet is Sheet1 and Sheet2, the script is run.
From OP's previous question, I understood that OP wants to achieve this without using the event object. So I proposed the above sample scripts.