FilterOn and DynamicCriteria allowed values for MS Graph Excel Filter Criteria

Question

I'm searching for the permitted values for the filterOn and dynamicCriteria attributes displayed in the Filter: apply filter criteria. I am unable to determine what are valid numbers because of how generic the sample values provided in the manual are.

POST https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
Content-type: application/json

{
  "criteria": {
    "criterion1": "criterion1-value",
    "criterion2": "criterion2-value",
    "color": "color-value",
    "operator": {
    },
    "icon": {
      "set": "set-value",
      "index": 99
    },
    "dynamicCriteria": "dynamicCriteria-value",
    "values": {
    },
    "filterOn": "filterOn-value"
  }
}

Answer 1

The filtering criteria applied to a column are not documented but it's very similar to Excel Javascript API.

https://learn.microsoft.com/en-us/javascript/api/excel/excel.filtercriteria?view=excel-js-preview

1. filterOn property

Determines whether the values should stay visible.

Possible values are: BottomItems, BottomPercent, CellColor, Dynamic, FontColor, Values, TopItems, TopPercent, Icon, Custom

Example of the request body for Values. Lets say you have the following table and you want to select values 1,3 and 5 from Column1

POST /v1.0/me/drive/items/{item_id}/workbook/tables/{table_name}/columns/1/filter

{
  "criteria": {
    "color": null,
    "criterion1": null,
    "criterion2": null,
    "filterOn": "Values",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": [
        "1",
        "3",
        "5"
    ]
  }
}

Display values greater than 2

{
    "criteria": {
        "color": null,
        "criterion1": ">2",
        "criterion2": null,
        "filterOn": "Custom",
        "dynamicCriteria": "Unknown",
        "icon": null,
        "operator": "Or",
        "values": null
    }
}

1. dynamicCriteria property

Used with Dynamic filtering.

Possible values are: Unknown, AboveAverage, AllDatesInPeriodApril, AllDatesInPeriodAugust, AllDatesInPeriodDecember, AllDatesInPeriodFebruray, AllDatesInPeriodJanuary, AllDatesInPeriodJuly, AllDatesInPeriodJune, AllDatesInPeriodMarch, AllDatesInPeriodMay, AllDatesInPeriodNovember, AllDatesInPeriodOctober, AllDatesInPeriodQuarter1, AllDatesInPeriodQuarter2, AllDatesInPeriodQuarter3, AllDatesInPeriodQuarter4, AllDatesInPeriodSeptember, BelowAverage, LastMonth, LastQuarter, LastWeek, LastYear, NextMonth, NextQuarter, NextWeek, NextYear, ThisMonth, ThisQuarter, ThisWeek, ThisYear, Today, Tomorrow, YearToDate, Yesterday

Example how to filter values above the average. Set filterOn to Dynamic and dynamicCriteria to AboveAverage.

{
    "criteria": {
        "color": null,
        "criterion1": null,
        "criterion2": null,
        "filterOn": "Dynamic",
        "dynamicCriteria": "AboveAverage",
        "icon": null,
        "operator": "And",
        "values": null
    }
}

Documentation:

Excel filterCriteria