0

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?

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
Husain Alhamali
  • 823
  • 4
  • 17
  • 32
  • 1
    Filter is different from filterview. The latter can only be applied in the browser for a single user only. – TheMaster Feb 04 '20 at 09:04
  • Thanks for ur respone, yes I'm aware of that, the script is being run by the user only, so there should be an option to apply the filter view once it's triggered by the user. I'm looking for that only. – Husain Alhamali Feb 04 '20 at 09:25

1 Answers1

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.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33