How to Enable FilterView after creating it in Sheets API

Question

simply, I'm using the following piece of code to create a filter view using Advanced Google Services

function applyFilter(sheetName, sheetId, colNo, startRowIndex, criteria) {

  delFilterViews(sheetName)
  var ss = SpreadsheetApp.openById("1cnYgUIdJZShvywco00xttiS293t_H9P7RTsBcOvJbBM");
  var ssId = ss.getId();
  var dataSheet = ss.getSheetByName("Follow Up Sheet");
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();

  var key = colNo;
  var filterSettings = {
  "title": criteria,
  "range": {
  "sheetId": sheetId,
  "startRowIndex": startRowIndex,
  "endRowIndex": lastRow,
  "startColumnIndex": 0,
  "endColumnIndex": lastColumn
  },
    "criteria": {
    }
  };

  filterSettings.criteria[colNo] = {
       "condition": {
         "type": "TEXT_CONTAINS",
         "values": [
           {
             "userEnteredValue": criteria
           }
         ]
       }
     }

   var requests = [{
    "addFilterView": {
    "filter": filterSettings
  }
  }];
  }

The code is working perfectly so far, but it's only creating the FilterView, what I'm looking for is how to -programmatically- apply this FilterView to the user?

Answer 1

Once you create the filter view - you retrieve its ID and append it with fvid=... to the URL of the spreadsheet, as done here:

https://docs.google.com/spreadsheets/d/XXXXXX/edit#gid=0&fvid=YYYYYY

To apply the filterview to the user, the user needs to open the spreadsheet with the URL including the filterview ID - this the only way.

Now, if you want to create and apply the filterview dynamically, you can transform your Apps Script code into a Web App and redirect the user to the dynamically created URL.

In other words: Instead of letting the user open the spreadsheet from his drive, let him do it by opening the WebApp URL and being redirected to the correct filterview.

If you deploy the WebApp as "User accessing the App" you can even dynamically create different filterviews, depending on which user opens the WebApp URL.