enter image description here

I want to create a filter area in A1 and A2 cells so that when I put drivers' name on these cells, the table (A:C) should be filtered according to the value entered in A1 and A2. Can someone explain me how to write Apps Script to perform such function?

Thank you in advance!

CodePudding user response：

It can be something like this probably:

function onEdit(e) {
  if (e.range.columnStart > 1) return;
  if (e.range.rowStart > 2) return;
  filter_table(e.value);
}

function filter_table(name) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(3,1,sheet.getLastRow(),3);
  var data = range.getValues(); 
  var hidden_values = data.map(x => x[0]).filter(x => x != name);

  var filter = sheet.getFilter();
  if (filter !== null) filter.remove();

  range.createFilter();
  var criteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(hidden_values).build();
  sheet.getFilter().setColumnFilterCriteria(1, criteria);
}

It filteres the range A3:C every time you edit cell A1 or cell A2.

But I'm not sure though what do you want to get when the two cells contain different names.

CodePudding user response：

Driver Data

function MyFunction() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName('Sheet0');
  const osh = ss.getSheetByName('Sheet1');
  osh.clearContents();
  const sr = 3;
  const dA = sh.getRange(1,1,2).getValues().flat();
  const name = dA[0]   ' '   dA[1];
  const nameColumn = 1;
  const vo = sh.getRange(sr,1,sh.getLastRow() - sr   1, sh.getLastColumn()).getValues().filter(r => r[nameColumn -1] == name);
  osh.getRange(1,1,vo.length,vo[0].length).setValues(vo);
}