In the previous post, I described the workbookFilterCriteria resource type and explained how to apply Values, Custom, TopItems, BottomItems, TopPercent, and BottomPercent.
Dynamic filtering
Filter rows with the value above the average
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AboveAverage",
"icon": null,
"operator": "And",
"values": null
}
}
The alternative endpoint
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyDynamicFilter
{
"criteria": "AboveAverage"
}
Filter rows with the value below the average
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "BelowAverage",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the next day
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "Tomorrow",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the current day
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "Today",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the previous day
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "Yesterday",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the next week
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "NextWeek",
"icon": null,
"operator": "And",
"values": null
}
}
TFilter rows with the dates that match this week
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "ThisWeek",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the previous week
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "LastWeek",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the next month
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "NextMonth",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match this month
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "ThisMonth",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the previous month
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "LastMonth",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the next quarter
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "NextQuarter",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match this quarter
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "ThisQuarter",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the previous quarter
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "LastQuarter",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the next year
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "NextYear",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match this year
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "ThisYear",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates that match the previous year
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "LastYear",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from the beginning of the current year to the current day
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "YearToDate",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from the first quarter, no matter what year
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodQuarter1",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from the second quarter, no matter what year
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodQuarter2",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from the third quarter, no matter what year
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodQuarter3",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from the fourth quarter, no matter what year
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodQuarter4",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from January
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodJanuary",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from February
Be aware that the correct value for dynamic criteria is with a typo: AllDatesInPeriodFebruray.
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria" : {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodFebruray",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from March
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodMarch",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from April
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodApril",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from May
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodMay",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from June
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodJune",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from July
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodJuly",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from August
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodAugust",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from September
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodSeptember",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from October
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodOctober",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from November
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodNovember",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows with the dates from December
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Dynamic",
"dynamicCriteria": "AllDatesInPeriodDecember",
"icon": null,
"operator": "And",
"values": null
}
}
Filter rows that match the specific cell color
The cell color is defined as a hexadecimal color.
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": "#C6EFCE",
"criterion1": null,
"criterion2": null,
"filterOn": "CellColor",
"dynamicCriteria": "Unknown",
"icon": null,
"operator": "And",
"values": null
}
}
The alternative endpoint
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyCellColorFilter
{
"color": "#C6EFCE"
}
Filter rows that match the specific font color
The font color is defined as a hexadecimal color.
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": "#9C5700",
"criterion1": null,
"criterion2": null,
"filterOn": "FontColor",
"dynamicCriteria": "Unknown",
"icon": null,
"operator": "And",
"values": null
}
}
The alternative endpoint
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyFontColorFilter
{
"color": "#C6EFCE"
}
Filter rows that match the specific icon
First icon in the set
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Icon",
"dynamicCriteria": "Unknown",
"operator": "And",
"values": null,
"icon": {
"index": 0,
"set": "ThreeSymbols"
}
}
}
The last icon in the set
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
"criteria": {
"color": null,
"criterion1": null,
"criterion2": null,
"filterOn": "Icon",
"dynamicCriteria": "Unknown",
"operator": "And",
"values": null,
"icon": {
"index": 2,
"set": "ThreeSymbols"
}
}
}
The alternative endpoint
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyIconFilter
{
"icon": {
"index": 2,
"set": "ThreeSymbols"
}
}
