Create a filter from a user selected data validation list

Question

I am trying to use Google apps script to create a filter across row 1 on 'Data' sheet tab. The filter is taken from a data validation list on 'Dashboard' sheet - cell 'I7', this is where the user changes the filter options.

e.g. if user selects 'Shopfloor' on drop down menu on Dashboard sheet. then this will change the filter on Data sheet for column E to show only Shopfloor staff.

I am almost there but am getting an error with setColumnFilterCriteria. Any help is much appreciated.

/* SET FOLLOWING VARIABLES */
var dropSS           = "Dashboard";     //Name of the sheet which dropdown list to be created
var primaryDropRange = "I7";            //Range which primary dropdown set

//CREATE PRIMARY DROPDOWN LIST
function onOpen(e) {
  /* SET FOLLOWING VARIABLES */
  var dataSS           = "Lists";         //Name of the sheet that contain data for dropdown lists
  var primaryDataRange = "E1:E5";         //Data range for primary dropdown
  var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();
  var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);
  var BA = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();
  primaryDropRange.setDataValidation(BA);
}

function filterBA(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var selBA = ss.getSheetByName(dropSS).getRange('I7').getValue();
  var sheet = ss.getSheetByName('Data');
  var filter = sheet.getFilter();
  //var criteria = SpreadsheetApp.newFilterCriteria().setVisibleValues(selBA).build();
  if(sheet.getFilter() != null) {                                                                   // Check if filter already exists and remove filter if true
     sheet.getFilter().remove();
  }
  var FilterRange = sheet.getDataRange().createFilter().setColumnFilterCriteria(4, selBA);
}

Link to my sample spreadsheet - https://docs.google.com/spreadsheets/d/191S0DNlxczKKi2SJuc45CXxDXZxXH4xNpjgEJxf46X8/edit?usp=sharing

NaziA NaziA · Accepted Answer · 2021-04-15T15:54:27

Your filterBA function should look like this:

function filterBA(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var selBA = ss.getSheetByName(dropSS).getRange('I7').getValue();
  var sheet = ss.getSheetByName('Data');

  // Check if filter already exists and remove filter if true
  if(sheet.getFilter() != null) {  
     sheet.getFilter().remove();
  }

  // Create criteria
  var newCriteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(selBA).build();
  // The 1-indexed position of the column
  sheet.getDataRange().createFilter().setColumnFilterCriteria(5, newCriteria);      
}

Your issues:

criteria parameter accepts type FilterCriteria variable, not the exact value you want to filter with.
column parameter is 1-indexed, not 0. All methods for sheets that accepts columns are 1-indexed.

Output:

Reference:

setColumnFilterCriteria

Create a filter from a user selected data validation list

2 Answers

Your issues:

Output:

Reference: