The FILTER() function is used to fetch data from a data store and filter data from that data store. The data for the Datastore is fetched from different sources using a BINDAPI or a LOOKUP function. However, when you apply the filter it is applied to the Datastore and not to any of the sources like sheets or APIs. At times APIs may not support the filters that you wish to apply to the data you need. You can use the same Datastore and use different filters as per your scenario. Moreover, using the Filter function to fetch filtered data from Datastore can also save multiple API calls to the Databases. The FILTER function is ideally used with the list controls like TableGrid and Detail view.
FILTER([datastore.columnname1, datastore.columnname2,…], Filter or Condition, Sort column,Order)
- datastore: is the name of the datastore created earlier to fetch data from sheet or API.
Columnname: is the column whose data is to be fetched.
- In case you need to fetch data from more than one column, then add the list of columns within the square [ ] brackets separated by comma as [datastore.columnname1,datastore.columnname2]
Filter: is the filter format to fetch data from the column. There are three components as Columns to be filtered, filter operator & filter criteria. In case you need to state multiple filters you can use AND (for all filter criteria match) or OR (for any one of the filter criteria match).
- The condition will be taken as datastore.columnname1=""
The parameters mentioned below are optional:
- Sorting: is used to sort the result set on the basis of the column specified.
- Order: is the order in which you want to sort the data fetched from the sheet. It can have value either ASC or DESC which needs to be put within double quotes “”.
The function returns the list of rows based on the filter criteria with the column details as specified using the filters.
To further understand how to use the FILTER() refer to this article here.