google-sheetsgoogle-apps-scriptfiltertriggers

Is there a google apps script trigger for when a filter changes?

I want to run a function after I change a filter (when data is filtered out or when the filter's sort changes). I have tried the simple triggers onEdit, onSelectionChange, and the installable trigger onChange, but none of them are triggered when I change the filter.

Using onEdit

function onEdit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() !== "Sheet3") return;
  let range = sheet.getRange("E1");
  range.setValue(new Date());
}

Using onChange

In my attept to use onChange, I ran the following script and gave permissions to google to install the trigger. In my testing, the trigger worked when I added data to the filtered table, but did not run when I simply changed the filter.

function setUpTrigger() {
  ScriptApp.newTrigger("onChange")
  .forSpreadsheet("1ryPr3P8d49J-XFiMo1utzy7Qc29xZXyermqnvNfrpK4")
  .onChange()
  .create();
}

function onChange(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() !== "Sheet3") return;
  let range = sheet.getRange("E1");
  range.setValue(new Date());
}

Is there a way to run a method when a filter is changed?

Solution

  • The problem

    After playing around a bit, here's where I believe the issue lies: not within the trigger, but rather within the getActiveSheet method.

    When you run these lines:

    let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (sheet.getName() !== "Sheet3") return;

    , my guess is that sheet.getName() is never 'Sheet3', regardless of where the filtering or sorting event took place.

    Indeed, as described in this other question, the getActiveSheet method does not work properly. As stated in the documentation, it is supposed to

    [get] the active sheet in a spreadsheet. The active sheet in a spreadsheet is the sheet that is being displayed in the spreadsheet UI.

    When editing a certain sheet, the latter does indeed become active. The same is true whenever a row or column is inserted/removed, and when a cell is formatted.

    Nonetheless, data seems to suggest that, when dealing with other types of events, it returns the same sheet any time it is invoked, that may or may not be the active sheet. This includes filtering and sorting of data.

    I tested this myself, with identic result. With the following function being triggered through the onChange event:

    function onChange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  SpreadsheetApp.getUi().alert(sheet.getName());
}

    ; I consistently see this on my screen:

    getActiveSheet returning the wrong sheet

    Whenever I filter or sort the table, even though Sheet2 is active, the method returns Sheet1.

    Failed attempts at solving it

    The aforementioned SO question suggests a possible solution involving adding a custom menu to the UI; in this case that doesn't work. Using SpreadsheetApp.flush() doesn't help either.

    Is there some order in this chaos? In my case, the method always returns the first sheet in the sequence of tabs, regardless of their order of creation. However, in the case of the linked question's OP, it was the middle (second) sheet... So I'm not sure about the consistency here.

    A Google Issue Tracker has been created; if the reader wants to track and/or star it, to make Google prioritise this issue, see References below.

    The workaround

    I can offer an unsatisfying partial workaround. The onChange trigger does not have a range object, so it is tough to work with. It does have, however, an changeType property.

    This property describes the type of event: row/column insertion/deletion, editing, formatting, etc. There isn't a SORT, nor a FILTER option, but both of those are included in the OTHER type. We can work with that so as to trigger your function only when a event takes place that is not one of the specified types:

    function onChange(e) {
  if (e.changeType === 'OTHER') {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
    const range = sheet.getRange("E1");
    range.setValue(new Date());
  }
}

    Two caveats:

    1. it would encompass other types of events - such as protecting a sheet or a range;
    2. it would also run whenever data is filtered or sorted in different sheets.

    Otherwise, the best solution would be, as @Wicket suggested initially, to have a sidebar with custom JavaScript that polls the spreadsheet.

    References