Recalculate Excel Online Workbooks with Graph Sessions

Plenty of the logic behind a report lives in formulas somebody built years ago: a pricing model, a commission calculator, a forecast with a dozen assumptions on an Inputs sheet. Rewriting that in pandas is risky and slow to sign off. The Graph workbook API lets a script use the model as it is — write the inputs, let Excel's online calculation engine do the work, and read the outputs — with no copy of Excel installed anywhere. Doing that reliably depends on two features: workbook sessions, which keep the workbook open between calls and decide whether changes are saved, and the calculate action. This guide from Automating Excel Online with Microsoft Graph covers both.

Prerequisites

pip install msal requests pandas

• A delegated token for a work or school account with Files.ReadWrite. The calculate action is listed as not supported for personal Microsoft accounts and for application permissions, so neither a consumer OneDrive nor an app-only token will work here. Authenticate to Microsoft Graph with MSAL for Excel Automation sets one up.
• An .xlsx workbook in OneDrive for Business or SharePoint with an input range and formula-driven outputs. The examples use an Inputs sheet with assumptions in B2:B4 and a Model sheet with results in A1:B3.

Step 1 — Understand the three ways to call the workbook API

Every workbook request runs in one of three modes, chosen by whether a session exists and how it was created.

• Persistent session (persistChanges: true) — changes are saved to the file, and the workbook stays open in the service between calls, which is much faster for a sequence of requests.
• Non-persistent session (persistChanges: false) — changes are visible to later calls in the same session but are discarded when the session ends. Nothing reaches the file.
• Sessionless — no header at all. Each request opens the workbook, performs the operation, saves and closes it. Fine for a single call; slow and less predictable for a sequence.

Step 2 — Wrap the session in a context manager

A session must be closed, including when something in the middle fails. A context manager guarantees the closeSession call and sets the workbook-session-id header on the shared requests.Session for everything inside the block.

import os
from contextlib import contextmanager
from urllib.parse import quote
import requests

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

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

def call(method: str, url: str, **kwargs):
    response = http.request(method, url, timeout=120, **kwargs)
    if response.status_code >= 400:
        raise RuntimeError(f"{method} {url} -> {response.status_code}: {response.text[:400]}")
    return response

@contextmanager
def workbook_session(persist: bool):
    created = call("POST", f"{BOOK}/createSession", json={"persistChanges": persist}).json()
    http.headers["workbook-session-id"] = created["id"]
    try:
        yield created["id"]
    finally:
        try:
            call("POST", f"{BOOK}/closeSession", json={})
        finally:
            http.headers.pop("workbook-session-id", None)

with workbook_session(persist=False) as session_id:
    print("session", session_id)

Closing releases the workbook in the service promptly. Sessions also expire on their own after a few minutes without requests, so a job that pauses between calls — waiting on a database query, say — should call POST /workbook/refreshSession periodically, or simply create a new session after the pause.

Step 3 — Force a recalculation before reading results

If the workbook is in automatic calculation mode, dependent formulas update when you write inputs. Many financial models are set to manual calculation to keep them responsive, and then written inputs do not change any outputs until something recalculates. The calculate action does that explicitly, with three levels.

def recalculate(kind: str = "Recalculate") -> None:
    if kind not in {"Recalculate", "Full", "FullRebuild"}:
        raise ValueError(kind)
    call("POST", f"{BOOK}/application/calculate", json={"calculationType": kind})   # 204 No Content

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

with workbook_session(persist=True):
    call("PATCH", f"{sheet('Inputs')}/range(address='B2:B4')", json={"values": [[0.05], [1200], [0.18]]})
    recalculate()
    outputs = call("GET", f"{sheet('Model')}/range(address='A1:B3')").json()["values"]
print(outputs)

Recalculate computes formulas Excel considers dirty, which is enough almost always. Full recalculates every formula regardless of whether it looks changed, and FullRebuild also rebuilds the dependency chain — the online equivalents of Ctrl+Alt+F9 and Ctrl+Shift+Alt+F9. Use the heavier levels when a model relies on volatile or user-defined behaviour that ordinary recalculation misses, not by default.

Step 4 — Run what-if scenarios without touching the file

A non-persistent session turns a shared workbook into a calculation service. Write a scenario's inputs, recalculate, read the outputs, and let the session end: the file in SharePoint never changes, so the owner's model is never left with somebody's test assumptions in it.

import pandas as pd

scenarios = pd.DataFrame({
    "name": ["base", "price rise", "downturn"],
    "growth": [0.05, 0.05, -0.02],
    "price": [1200, 1320, 1150],
    "churn": [0.18, 0.21, 0.25],
})

results = []
with workbook_session(persist=False):
    for row in scenarios.itertuples(index=False):
        call("PATCH", f"{sheet('Inputs')}/range(address='B2:B4')",
             json={"values": [[row.growth], [row.price], [row.churn]]})
        recalculate()
        values = call("GET", f"{sheet('Model')}/range(address='A1:B3')").json()["values"]
        results.append({"scenario": row.name, **{label: value for label, value in values[1:]}})

print(pd.DataFrame(results))

All three scenarios run inside one session, so the workbook is opened once, and each scenario's inputs simply overwrite the previous one's. Keep scenario runs sequential: the session holds one set of inputs at a time, and parallel requests would read each other's results. If the answers feed a report, write them with the file-level tools in Building Excel Reports with XlsxWriter rather than back into the model.

Step 5 — Handle slow session creation

Opening a large workbook can take long enough for a normal request to time out. Session creation supports Microsoft's long-running operation pattern: send Prefer: respond-async, and the service either answers immediately with 201 Created or returns 202 Accepted with a Location header to poll.

import time

def create_session_async(persist: bool, poll_seconds: int = 30, max_wait: int = 600) -> str:
    response = call("POST", f"{BOOK}/createSession", json={"persistChanges": persist},
                    headers={"Prefer": "respond-async"})
    if response.status_code == 201:
        return response.json()["id"]
    status_url = response.headers["Location"]
    waited = 0
    while waited < max_wait:
        time.sleep(poll_seconds)
        waited += poll_seconds
        status = call("GET", status_url).json()
        if status["status"] == "succeeded":
            return call("GET", status["resourceLocation"]).json()["id"]
        if status["status"] == "failed":
            raise RuntimeError(f"session creation failed: {status.get('error')}")
    raise TimeoutError("workbook did not open in time")

print(create_session_async(persist=False))

Microsoft suggests polling roughly every 30 seconds and never waiting more than four minutes between polls. The operation's status moves through notStarted and running to succeeded or failed, and a successful operation points to the session information through resourceLocation.

Step 6 — Keep a session alive through a long job

A job that interleaves workbook calls with slow work of its own — a database query between scenarios, a large pandas transform before writing results — can outlast the session's idle timeout. Rather than guessing how long the pause will be, refresh the session whenever enough time has passed since the last workbook request. refreshSession takes the same session header and has no body to speak of.

REFRESH_AFTER = 120          # seconds; comfortably inside the idle timeout

class SessionKeeper:
    def __init__(self):
        self.last = time.monotonic()

    def touch(self) -> None:
        self.last = time.monotonic()

    def keep_alive(self) -> None:
        if time.monotonic() - self.last > REFRESH_AFTER:
            call("POST", f"{BOOK}/refreshSession", json={})
            self.touch()

keeper = SessionKeeper()
with workbook_session(persist=True):
    for batch in range(3):
        keeper.keep_alive()                                   # before each workbook call
        call("GET", f"{sheet('Model')}/range(address='A1:B3')")
        keeper.touch()
        # ... slow local work between workbook calls ...
print("finished with the session still valid")

Calling keep_alive before each workbook request, rather than on a timer thread, keeps the logic single-threaded and makes the refresh happen exactly when it is needed. If a refresh itself fails because the session has already expired, the context manager's closeSession will fail too; catch that, create a new session and rewrite the inputs, which is safe because a non-persistent session never saved anything in the first place.

Common pitfalls

Performance and scale notes

A session removes the cost of opening the workbook on every call, which is the dominant cost for large files, so a ten-call job in a session is usually several times faster than the same calls sessionless. Calculation time depends entirely on the model; measure one Recalculate and budget scenarios from that. Microsoft recommends sequential rather than concurrent requests against a single workbook, and heavy use brings throttling responses with a Retry-After header — honour it, as in Append Rows to an Excel Online Table with Microsoft Graph.

Conclusion

Wrap any multi-call workbook job in a session, closed reliably by a context manager. Choose persistChanges: true when the job should update the file and false when it only needs answers — the non-persistent session is what makes a shared Excel model safe to use as a calculation service. Call application/calculate with Recalculate before reading outputs from a manually calculated workbook, escalate to Full only when results look stale, and use the asynchronous session pattern for workbooks that are slow to open.

Frequently asked questions

Do I need a session to call calculate? No, but without one the workbook is opened, recalculated, saved and closed in a single request, and any inputs you wrote in an earlier sessionless call have already been saved. Sessions make the sequence predictable.

How long does a workbook session last? Sessions expire after a few minutes without activity. Call refreshSession to keep one alive during a pause, or create a new session when work resumes.

Will people with the file open see my what-if inputs? Not with a non-persistent session, whose changes are never saved. With a persistent session they see changes as they are written, just like co-authoring.

Can I evaluate a single Excel function without a model? Yes. The workbook functions endpoints evaluate individual worksheet functions such as VLOOKUP or PMT, though for most calculations pandas is simpler once the data is in Python.

Related

• Up one level: Automating Excel Online with Microsoft Graph — the full workbook API workflow.
• Read and Write Excel Online Ranges with Microsoft Graph — the range reads and writes used in each scenario.
• Recalculate Excel Formulas Without Excel in Python — local alternatives for files on disk.
• Refresh Excel Data Connections and Recalculate with win32com — the desktop Excel equivalent.
• Authenticate to Microsoft Graph with MSAL for Excel Automation — the delegated token these calls require.