Automating Excel Online with Microsoft Graph

In a Microsoft 365 organisation the important workbooks are no longer on anyone's C: drive. They sit in SharePoint document libraries and OneDrive folders, several people have them open at once, and Power Automate or Power BI may already read from them. A reporting script that expects a local path does not fit that world, and one that downloads, edits and re-uploads files will sooner or later overwrite somebody's changes. Microsoft Graph offers two better routes: file endpoints that move workbook bytes in and out, and a workbook API that edits ranges and tables inside Excel's online service. This section of Automating Reporting Workflows shows when to use each, and how to build jobs on them that survive a schedule.

What this section covers

• Authenticate to Microsoft Graph with MSAL for Excel Automation — delegated tokens with a persistent cache, and app-only tokens where they are allowed.
• Read an Excel File from SharePoint or OneDrive into pandas — resolve sites and drives, download into memory, skip unchanged files.
• Read and Write Excel Online Ranges with Microsoft Graph — ranges, used ranges, dates, blanks and formulas.
• Append Rows to an Excel Online Table with Microsoft Graph — idempotent, batched feeds into shared tables.
• Recalculate Excel Online Workbooks with Graph Sessions — sessions, calculation and what-if scenarios.

Publishing a finished report to SharePoint is covered separately in Upload an Excel Report to SharePoint with Python.

The rule that shapes everything: which token for which call

Graph issues two kinds of access token. An app-only token comes from a client secret or certificate and represents the application itself; it is ideal for scheduled jobs because no person is involved. A delegated token represents a signed-in user and carries that user's access. The file endpoints — download a workbook's content, upload a new version — accept application permissions. The workbook API endpoints — ranges, tables, calculation, sessions — list application permissions as not supported in Microsoft's reference. That single fact decides how most jobs in this section are built.

import os
import msal

AUTHORITY = f"https://login.microsoftonline.com/{os.environ['GRAPH_TENANT_ID']}"

def app_token() -> str:                       # file endpoints: download, upload
    app = msal.ConfidentialClientApplication(os.environ["GRAPH_CLIENT_ID"], authority=AUTHORITY,
                                             client_credential=os.environ["GRAPH_CLIENT_SECRET"])
    return app.acquire_token_for_client(scopes=["https://graph.microsoft.com/.default"])["access_token"]

def delegated_token(cache: msal.SerializableTokenCache) -> str:   # workbook API: ranges, tables, calculate
    app = msal.PublicClientApplication(os.environ["GRAPH_CLIENT_ID"], authority=AUTHORITY, token_cache=cache)
    accounts = app.get_accounts()
    result = app.acquire_token_silent(["Files.ReadWrite"], account=accounts[0]) if accounts else None
    if not result:
        raise RuntimeError("no cached sign-in: run the device code sign-in once by hand")
    return result["access_token"]

print(app_token()[:8], delegated_token(msal.SerializableTokenCache())[:8])

A delegated job still runs unattended once it has been set up: sign in once with the device code flow, persist MSAL's token cache as a protected file, and every later run acquires tokens silently. Use a dedicated account whose access is limited to the libraries the job touches, and make the script fail fast — never wait for a device code — when the cached sign-in has expired. The full setup, including cache protection and a token check, is in Authenticate to Microsoft Graph with MSAL for Excel Automation.

Setting up the app registration

Both token types come from a single app registration in Microsoft Entra ID, configured for the two roles it plays. Keeping them on one registration is simpler to audit; splitting them into two registrations is reasonable when different teams own the read and write sides.

Two operational decisions are worth making at the same time. Decide which account signs in for the delegated side — a dedicated service account is far easier to manage than a person's account that disappears when they change roles — and confirm how your organisation's conditional access policies treat it, because a policy that demands periodic re-authentication will eventually require a manual sign-in. And decide where the token cache and client secret live on the machine that runs the job, with file permissions or a secret store that only the job's user can read.

Addressing a workbook

Every call starts from a drive item. SharePoint sites are resolved from their host name and path to a site ID, a document library is the site's drive, and files are addressed either by path under the drive root or by their stable item ID. A OneDrive is the same shape under /users/{upn}/drive. Resolve IDs once and keep them in configuration; paths change when people rename folders, item IDs do not.

import requests
from urllib.parse import quote

GRAPH = "https://graph.microsoft.com/v1.0"
http = requests.Session()
http.headers["Authorization"] = f"Bearer {app_token()}"

site = http.get(f"{GRAPH}/sites/contoso.sharepoint.com:/sites/Finance", timeout=60).json()["id"]
drive = http.get(f"{GRAPH}/sites/{site}/drive", timeout=60).json()["id"]
item = http.get(f"{GRAPH}/drives/{drive}/root:/{quote('Reports/2026/sales.xlsx')}", timeout=60).json()

BOOK = f"{GRAPH}/drives/{drive}/items/{item['id']}/workbook"
print(item["name"], item["eTag"], BOOK)

The item metadata is useful on its own. Its eTag changes whenever the file changes, so comparing it with the last value your job processed is a cheap way to skip work when a source file has not been touched since the previous run.

Reading source data into pandas

When a job needs the contents of a workbook — every row of a sheet, several sheets, a folder of branch files — downloading the file is simpler and faster than asking the workbook API for ranges. Request :/content for the item, wrap the bytes in BytesIO, and every read_excel option applies.

from io import BytesIO
import pandas as pd

content = http.get(f"{GRAPH}/drives/{drive}/items/{item['id']}/content", timeout=300).content
frames = pd.read_excel(BytesIO(content), sheet_name=None)
for name, frame in frames.items():
    print(name, frame.shape)

Reading the whole file this way has a second advantage beyond speed: the job works on a consistent snapshot. Several range requests made one after another can straddle an edit somebody makes in between, so the first sheet reflects the old numbers and the second the new ones. A single download cannot, which matters whenever a report reconciles figures across sheets.

This path works with an app-only token and reads the last saved version, which in Excel for the web is continuously autosaved. For folders, follow @odata.nextLink when listing children; for very large files, stream the response to a temporary file instead of holding it in memory. Each of those, with site resolution and eTag checks, is in Read an Excel File from SharePoint or OneDrive into pandas.

Editing ranges in place

Writing back is where the routes diverge sharply. Uploading a modified copy replaces the whole file and races with anyone who has it open. The workbook API edits just the range you name, inside the online service, so co-authors see new values appear and formulas recalculate. The request is a PATCH with a values array whose dimensions exactly match the address.

http.headers["Authorization"] = "Bearer " + delegated_token(msal.SerializableTokenCache())

summary = pd.DataFrame({"Region": ["North", "South"], "Amount": [2000.0, 1600.0]})
values = [list(summary.columns)] + [[v.item() if hasattr(v, "item") else v for v in row]
                                    for row in summary.itertuples(index=False)]
address = f"A1:B{len(values)}"
response = http.patch(f"{BOOK}/worksheets/{quote('Summary')}/range(address='{address}')",
                      json={"values": values}, timeout=60)
print(response.status_code, address)

Three details cause most of the trouble with range updates, and all three are silent. A null in values means leave this cell unchanged, not clear it, so send an empty string for blanks. NumPy scalars are not JSON-serialisable, so convert with .item(). And ISO date strings are parsed according to the workbook's locale, so send Excel serial numbers with a date numberFormat instead. Reading works the same way in reverse, with usedRange(valuesOnly=true) for sheets whose size is not known. The complete treatment is in Read and Write Excel Online Ranges with Microsoft Graph.

Feeding shared tables

When the destination is an Excel table — the usual structure behind a shared tracker, a Power BI source or a Power Automate trigger — append with tables/{name}/rows/add rather than computing addresses. The table grows, calculated columns and formatting extend, and the job never needs to know which row is the last one.

TABLE = f"{BOOK}/tables/{quote('Orders')}"
header = http.get(f"{TABLE}/headerRowRange", timeout=60).json()["values"][0]

incoming = pd.DataFrame({"Amount": [1320.0], "Rep": ["Cara"], "Region": ["West"]})
rows = [[v.item() if hasattr(v, "item") else v for v in r] for r in incoming[header].itertuples(index=False)]
added = http.post(f"{TABLE}/rows/add", json={"index": None, "values": rows}, timeout=120)
print(added.status_code, header)

Three habits turn that call into a dependable feed. Reorder the frame to the table's header, because the arrays are positional. Check existing keys before appending, so a rerun never duplicates rows. And send rows in batches — Microsoft's reference for this endpoint warns that one row per call degrades performance — retrying 504 and 429 responses with a delay. The complete job is in Append Rows to an Excel Online Table with Microsoft Graph.

Sessions and calculation

Each sessionless workbook request opens the workbook, performs the operation, saves and closes it. For a job of more than two or three calls, create a session and send its ID in the workbook-session-id header: the workbook stays open between calls, and you choose whether changes persist. A non-persistent session is the key to using a shared Excel model as a calculation service — write inputs, call application/calculate, read outputs, and let the session end without anything being saved.

created = http.post(f"{BOOK}/createSession", json={"persistChanges": False}, timeout=120).json()
http.headers["workbook-session-id"] = created["id"]
try:
    http.patch(f"{BOOK}/worksheets/Inputs/range(address='B2')", json={"values": [[0.07]]}, timeout=60)
    http.post(f"{BOOK}/application/calculate", json={"calculationType": "Recalculate"}, timeout=120)
    result = http.get(f"{BOOK}/worksheets/Model/range(address='A1:B3')", timeout=60).json()["values"]
    print(result)
finally:
    http.post(f"{BOOK}/closeSession", json={}, timeout=60)
    http.headers.pop("workbook-session-id", None)

Sessions expire after a few minutes without requests, so refresh or recreate them across long pauses. Opening a very large workbook can take long enough to time out, and session creation supports Prefer: respond-async with status polling for exactly that case. Recalculate is the right calculation type almost always; Full and FullRebuild exist for models whose dependencies ordinary recalculation misses. The patterns — a context manager, scenario loops and asynchronous session creation — are in Recalculate Excel Online Workbooks with Graph Sessions.

Throttling and concurrency

The Excel APIs in Graph do not publish simple request-per-minute limits, because the cost of a call depends heavily on the workbook. Two practices keep jobs out of trouble. First, honour Retry-After on 429 responses and retry 503 and 504 with backoff. Second, keep requests to any one workbook sequential: Microsoft's guidance is explicit that concurrent writes to the same workbook tend to cause throttling, timeouts and merge conflicts rather than speed.

import time

def with_retry(send, attempts: int = 5):
    for attempt in range(1, attempts + 1):
        response = send()
        if response.status_code in (429, 503, 504) and attempt < attempts:
            time.sleep(float(response.headers.get("Retry-After", 2 ** attempt)))
            continue
        response.raise_for_status()
        return response

ok = with_retry(lambda: http.get(f"{BOOK}/worksheets", timeout=60))
print(ok.status_code)

Parallelism is still useful across different workbooks — ten branch files can be processed by ten workers — but within one file, one request at a time is both the polite and the fastest option.

Logging what a job changed

A job that edits shared workbooks needs a record of what it did, because the first question after an unexpected number appears is always "did the script write that?". Graph's version history on the file shows that something changed; your own log should show what. Record the workbook, the operation, the range or table, the number of rows, the session mode and the Graph request-id response header, which is what Microsoft support asks for when a call behaves strangely.

import json
import logging
from datetime import datetime, timezone

logging.basicConfig(level=logging.INFO, format="%(message)s")
log = logging.getLogger("graph-excel")

def audited(operation: str, target: str, response, rows: int = 0) -> None:
    log.info(json.dumps({
        "at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
        "operation": operation,
        "workbook": item["name"],
        "target": target,
        "rows": rows,
        "status": response.status_code,
        "request_id": response.headers.get("request-id"),
        "session": "workbook-session-id" in http.headers,
    }))

audited("rows/add", "Orders", added, rows=len(rows))

One JSON object per line is easy to search and easy to ship to whatever log store the rest of your jobs use. Pair it with the eTag you recorded before and after the run, and an unexpected change can be traced to a specific run, operation and request in a few minutes. The same structured-logging approach for whole report jobs is described in Log Python Excel Script Output to a File.

When not to use Graph for Excel work

Graph is the right tool when the workbook's home is Microsoft 365 and people keep using it there. It is the wrong tool when a script is really building a report from scratch — that is faster and more controllable with xlsxwriter or openpyxl locally, followed by a single upload. It is also a poor fit for high-volume data: an Excel table in SharePoint is comfortable with thousands of rows and awkward with millions, and a database behind the workbook is the durable design at that scale. And when a step needs desktop-only features — VBA macros, some data connection types — the options are the desktop application through COM or xlwings, as in Automating Excel with COM and pywin32.

Common failures at a glance

Key takeaways

• File endpoints (download, upload) accept app-only tokens; the workbook API (ranges, tables, sessions, calculate) needs delegated tokens.
• A delegated job can run on a schedule with a device-code sign-in once and a protected MSAL token cache.
• Download with :/content into BytesIO to read whole workbooks into pandas; use eTags to skip unchanged files.
• Edit ranges in place with PATCH: exact dimensions, "" for blanks, .item() for NumPy values, serial numbers for dates.
• Append to shared tables with rows/add in batches, aligned to the header and de-duplicated by key.
• Use sessions for multi-call jobs; non-persistent sessions plus calculate turn a shared model into a calculation service.
• Keep requests to one workbook sequential and honour Retry-After.

Frequently asked questions

Do I need Excel installed to use the Graph workbook API? No. The workbook API runs on Excel's online service in Microsoft 365. Your script only needs HTTP access, a token and the workbook's location.

Does the workbook API work with .xls or .csv files? No. It works with .xlsx workbooks. Convert older formats first, or read them by downloading the file content and using pandas with the appropriate engine.

Can a scheduled job use the workbook API without a user account? Not with application permissions, which the workbook endpoints do not support. Use a dedicated account with a cached delegated sign-in, or download, modify and upload the file with an app-only token when nobody edits it concurrently.

Is the Graph SDK for Python required? No. The endpoints are plain REST calls, and requests with MSAL is enough, as used throughout this section. The msgraph-sdk package works too if you prefer typed request builders.

Will people with the workbook open see changes made through Graph? Yes. Range updates and table appends through the workbook API behave like co-authoring edits, so open copies in Excel for the web update as the job runs.

How do I test a Graph job without touching production workbooks? Point the job at a copy of the workbook in a test library by keeping drive and item IDs in configuration, and use non-persistent sessions for dry runs. For unit tests, replace the HTTP session with a fake that returns recorded responses, so the transformation logic is tested without any network calls.

Related

• Up one level: Automating Reporting Workflows — scheduling, delivery and packaging for Excel reports.
• Authenticate to Microsoft Graph with MSAL for Excel Automation
• Read an Excel File from SharePoint or OneDrive into pandas
• Read and Write Excel Online Ranges with Microsoft Graph
• Append Rows to an Excel Online Table with Microsoft Graph
• Recalculate Excel Online Workbooks with Graph Sessions
• Publishing Excel Reports to Cloud Storage — uploading finished reports to SharePoint, S3 and Drive.
• Send an Excel Report with the Microsoft Graph API — mail delivery through the same platform.
• Using Python in Excel — Python inside the workbook rather than calling into it.

More in this section:

• Add a Chart to an Excel Online Workbook with Microsoft Graph
• Add, Rename and Delete Worksheets with Microsoft Graph
• Copy a Template Workbook in SharePoint with Microsoft Graph
• Create a New Workbook in OneDrive with Microsoft Graph
• Run a Report When an Excel File Changes in OneDrive
• Share an Excel File in OneDrive with a Link Using Graph
• Sort and Filter an Excel Online Table with Microsoft Graph