Automatically Refresh A Filter When a Specific Cell is Changed

Question

I have a simple formula in column A that returns True or False against rows depending on the value of cell I3. At the moment, each time I change the value of cell I3 I have to manually go to the filter and select "OK" to refresh it. Can this be run be a script that is triggered by any edit to cell I3?

https://docs.google.com/spreadsheets/d/1H2lkC-rQKjdL_7A8t0_mNNzk4OJr0iQi0wsja0rQ-7w/edit#gid=893032115

Answer 1

Yes, you can automate the filter if cell I3 is edited. I'm not an expert in filters (particularly scripted), so I created and re-used a macro.

1. Create a macro that selects the filter and "selects Yes". Rename the macro to a meaningful name.
2. Create a onEdit(e) script. Evaluate whether cell I3 was edited; if yes, run the macro

---

function onEdit(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetname = "P&L";
  var sheet = ss.getSheetByName(sheetname);
  var erow = e.range.getRow();
  // Logger.log("DEBUG: row = "+erow);
  var ecolumn = e.range.getColumn();
  // Logger.log("DEBUG: column = "+ecolumn);
  if (erow == 3 || ecolumn == 8 ){
    // there is a match so the cell is I3
    // Logger.log("DEBUG: the cell is I3");
    updatefilter();
  }
  else
  {
    // there is no match so the cell is NOT I3
    //Logger.log("DEBUG: the cell is not I3");
  }

}

function updatefilter() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('V3').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['', 'No'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(22, criteria);
};