CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Mike Steelson
Mike Steelson

Reputation: 15308

How can I build a filter that contains a certain text by gas

I have a script that works fine

function setFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var filterSettings = {};
  filterSettings.range = {sheetId: ss.getSheetByName("mySheet").getSheetId()};
  filterSettings.criteria = {};
  filterSettings['criteria'][4] = {
    'hiddenValues': ["FALSE"]
  };
  var request = {
    "setBasicFilter": {
      "filter": filterSettings
    }
  };
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

with in column E

={"test";arrayformula(if(B2:B="",,not(iserror(find("mike",B2:B)))))}

Upvotes: 0

Views: 897

Answers (2)

Michael Dimmitt
Michael Dimmitt

Reputation: 1054

If you have an already existing filter on the spreadsheet. Grab the existing filter and use the filter criteria builder.

https://developers.google.com/apps-script/reference/spreadsheet/filter-criteria-builder#whenTextContains(String)

const sheet = activeRange.getSheet();
const filter = sheet.getFilter();
const criteria = SpreadsheetApp.newFilterCriteria()
  .whenTextContains('mike')
  .build();

// The "3" below says put the filter in the 4th column of the filter. 
filter.setColumnFilterCriteria(3, criteria);

/* to reset the filter at that column.
  filter.removeColumnFilterCriteria(3)
*/

Upvotes: 0

Iamblichus
Iamblichus

Reputation: 19309

Issue:

You have to define TEXT_CONTAINS under field condition.

Solution:

Replace this:

filterSettings['criteria'][4] = {
  'hiddenValues': ["FALSE"]
};

With this:

filterSettings['criteria'][1] = {
  "condition": {
    "type": "TEXT_CONTAINS",
    "values": [
      {
        "userEnteredValue": "mike"
      }
    ]
  }
};

Notes:

  • criteria is deprecated, consider using filterSpecs instead.
  • I didn't notice your second question before. Take a look at Managing filter views for information on creating filter views.

Reference:

Upvotes: 1

Related Questions