I am stuck figuring out how to trigger onEdit() function on cell change. It should be changing the filter criteria to hide empty rows, on edit of C2 or C4 cell. (I am hiding rows based on cell value 0/1, if 1 show rows). I already have something like this:
function onEdit(e) {
const range = e.range;
const sheet = range.getSheet();
const filter = sheet.getFilter();
const fRange = filter.getRange();
var criteria = filter.newFilterCriteria()
setHiddenValues(['0']).build();
fRange.createFilter().setColumnFilterCriteria(2, criteria);
}
It doesn't work. Do you have any idea how to solve this code?
CodePudding user response：
Suggestion
If I have understood your question clearly, your goals after triggering the onEdit function on column B (number 2) are:
- Hide rows with
0 - Filter only rows with
1
[UPDATED]
Tweaked Script
function onEdit(e) {
if(e.range.getColumn() != 3) return; //Skip script when Column B is NOT selected
if(e.range.getA1Notation() == "C2" || e.range.getA1Notation() == "C4"){
const range = e.range;
const sheet = range.getSheet();
const filter = sheet.getFilter();
const fRange = filter.getRange();
var criteria = SpreadsheetApp.newFilterCriteria().whenNumberEqualTo(1);
filter.remove(); //Needs to remove the original filter as you can't create a filter (with new criteria) in a sheet that already has a filter
fRange.createFilter().setColumnFilterCriteria(2, criteria);
}; //Run the script only when C2 or C4 is selected
}
Version 2
using
setHiddenValues()
function onEdit(e) {
if(e.range.getColumn() != 3) return; //Skip script when Column B is NOT selected
if(e.range.getA1Notation() == "C2" || e.range.getA1Notation() == "C4"){
const range = e.range;
const sheet = range.getSheet();
const filter = sheet.getFilter();
const fRange = filter.getRange();
var criteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(["0"]);
filter.remove(); //Needs to remove the original filter as you can't create a filter (with new criteria) in a sheet that already has a filter
fRange.createFilter().setColumnFilterCriteria(2, criteria);
}; //Run the script only when C2 or C4 is selected
}
