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 oftext
,number
, orboolean
(must be relevant to the column ID you are filtering)type
is the filter action, which can be any ofequalsAny
,notEqualsAny
,containsAny
,notContainsAny
,isBlank
,isNotBlank
,inRange
,lessThanOrEqual
,lessThan
,greaterThanOrEqual
, orgreaterThan
filter
is a list of filter values, which will all be applied to the filter (soequalsAny
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] .