Automatically Refresh A Filter When a Specific Cell is Changed

Viewed 6221
2 Answers

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);
};

You can do that from the script something like this,

function setFilter() {
  var ss = SpreadsheetApp.getActive();
  var tab = ss.getSheetByName("tabName")
  var filter = {};
  filter.range = {
    sheetId: tab.getSheetId()
  };

  filter.criteria = {};
  // Which column to add the filter to
  var columnIndex = 2;
  filter['criteria'][columnIndex] = {
    'hiddenValues': ["No"]
  };
  var request = {
    "setBasicFilter": {
      "filter": filter
    }
  };
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

Code not tested Add a button to call the function.

Related