Filter Model Detail Guide

Details of how to specify filters in your API calls

An important operation with data in Gigasheet is to use 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.

Quickstart: Use a Filter Model from the Gigasheet Web Application

The easiest way to construct a filter model with the correct structure is to use the Gigasheet Web Application to create a filter, save it as a Saved Filter, and then use the saved filter model as a starting point. Once you have created and saved a filter in the Web Application, you obtain the filter model applied to a specific sheet as a Json object. You could use that exact Json as the filterModel parameter in your API call to /file/{handle}/filter, or you could write code to modify the filter model programmatically before using it as a parameter.

To follow this approach:

1. Upload and open the data you want to filter in the Gigasheet web application at app.gigasheet.com
2. Use the Gigasheet filter builder to construct the filter you want (you could also make a filter with placeholder values like "CHANGEME" if you wish to programmatically modify it later)
3. Save the filter with an informative name
4. Call the "List saved filters" endpoint /filter-templates to find the handle of your filter
5. Use the "Get filter model for saved filter" endpoint /filter-templates/{filter-handle}/on-sheet/{handle} to obtain a filter model of that filter as applied to the sheet you want to filter
6. Take the results of step 5 and use it as the filterModel parameter in the call to /file/{handle}/filter to obtain filtered results from the sheet
7. Optionally, modify the output of step 5 programmatically before step 6 to replace your placeholder values with real values or whatever your needs might be

You may want to complete steps 1-5 once and then use variations on step 7 in your code to reuse the same filter model with different filter values.

Alternatively, if you want to understand the filter model structure in more detail or you want to try assembling the filter model from scratch, read the rest of this page.

Filter Model Structure

In the /file/{handle}/filter endpoint, 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.

JSON

{
  "_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:

JSON

{
  "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")

Filter Model Example

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)

JSON

{
  "_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.

Additional Help

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