Filter workbook table's column with the Graph API | Part 1

09/08/2023
WorkbookFilteringExcelGraph API

With the Microsoft Graph, you can read and modify Excel workbooks stored in OneDrive for Business, SharePoint site or Group drive.

You can access a set of Excel objects (such as Table, Range, or Chart). For a table object, you can retrieve a list of columns. For each table's column, you can manage the filtering of the column.

When you try to apply the filtering criteria with the Graph API for the first time, you will find out that the official documenation provides a very basic example. So that you don't have to spend time googling, you can go through this blog post and check how to filter a table.

The filtering criteria applied to the table's column are represented by the workbookFilterCriteria resource type.

Filter criteria

The workbookFilterCriteria has the following properties

Property Description
color The HTML color string used to filter cells. Used with cellColor and fontColor filtering
criterion1 The first criterion used to filter data. Used as an operator in the case of custom filtering. Used as a number in the case of top/bottom items/percents filtering
criterion2 The second criterion used to filter data. Only used as an operator in the case of custom filtering
dynamicCriteria The dynamic criteria to apply on the column. Used with dynamic filtering.
filterOn The property used by the filter to determine whether the values should stay visible
icon The icon used to filter cells. Used with icon filtering
operator The operator used to combine criterion1 and criterion2 when using custom filtering
values The set of values to be used as part of values filtering

The possible values for the filterOn property are:

  • BottomItems - Show the rows with the bottom n items
  • BottomPercent - Show the rows with the bottom n% of values
  • CellColor - Show the rows that match the specific cell color
  • Dynamic - Show the rows that match the specific dynamic criterion
  • FontColor - Show the rows that match the specific font color
  • Values - Show the rows that contain one of the specified value
  • TopItems - Show the rows with the top n items
  • TopPercent - Show the rows with the top n% of values
  • Icon - Show the rows that match the specific icon
  • Custom - Show the rows that match custom criterion defined by criterion1, criterion2, and operator.

The possible values for the dynamicCriteria property are:

  • Unknown - No dynamic filter is applied
  • AboveAverage - Show the rows with the values above the average
  • BelowAverage - Show the rows with the values below the average
  • Today - Show the rows with the dates that match the current day
  • Tomorrow - Show the rows with the dates that match the next day
  • Yesterday - Show the rows with the dates that match the previous day
  • ThisWeek - Show the rows with the dates that match the current week
  • LastWeek -Show the rows with the dates that match the previous week
  • NextWeek - Show the rows with the dates that match the next week
  • ThisMonth - Show the rows with the dates that match the current month
  • LastMonth - Show the rows with the dates that match the previous month
  • NextMonth - Show the rows with the dates that match the next month
  • AllDatesInPeriodJanuary - Show the rows with the dates from January, no matter what the year is
  • AllDatesInPeriodFebruray - Show the rows with the dates from February, no matter what the year is. Yes, the value is with the typo Februray instead of February
  • AllDatesInPeriodMarch - Show the rows with the dates from March, no matter what the year is
  • AllDatesInPeriodApril - Show the rows with the dates from April, no matter what the year is
  • AllDatesInPeriodMay - Show the rows with the dates from May, no matter what the year is
  • AllDatesInPeriodJune - Show the rows with the dates from June, no matter what the year is
  • AllDatesInPeriodJuly - Show the rows with the dates from July, no matter what the year is
  • AllDatesInPeriodAugust - Show the rows with the dates from August, no matter what the year is
  • AllDatesInPeriodSeptember - Show the rows with the dates from September, no matter what the year is
  • AllDatesInPeriodOctober - Show the rows with the dates from October, no matter what the year is
  • AllDatesInPeriodNovember - Show the rows with the dates from November, no matter what the year is
  • AllDatesInPeriodDecember - Show the rows with the dates from December, no matter what the year is
  • ThisQuarter - Show the rows with the dates from this quarter
  • LastQuarter - Show the rows with the dates from previous quarter
  • NextQuarter - Show the rows with the dates from next quarter
  • AllDatesInPeriodQuarter1 - Show the rows with the dates from the first quarter, no matter what the year is
  • AllDatesInPeriodQuarter2 - Show the rows with the dates from the second quarter, no matter what the year is
  • AllDatesInPeriodQuarter3 - Show the rows with the dates from the third quarter, no matter what the year is
  • AllDatesInPeriodQuarter4 - Show the rows with the dates from the fourth quarter, no matter what the year is
  • ThisYear - Show the rows with the dates from this year
  • LastYear - Show the rows with the dates from previous year
  • NextYear - Show the rows with the dates from next year
  • YearToDate - Show the rows with the dates from the beginning of the current year to the current day

The possible values for the operator property are:

  • And - The both criterion1 and criterion2 must be satisfied
  • Or - At least one criterion must be satisfied

The icon property is of the type workbookIcon and has the index property and the set property. The index represents the index of the icon in the given set. The set specifies the set that the icon is part of. Possible values for the set property are:

  • Invalid - no set of icons
  • ThreeArrows -
  • ThreeArrowsGray -
  • ThreeFlags -
  • ThreeTrafficLights1 -
  • ThreeTrafficLights2 -
  • ThreeSigns -
  • ThreeSymbols -
  • ThreeSymbols2 -
  • FourArrows -
  • FourArrowsGray -
  • FourRedToBlack -
  • FourRating -
  • FourTrafficLights -
  • FiveArrows -
  • FiveArrowsGray -
  • FiveRating -
  • FiveQuarters -
  • ThreeStars -
  • ThreeTriangles -
  • FiveBoxes -

The filtering criteria can be applied by making the request

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria" : {
    "color": "string",
    "criterion1": "string",
    "criterion2": "string",
    "dynamicCriteria": "string",
    "filterOn": "string",
    "operator": "string",
    "icon": {"@odata.type": "microsoft.graph.workbookIcon"},
    "values": {"@odata.type": "microsoft.graph.Json"}
  }
}

The request body contains a JSON representing the workbookFilterCriteria.

There are alternative endpoints for specific filters defined in the filterOn property.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyBottomItemsFilter
{
  "count": interger
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyBottomPercentFilter
{
  "percent": interger
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyCellColorFilter
{
  "color": "string"
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyCustomFilter
{
  "criteria1": "string",
  "criteria2": "string",
  "oper": "string"
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyDynamicFilter
{
  "criteria": "string"
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyFontColorFilter
{
  "color": "string"
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyIconFilter
{
  "icon": {"@odata.type": "microsoft.graph.workbookIcon"}
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyTopItemsFilter
{
  "count": interger
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyTopPercentFilter
{
  "percent": interger
}

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyValuesFilter
{
  "values": {"@odata.type": "microsoft.graph.Json"}
}

Let's practice all the filtering criteria. As a reference file, you can download the following Excel file and used it for testing.

For testing, upload the file to OneDrive or SharePoint, where you have access through the Graph API.

Filter by values

Filter rows that contain specific numbers

Specify the numbers in the values property.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": null,
    "criterion2": null,
    "filterOn": "Values",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": [
      "1",
      "11",
      "13",
      "15",
      "3",
      "5",
      "7",
      "9"
    ]
  }
}

The alternative endpoint

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyValuesFilter
{
  "values": [
    "1",
    "11",
    "13",
    "15",
    "3",
    "5",
    "7",
    "9"
  ]
}

Filter rows that contain specific texts

Specify the texts in the values property.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria" : {
    "color": null,
    "criterion1": null,
    "criterion2": null,
    "filterOn": "Values",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": [
      "Graph API",
      "Sites",
      "Workbook"
    ]
  }
}

Filter rows that contain dates in specific month and year

If the format of the cells in the column is set to date, you can filter rows by specific year, month, or a day. The items in the values property must supply a JSON representation of Microsoft.ExcelServices.FilterDatetime object.

The Microsoft.ExcelServices.FilterDatetime type has properties date and specificity. The date property is the date in ISO8601 format used to filter data. The specificity describes how specific the date should be used to keep data. The possible values are: Year, Month, Day, Hour, Minute, Second.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": null,
    "criterion2": null,
    "filterOn": "Values",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": [
      {
        "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
        "specificity": "Month",
        "date": "2023-07-01"
      },
      {
        "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
        "specificity": "Month",
        "date": "2023-08-01"
      }
    ]
  }
}

The @odata.type can be ommited.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": null,
    "criterion2": null,
    "filterOn": "Values",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": [
      {
        "specificity": "Month",
        "date": "2023-07-01"
      },
      {
        "specificity": "Month",
        "date": "2023-08-01"
      }
    ]
  }
}

Filter rows that contain dates in specific year

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": null,
    "criterion2": null,
    "filterOn": "Values",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": [
      {
        "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
        "specificity": "Year",
        "date": "2023-01-01"
      }
    ]
  }
}

Filter rows that contain dates in specific day

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": null,
    "criterion2": null,
    "filterOn": "Values",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": [
      {
        "@odata.type": "Microsoft.ExcelServices.FilterDatetime",
        "specificity": "Day",
        "date": "2023-06-23"
      }
    ]
  }
}

Custom filter

Filter rows that contain the value either one value of another

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=1",
    "criterion2": "=3",
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that do not contain specific value

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "<>5",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain value greater than a specific value

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": ">6",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain value greater than or equal to a specific value

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": ">=7",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain value less than a specific value

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "<8",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain value less than or equal to a specific value

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "<=9",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain value between one and another value

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": ">=4",
    "criterion2": "<=8",
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

Filter rows that contain value less than one or greater than another value

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "<3",
    "criterion2": ">12",
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain value equals to a specific string

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=Calendar",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain a specific substring

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=*ot*",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that do not contain a specific substring

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "<>*ot*",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain one specific substring but do not contain another specific substring

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=*p*",
    "criterion2": "<>*t*",
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

Filter rows that begin with

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=g*",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that end with

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=*s",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows that contain a specific date

The date can be specified in the format m/dd/yyyy.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=7/30/2023",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Or specify the date by number of days since 1/1/1900.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "=45135",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows with the dates before a specific date

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "<07/19/2023",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Or

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "<45126",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows with the dates after a specific date

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": ">07/01/2023",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Or

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": ">45108",
    "criterion2": null,
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "Or",
    "values": null
  }
}

Filter rows between two dates

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": ">=07/01/2023",
    "criterion2": "<=07/31/2023",
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

Or

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": ">=45108",
    "criterion2": "<=45138",
    "filterOn": "Custom",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

Filtering the rows with the top n items

Filter top 10 items

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "10",
    "criterion2": null,
    "filterOn": "TopItems",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

There is an alternative endpoint which allows to apply top items filter, by specifying only number of top items.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyTopItemsFilter
{
  "count": 10
}

Filtering th rows with the bottom n items

Filter bottom 10 items

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "10",
    "criterion2": null,
    "filterOn": "BottomItems",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

The alternative endpoint, number of items specified in the request body

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyBottomItemsFilter
{
  "count": 10
}

Filtering the rows with the top n% of values

Filter top 10 percent

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "10",
    "criterion2": null,
    "filterOn": "TopPercent",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

The alternative endpoint, number of percent specified in the request body.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyTopPercentFilter
{
  "percent": 10
}

Filtering the rows with the bottom n% of values

Filter bottom 10 percent

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/apply
{
  "criteria": {
    "color": null,
    "criterion1": "10",
    "criterion2": null,
    "filterOn": "BottomPercent",
    "dynamicCriteria": "Unknown",
    "icon": null,
    "operator": "And",
    "values": null
  }
}

The alternative endpoint, number of percent specified in the request body.

POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/filter/applyBottomPercentFilter
{
  "percent": 10
}

In the next post, I will describe Dynamic, CellColor, FontColor, and Icon filter.

0
Buy Me a Coffee at ko-fi.com
An error has occurred. This application may no longer respond until reloaded. Reload x