FilterOn and DynamicCriteria allowed values for MS Graph Excel Filter Criteria-CodePudding

I am looking for the allowed values for the filterOn and dynamicCriteria properties shown in the filter criteria described at

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"
  }
}

CodePudding user response：

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

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
    }
}

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