Google Apps Script - Need help Filtering with OR condition

Question

I am trying to write a simple apps script function that filters spreadsheet rows based on a search term.

function sheetFind() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var descToMatch = sheet.getRange(1, 12).getValue(); 
    var range = sheet.getDataRange();
    var filtercriteria = SpreadsheetApp.newFilterCriteria().whenTextContains(descToMatch);  
    var filter = range.getFilter() || range.createFilter();// getFilter already available or create  a new one
    filter.setColumnFilterCriteria(2, filtercriteria); //set the criteria against Col2 (B column)
    filter.setColumnFilterCriteria(3, filtercriteria); //set the criteria against Col3 (C column)
    filter.setColumnFilterCriteria(5, filtercriteria); //set the criteria against Col5 (E column)
}

The problem is that the above applies the filter criteria to each column and I get no results unless the term is in each of those columns in a row. What I want is this to not be "AND", but rather an "OR" search. So I would like to see results where the search term exists in any one of the three columns.

Does someone know how to do that?

Thank you!

pnuts pnuts · Accepted Answer · 2018-12-19T17:43:54

First set a flag in a spare column according to whether or not your search term occurs in any one of the three columns (row by row), then filter on that column.

Google Apps Script - Need help Filtering with OR condition

1 Answers