onEdit script depending on a cell from another tab in the same sheet

Question

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

}

}

Answer 1

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