CODEWITHSUNDEEP
google-apps-scriptfilter
Newbie
Newbie

Reputation: 281

How to filter multiple criteria in a single column with Google Apps Script?

With Google Apps Script, I like to filter multiple criteria in a single column on a google sheet. In the following simple example, I like to filter "Apple" and "blank" in Column A, in order to get the same filtering results as we do manually in the picture example below. I couldn't figure it out in Google Apps Script. My actual application deals with a huge number of data sets in a google sheet (100k rows). So I would appreciate any help for an efficient method! Thank you!

function test() {

  var data = [['Fruits', 'Price'],
  ['Apple', 10],
  ['Orange', 5],
  ['', 30],
  ['Mango', 7]];

  var ss = SpreadsheetApp.getActiveSheet()
  ss.getRange('A1:B5').setValues(data);

  if (ss.getFilter() != null) {   //Remove existing fitler before creating a new filter
    ss.getFilter().remove();
  }
  var filter = ss.getRange('A1:B').createFilter();

  criteria = ['Apple', ''];
  filterCriteria = SpreadsheetApp.newFilterCriteria().whenNumberEqualToAny(criteria);
  // filterCriteria = SpreadsheetApp.newFilterCriteria().whenTextEqualToAny(criteria);  //didn't work
  filter.setColumnFilterCriteria(1, filterCriteria);
}

Manual Filter Example

Upvotes: 0

Views: 2168

Answers (1)

Nikko J.
Nikko J.

Reputation: 5543

Try this:

filterCriteria = SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied("=REGEXMATCH(A2:A5,  \"(Apple|^$)\" )")

This will use a regular expression that will check if the row contains Apple or an empty cell.

Note: If you want to add more criteria, just add inside the open and close parenthesis () and separate it with |. Example (Apple|Mango|^$)

Output:

enter image description here

Reference

Upvotes: 1

Related Questions