Sort and Filter an Excel Online Table with Microsoft Graph

An Excel table shared in OneDrive or SharePoint often needs to be left in a particular state: the open-issues log sorted by priority, the sales tracker filtered to the current quarter before a review meeting, the order list showing only overdue items for the team that chases them. Microsoft Graph's workbook API can apply exactly the sorts and filters a person would set with the drop-down arrows in the header row, and it can also read back only the rows a filter leaves visible. This page sorts a table on several columns, filters by a list of values, by custom criteria and by top items, reads the visible rows, clears everything again, and closes with the question worth asking first — whether the workbook should be changed at all, or the rows simply read and filtered in Python. It belongs to Automating Excel Online with Microsoft Graph.

Prerequisites

pip install requests pandas

You need a Graph token with Files.ReadWrite, and the drive and item ID of a workbook that contains an Excel table — a formatted table, not just a range of cells, since sorting and filtering through the API work on table objects. Authenticate to Microsoft Graph with MSAL for Excel Automation covers the token. The outputs below come from a mocked Graph API that applies the sorts and filters to an eight-row orders table, so the results can be shown without a tenant; the request shapes are the documented ones.

Step 1 — Address the table and sort it

The table is addressed by name under the workbook. A sort is a POST to sort/apply with a list of fields; each field's key is the zero-based column index within the table, not the sheet column:

import os
import requests

GRAPH = "https://graph.microsoft.com/v1.0"
TABLE = f"{GRAPH}/drives/b!ops-drive/items/01ORDERSBOOK/workbook/tables('Orders')"
session = requests.Session()
session.headers["Authorization"] = f"Bearer {os.environ['GRAPH_TOKEN']}"

headers = session.get(f"{TABLE}/headerRowRange", timeout=60).json()["values"][0]
key = {name: i for i, name in enumerate(headers)}

def post(path, body=None):
    r = session.post(f"{TABLE}/{path}", json=body or {}, timeout=60)
    r.raise_for_status()

def show(title):
    rows = session.get(f"{TABLE}/range/visibleView/rows", timeout=60).json()["value"][1:]
    print(title)
    for r in rows:
        print("  ", *r["values"][0])

post("sort/apply", {"fields": [{"key": key["Status"], "ascending": True}, {"key": key["Amount"], "ascending": False}]})
show("sorted by Status, then Amount descending:")

Looking the column positions up from the header row, rather than hard-coding 3 for Amount, keeps the code working when someone inserts a column in the table. The fields are applied in order of priority: the first is the main sort, the second breaks ties within it — the same as adding levels in Excel's Sort dialog. Fields also accept matchCase for text sorts and sortOn for sorting by cell or font colour, which mirrors the options in the dialog. The sort is applied to the workbook itself, so everyone who opens the file sees the table in the new order.

That shared effect is the reason to sort through Graph rather than locally: a team log that should always open with the newest or most urgent items at the top can be put in that order by a scheduled job a few minutes before people start work, with no one having to remember to click the header. Rows added later by people or by an append job land at the bottom of the table, so run the sort after each batch of appends if the order matters. A sort also moves whole rows, including any cells in the table that contain formulas, so formulas that refer to other rows of the same table by position rather than by structured reference can produce different results after sorting — another reason to prefer structured references in shared workbooks.

Step 2 — Filter by values, by custom criteria and by top items

Filters are applied per column with columns('{name}')/filter/apply. The criteria object's filterOn decides the kind of filter:

def filter_column(name, criteria):
    post(f"columns('{name}')/filter/apply", {"criteria": criteria})

filter_column("Status", {"filterOn": "values", "values": ["Overdue", "Open"]})
filter_column("Amount", {"filterOn": "custom", "criterion1": ">=1000"})
show("Open or Overdue, amount at least 1,000:")

post("clearFilters")
filter_column("Amount", {"filterOn": "topItems", "criterion1": "3"})
show("top three amounts:")

values keeps rows whose cell text is in the list — the checkboxes in the drop-down. custom takes one or two criteria such as >=1000 or =North, combined with "operator": "and" or "or" — Excel's "Number Filters" and "Text Filters"; wildcards like =*Ltd work as they do in Excel. topItems, bottomItems, topPercent and bottomPercent keep the top or bottom N rows or percent by value. Filters on different columns combine with AND, exactly as in the interface: the first example shows open and overdue orders that are also at least 1,000. Other filterOn values cover filtering by cell or font colour, by icon and by dynamic criteria such as "this month" — for example {"filterOn": "dynamic", "dynamicCriteria": "thisMonth"} on a date column, which keeps working as the calendar moves on.

Remember that filter values compare against the cell's text: a column formatted to show 1,250.00 must be filtered on that text when using values, while custom criteria compare numbers. Dates are the usual trap — use dynamic criteria or numeric custom criteria on the date serial rather than matching displayed date strings, which depend on the workbook's locale.

Step 3 — Read the visible rows, then clear

range/visibleView/rows returns only the rows the current filters leave visible, in the current sort order, with the header as the first row. That is how the show() helper above printed its results, and it is the way to read what a person looking at the filtered table sees. Afterwards, put the table back:

post("clearFilters")
post("sort/clear")
rows = session.get(f"{TABLE}/rows", timeout=60).json()["value"]
print(len(rows), "rows visible again, first:", rows[0]["values"][0][0])

clearFilters removes every column filter on the table, and sort/clear removes the sort state — the rows stay where the last sort left them in a real workbook (clearing a sort does not unsort data), so if the original order matters, include a sequence column and sort by it to restore it. The mock here returns the original order, which a real workbook would only do after such a sort. For workbooks several people edit at once, wrap a sequence of filter and read calls in a workbook session so they see a consistent state; sessions are covered in Recalculate Excel Online Workbooks with Graph Sessions.

Step 4 — Or filter in pandas instead

Applying a filter changes the shared workbook: everyone who opens it now sees only the filtered rows, and someone who did not set the filter may conclude that data is missing. If the goal is to use a subset of the rows in Python, read the table once and filter locally:

import pandas as pd

values = [r["values"][0] for r in session.get(f"{TABLE}/rows", timeout=60).json()["value"]]
df = pd.DataFrame(values, columns=headers)
overdue = df[(df["Status"] == "Overdue") & (df["Amount"] >= 700)].sort_values("Amount", ascending=False)
print(overdue.to_string(index=False))

This leaves the workbook untouched, uses exact comparisons on real values rather than displayed text, and supports conditions Excel's filters cannot express. Use Graph's sort and filter when the point is the state of the workbook for people — the view a team opens before a meeting, or the order a log should keep. Use pandas when the point is the data for your code. Loading the whole file into pandas is covered in Read an Excel File from SharePoint or OneDrive into pandas.

Common pitfalls

• Hard-coded column keys. Look positions up from the header row.
• Filtering on displayed text with numbers. Use custom criteria for numbers and dates.
• Forgetting the shared view. A filter you leave on confuses the next reader.
• Expecting sort/clear to restore the original order. Keep a sequence column.
• Using a plain range. Sorting and filtering through these endpoints need a table.

Conclusion

To sort an Excel Online table with Graph, POST to tables('{name}')/sort/apply with fields whose key is the column's index in the table. To filter, POST criteria to columns('{name}')/filter/apply — values, custom, topItems or dynamic — read what remains with range/visibleView/rows, and clear it with clearFilters and sort/clear. Do this when people need to see the workbook that way; when only your code needs a subset, read the rows into pandas and filter there.

Frequently asked questions

How do I sort an Excel Online table with Microsoft Graph? POST to the table's sort/apply endpoint with a fields list; each field has a key, the zero-based column index within the table, and ascending true or false.

How do I filter a table column through Graph? POST to the column's filter/apply endpoint with criteria such as filterOn values and a list of values, or filterOn custom with criterion1 like >=1000.

How do I read only the visible rows after filtering? GET the table's range/visibleView/rows; it returns the header and the rows the filters leave visible, in the current order.

Does filtering through Graph change the file for other users? Yes. Filters and sorts are saved in the workbook, so everyone sees them; filter in pandas if only your code needs the subset.

Related

• Up one level: Automating Excel Online with Microsoft Graph — Excel in the cloud from Python.
• Append Rows to an Excel Online Table with Microsoft Graph — adding rows to the table.
• Read and Write Excel Online Ranges with Microsoft Graph — working with ranges.
• Apply AutoFilter Criteria and Hide Rows — the same for local files.