Read and Write Excel Online Ranges with Microsoft Graph

Downloading a workbook, editing it with openpyxl and uploading it again works until two things happen at once: someone has the file open in Excel for the web, and your upload replaces their afternoon's work. The Graph workbook API avoids that race by editing the workbook where it lives. You read and write ranges through Excel's own online service, the file never leaves SharePoint, formulas recalculate, and people who have it open see the new values appear. This guide, part of Automating Excel Online with Microsoft Graph, covers reading ranges into pandas and writing DataFrames back.

Prerequisites

pip install msal requests pandas openpyxl

• A delegated access token with Files.ReadWrite. The workbook endpoints do not support application permissions; the device-code-and-cache setup in Authenticate to Microsoft Graph with MSAL for Excel Automation provides one that works on a schedule.
• The drive ID and item ID of an .xlsx workbook. The workbook API works on .xlsx files only — not .xls, and not .csv.

Step 1 — Build the workbook base URL

Every workbook call hangs off the file's item URL followed by /workbook. Build it once, and give the session a helper that raises with Graph's error message, which for the workbook API is usually specific about the problem.

import os
from urllib.parse import quote
import requests

GRAPH = "https://graph.microsoft.com/v1.0"
DRIVE_ID = os.environ["GRAPH_DRIVE_ID"]
ITEM_ID = os.environ["GRAPH_ITEM_ID"]
BOOK = f"{GRAPH}/drives/{DRIVE_ID}/items/{ITEM_ID}/workbook"

session = requests.Session()
session.headers["Authorization"] = "Bearer " + os.environ.get("GRAPH_DELEGATED_TOKEN", "token")

def call(method: str, url: str, **kwargs) -> dict:
    response = session.request(method, url, timeout=60, **kwargs)
    if response.status_code >= 400:
        raise RuntimeError(f"{method} {url} -> {response.status_code}: {response.text[:400]}")
    return response.json() if response.content else {}

def sheet_url(name: str) -> str:
    return f"{BOOK}/worksheets/{quote(name)}"

print([ws["name"] for ws in call("GET", f"{BOOK}/worksheets")["value"]])

Quoting the worksheet name handles spaces and punctuation in tab names. Reuse this call helper for everything that follows; the other guides in this section build on the same shape.

Step 2 — Read a range into pandas

range(address='A1:C20') returns a JSON object whose values property is a list of rows. The first row is the header when the range starts at one, so turning it into a DataFrame is a single constructor call.

import pandas as pd

def read_range(sheet: str, address: str) -> pd.DataFrame:
    data = call("GET", f"{sheet_url(sheet)}/range(address='{address}')")
    header, *rows = data["values"]
    return pd.DataFrame(rows, columns=header)

sales = read_range("Sales", "A1:C3")
print(sales)

The response also carries text — the values as formatted strings, exactly as Excel displays them — as well as formulas and numberFormat. values is what you want for analysis, because it keeps numbers as numbers; text is useful when you need to see a date or currency the way a reader does.

Step 3 — Read whatever is used on a sheet

When the size of the data is not known in advance, ask for the sheet's used range. The valuesOnly=true argument ignores cells that have formatting but no value, which stops a sheet where somebody once coloured row 5,000 from returning thousands of empty rows.

def read_used_range(sheet: str) -> pd.DataFrame:
    data = call("GET", f"{sheet_url(sheet)}/usedRange(valuesOnly=true)")
    header, *rows = data["values"]
    frame = pd.DataFrame(rows, columns=header)
    print("used range:", data["address"])
    return frame.replace("", pd.NA).dropna(how="all")

used = read_used_range("Sales")
print(used.shape)

Empty cells come back as empty strings rather than nulls, which is why the replace is there before any blank-row cleanup. The address in the response, such as Sales!A1:C3, is worth logging: it shows at a glance when a sheet has grown unexpectedly.

Step 4 — Write a DataFrame back with the right address

Writing is a PATCH to a range with a values array whose dimensions exactly match the address. Compute the address from the frame's shape instead of typing it, so the two can never disagree, and convert every value into something JSON can carry.

from openpyxl.utils import get_column_letter

def address_for(frame: pd.DataFrame, top_left_row: int = 1, top_left_col: int = 1, header: bool = True) -> str:
    rows = len(frame) + (1 if header else 0)
    first = f"{get_column_letter(top_left_col)}{top_left_row}"
    last = f"{get_column_letter(top_left_col + frame.shape[1] - 1)}{top_left_row + rows - 1}"
    return f"{first}:{last}"

def to_cell(value):
    if value is None or (isinstance(value, float) and pd.isna(value)) or value is pd.NA:
        return ""                     # empty string clears a cell; null would leave it unchanged
    if isinstance(value, pd.Timestamp):
        return (value.normalize() - pd.Timestamp("1899-12-30")).days + value.hour / 24 + value.minute / 1440
    if hasattr(value, "item"):
        return value.item()           # NumPy scalar -> plain Python number
    return value

def write_frame(sheet: str, frame: pd.DataFrame, top_left_row: int = 1, top_left_col: int = 1) -> str:
    values = [list(frame.columns)] + [[to_cell(v) for v in row] for row in frame.itertuples(index=False)]
    address = address_for(frame, top_left_row, top_left_col)
    call("PATCH", f"{sheet_url(sheet)}/range(address='{address}')", json={"values": values})
    return address

summary = sales.groupby("Region", as_index=False)["Amount"].sum()
print("wrote", write_frame("Summary", summary))

Two conversions in to_cell prevent the most confusing failures. In a range update, null does not mean "empty" — it means "leave this cell as it is", so a NaN sent as null silently keeps last week's value in place. Send an empty string to clear a cell instead. And NumPy integers and floats are not JSON-serialisable, so .item() turns them into plain Python numbers before requests encodes the body.

Step 5 — Send dates and number formats deliberately

Excel stores dates as serial numbers and shows them as dates through a number format. The workbook API behaves the same way: send a serial number and a date format together in one PATCH, and the cells behave like real dates for sorting, filtering and formulas. numberFormat takes an array with the same dimensions as values.

monthly = pd.DataFrame({
    "Month": pd.to_datetime(["2026-07-01", "2026-08-01", "2026-09-01"]),
    "Revenue": [48210.5, 51990.0, 47305.25],
})
address = address_for(monthly, top_left_row=1, top_left_col=5)          # E1:F4
values = [list(monthly.columns)] + [[to_cell(v) for v in row] for row in monthly.itertuples(index=False)]
formats = [["General", "General"]] + [["mmm yyyy", "#,##0.00"] for _ in range(len(monthly))]

call("PATCH", f"{sheet_url('Summary')}/range(address='{address}')",
     json={"values": values, "numberFormat": formats})
print("dates written to", address)

Sending ISO date strings such as "2026-09-01" also often works, because Excel parses the text as it would typed input — but the result depends on the workbook's locale, and a day-first locale turns 2026-03-04 into April. Serial numbers are unambiguous. The format codes are the same ones used from openpyxl, listed in Write Custom Number Format Codes in Excel with Python.

Step 6 — Write formulas instead of values

The same PATCH accepts a formulas array. Writing a formula lets Excel compute totals and ratios in the workbook, so they stay correct when someone edits the numbers by hand later.

last_row = len(summary) + 1
total_row = last_row + 1
call("PATCH", f"{sheet_url('Summary')}/range(address='A{total_row}:B{total_row}')",
     json={"formulas": [["Total", f"=SUM(B2:B{last_row})"]]})
print("total formula in row", total_row)

A plain string such as Total is accepted in the formulas array and written as a constant. The formula is calculated by the service according to the workbook's calculation mode; if the workbook is set to manual calculation, trigger a recalculation as described in Recalculate Excel Online Workbooks with Graph Sessions.

Common pitfalls

Performance and scale notes

Each call opens the workbook in the online service, which is noticeably slower than a local file operation, so write in blocks rather than cell by cell: one PATCH for a 500-row table is far faster than 500 single-cell updates. For more than a couple of calls against the same file, create a workbook session and pass its ID with each request, which avoids reopening the workbook every time. Very large writes — tens of thousands of cells — are better split into chunks of a few thousand rows, sent sequentially, with a retry on 429 and 504 responses.

Conclusion

The workbook API edits a OneDrive or SharePoint workbook in place, which makes it the right tool whenever people may have the file open. Read with range(address=…) or usedRange(valuesOnly=true) and build the DataFrame from values. Write with a PATCH whose address is computed from the frame's shape, convert blanks to empty strings and NumPy scalars to Python numbers, and send dates as serial numbers with a matching numberFormat. Use a delegated token, write in blocks, and move to sessions once a job makes more than a few calls.

Frequently asked questions

Can I append rows below existing data with a range update? You can, by reading the used range to find the last row and writing below it. If the data is an Excel table, adding rows to the table is simpler and keeps formulas and formatting extending automatically.

Why does my range update succeed but nothing changes? Most often the values were null, which the API treats as leave unchanged. Also check that you wrote to the sheet you are looking at, and that the request was not made in a non-persistent session.

Can I write to a workbook that is open in Excel for the web? Yes. The workbook API works with co-authoring, and people who have the file open see the changes. Avoid rewriting ranges someone is actively editing at the same moment.

Is there a limit to how much I can write in one request? Graph does not publish a fixed cell limit, but large payloads are slower and more likely to time out. Chunks of a few thousand rows per request are a reliable starting point.

Related

• Up one level: Automating Excel Online with Microsoft Graph — the whole toolkit for workbooks in Microsoft 365.
• Append Rows to an Excel Online Table with Microsoft Graph — the easier write path when the data is a table.
• Recalculate Excel Online Workbooks with Graph Sessions — sessions, calculation and batching many calls.
• Read an Excel File from SharePoint or OneDrive into pandas — the download path when you only need to read.
• Fix Excel Serial Numbers Showing Instead of Dates — how Excel date serials work.