Filter Model Detail Guide

An important operation with data in Gigasheet is to create filters, which can get complex. This page describes how to specify filters in your API calls using a Filter Model.

Most likely, you will use filters with the /file/{handle}/filter API endpoint to return rows from a sheet matching your filters. The filters are specified in the filterModel parameter, which takes a Json object that is your Filter Model. The structure of the Filter Model object is described below.

{
  "_cnf_":
  [
    [
      {...filter1...},  # ( filter1 OR filter2 ) 
      {...filter2...},
      ...
    ],
    [
      {...filter3...}  # AND filter3
    ],
    ...
  ]
}  # together: ( ( filter1 OR filter2 ) AND filter3 )

There must be an outer key _cnf_, which stands for Conjunctive Normal Form. That key must contain a list of lists. The filters within the inner lists will be combined with OR, and those groups of OR will be combined together with AND in the outer list. This combination of AND an OR is illustrated above in pseudo-Json with comments (note that the above example is not valid Json).

An individual filter entry also has a required structure, which looks like this:

{
  "colId":"A",
  "isCaseSensitive":false,
  "filterType":"text",
  "type":"equalsAny",
  "filter":["match this"]
}

Where:

• colId is the column ID, which you can obtain from /dataset/{handle}/columns
• isCaseSensitive is a boolean for whether to make it case sensitive (applicable to text filters, otherwise ignored)
• filterType is the data type of your filter, which can be any of text, number, or boolean (must be relevant to the column ID you are filtering)
• type is the filter action, which can be any of equalsAny, notEqualsAny, containsAny, notContainsAny, isBlank, isNotBlank, inRange, lessThanOrEqual, lessThan, greaterThanOrEqual, or greaterThan
• filter is a list of filter values, which will all be applied to the filter (so equalsAny with filter value ["foo", "bar"] will match rows where that column is any of "foo" or "bar")

Here is a complete valid filter model that matches the following logic:
( ( { B does not contain any of [foo, bar] } ) OR {F equals baz} ) AND {H > 5}

{
  "_cnf_": [
    [
      {
        "colId": "B",
        "filter": [
          "foo",
          "bar"
        ],
        "filterType": "text",
        "isCaseSensitive": false,
        "type": "notContainsAny"
      },
      {
        "colId": "F",
        "filter": [
          "baz"
        ],
        "filterType": "text",
        "isCaseSensitive": false,
        "type": "equalsAny"
      }
    ],
    [
      {
        "colId": "H",
        "filter": "5",
        "filterType": "number",
        "isCaseSensitive": false,
        "type": "greaterThan"
      }
    ]
  ]
}

You may combine a very large number of filters by adding additional entries in the inner lists. However, you must always follow the same structure of OR and AND blocks, it is not valid to have a different levels of nesting other than what is described on this page.

For additional help constructing filter models, please contact [email protected] .