Use Google App Script to insert new Filter View containing Custom Formula

Please see example picture below.

I would like to use Google Sheets script editor to add a Filter view on cell B3, that filters on a custom formula =(B4=$B$1).

The plan is to add a onEdit function that checks whether the value in cell B1 has changed, and if so reset the filter view in cell B3.

This is breaking my brain! I can't seem to find a way to auto update/refresh the filter once my value in B1 has changed.

My code currently looks like this, but this doesn't re-add the custom formula. It just keeps showing me all the non-empty rows.

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetname = "Lactation";
  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 == 1 & ecolumn == 2 ){
    // there is a match so the cell is B2
    //Logger.log("DEBUG: the cell is B2");
    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('B3').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['', 'No'])
  .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(2, criteria);
};

• You want to modify the basic filter of the cells "B3:B" when the cell "B1" on the sheet Lactation is edited.
  • In this case, you want to use the OnEdit event trigger.
• You want to use =(B4=$B$1), which is the custom formula, as the filter condition.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

• You can take advantage of the event object as e of onEdit(e).
• In order to use the custom function for the basic filter, you can use whenFormulaSatisfied.

Modified script:

In order to run the script, please edit the cell B1 on the sheet Lactation.

function onEdit(e){
  var sheetName = "Lactation";
  var editCell = "B1";

  var range = e.range;
  var sheet = range.getSheet();
  if (range.getA1Notation() == editCell && sheet.getSheetName() == sheetName) {
    updatefilter(sheet);
  } else {
    // do something
  }
}

function updatefilter(sheet) {
  sheet.getRange('B3').activate();
  var criteria = SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied("=(B4=$B$1)").build();
  var filter = sheet.getFilter();
  if (!filter) {
    filter = sheet.getRange("B3:B").createFilter();
  }
  filter.setColumnFilterCriteria(2, criteria);
};

• In this script, when the cell "B1" on the sheet of Lactation is edited, the function updatefilter is run by the OnEdit event trigger. And when the basic filter is existing, it is updated. When no filter is existing, the filter is set as new filter. The range of the filter is "B3:B". The custom function of =(B4=$B$1) is used as the criteria.

Note:

• In above script, the simple trigger can be used. But if you use the methods for requiring to authorize, when an error occurs, please try to use Installable Triggers.

References:

• Simple Triggers
• Installable Triggers
• Event Objects
• whenFormulaSatisfied(formula)

If I misunderstood your question and this was not the direction you want, I apologize.