Automatically refresh filter on change in Google Sheets-CodePudding

I have a formula based on a script in cell G, which provides the hex code of the background colour of cell A. I've applied a filter to column G so specific colours are hidden depending on the hex code that appears in cell G, however the filter doesn't update automatically. I have to go back into the filter and click OK for it to refresh. Is there a script to do this automatically?

CodePudding user response：

As a workaround (semi-automatic) it can be a script that will re-apply a filter for any column(s). Here is example:

function update_filter() {
  var col = 1; // column 'A'
  var filter = SpreadsheetApp.getActiveSheet().getFilter();
  var criteria = filter.getColumnFilterCriteria(col);
  filter.setColumnFilterCriteria(col, criteria);
}

It takes the filter from column 1 (A) and applies it again on the same column.

You can run this script with a button or menu. Or it can be activated via onEdit() trigger.