How can I set a filter view in a sheets script?

Question

I have defined several filter views and would like to apply one of those predefined filter views inside of a Google sheets script.

NOTE: Filter Views are buried in menus and not all sheet users will know where to find them. Conversely, users making changes to filters and sorting change the view for all users unless Filter Views are used. Selecting Filter Views by prominent buttons solves these issues well for users of all skill levels.

Answer 1

This is a long standing feature request. However, there is no code here in your question to review.

https://code.google.com/p/google-apps-script-issues/issues/detail?id=524

---

Update

Three additional services have been added to Apps Script related to filters: Class Filter and Range.createFilter, Sheet.getFilter.

These will not allow you to access or control the saved filter views, you might be able to script functionality similar. However, your options for UI buttons would either be image script runners or instantiate a side-bar app. Either of which might not be visible. Equally they affect the sheet for all viewers.

---

Further Update

As noted in an answer to a newer question, FilterViews can now be created programmatically. There are caveats noted in that answer that concern protected ranges.

This still doesn't address the OP request to activate specific FilterViews. They can be created, updated or deleted but even using the Sheets Advanced API, they cannot be activated programmatically by the current viewer.

Answer 2

In below example, a simple criteria TRUE/FALSE is used to filter sheet's view, i.e. if the value in cell is FALSE, the row is hidden. Thus you have two functions, setFilter and clearFilter, apparently. In the most primitive way, this would look something like this:

function setFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var filterSettings = {};

  // The range of data on which you want to apply the filter.
  // optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex
  filterSettings.range = {
    sheetId: ss.getSheetByName("sheetName").getSheetId() // provide your sheetname to which you want to apply filter.
  };

  // Criteria for showing/hiding rows in a filter
  // https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria
  filterSettings.criteria = {};
  var columnIndex = 9; // column that defines criteria [A = 0]
  filterSettings['criteria'][columnIndex] = {
    'hiddenValues': ["FALSE"]
  };

  var request = {
    "setBasicFilter": {
      "filter": filterSettings
    }
  };
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

function clearFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheetId = ss.getSheetByName("sheetName").getSheetId();
  var requests = [{
    "clearBasicFilter": {
      "sheetId": sheetId
    }
  }];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

Answer 3

What you want is to make it easy for your users to change filtered views.

An easy way achieve that is to use regular hyperlinks in frozen rows or columns:

This works because filtered views are activated just by an additional URL parameter named fvid:

Pros:

• works across sheets and tabs.
• easy to implement.
• no coding required.

Cons:

• filtered views need to be manually updated when rows exceed the range set in them because they do not accept open ranges...
• it'not possible to use images as they do not accept hyperlinks
• you need to create an "All rows" filtered view as removing the fvid parameter does not live update like adding or changing it.