I am trying to create a script using an onEdit feature so that it activates every time a specific cell is altered.
The problem is that this specific cell is not in the same tab where the script needs to run, so what I have so far is not working.
Here exactly what I need:
Every time cell K4 (which contains a checkbox) in tab named "Filter" is checked or unchecked, I want the existing filter in K:K in Overview tab to be updated.
When run manually, the filter updates perfectly, but when I add the onEdit feature based on tab "Filter", it does not work, so I assume the problem is there.
I would really appreciate your help.
Thanks in advance!
function ResetFilter() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Overview");
var criteria = SpreadsheetApp.newFilterCriteria()
.setHiddenValues(['Archived', 'Cancelled', 'Excluded'])
.build();
var filter = sheet.getFilter() || sheet.getRange("K:K").createFilter();
filter.setColumnFilterCriteria(11, criteria);
}
function onEdit(e) {
var sheet = SpreadsheetApp.getActive().getSheetByName("Filter")
if(e.range.getA1Notation() == "K4"){
ResetFilter();
}
}
>Solution :
The problem is not in the edition in another tab, that wouldn’t be a problem. What I suggest is you change the name of onEdit(e) to another thing – onEditing(e), whenEdit(e) or whatever you want, and set an installable trigger. There are several tutorials on that, but as little instructions:
- Click on the clock on the left of the image (highlighted in yellow)
- Then "Add Trigger" on the left bottom
- Choose the name of your function (on the top)
- Change the option at the bottom to "On edit"
This will run the script with further permissions and will probably work!
PS: see Documentation