Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Call Excel Worksheet Functions Through Microsoft Graph

Use Excel's own calculation engine from Python without Excel installed — call worksheet functions such as PMT and VLOOKUP on a workbook in OneDrive or SharePoint through Microsoft Graph, pass values and ranges as arguments, read results and Excel errors, and reuse a session for many calls.
Excel Online & Microsoft GraphFormulas in workbooks

Some calculations are defined by what Excel does. A loan schedule must match the PMT figure the finance team sees in their workbook; a working-day count must follow NETWORKDAYS.INTL exactly; a lookup must use the table maintained in a SharePoint workbook. Reimplementing those in Python works until an edge case differs. If the workbook lives in OneDrive for Business or SharePoint, Microsoft Graph can evaluate Excel worksheet functions for you, in the cloud, with Excel's own engine. This guide, part of Automating Excel Online with Microsoft Graph, shows the request shape, how to pass values and ranges, how to read results and errors, and how to batch many calls into one session.

One worksheet function call through Graph The Python script posts the function arguments to the workbook functions endpoint of a workbook in SharePoint. Graph hands the call to the Excel calculation service, which evaluates it against the workbook and returns a JSON body with the value and an error field. Python script Microsoft Graph Excel service 1. POST …/workbook/functions/pmt 2. evaluate PMT(rate, nper, pv) 3. result or #error 4. {"value": …, "error": null}

Prerequisites

Bash
pip install requests msal

You need an access token with Files.ReadWrite (or Files.Read for pure lookups) and the drive and item IDs of a workbook in OneDrive for Business or SharePoint; getting both is covered in Authenticate to Microsoft Graph with MSAL for Excel Automation. Personal OneDrive accounts do not support the workbook API. The examples read the token and IDs from the environment:

Python
import os

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["GRAPH_TOKEN"]

Step 1 — Call a function with plain values

Each worksheet function has an endpoint under /workbook/functions/, named after the function in lower camel case. The body is a JSON object whose keys are the function's argument names — PMT(rate, nper, pv) takes rate, nper and pv:

Python
def excel_function(name, **arguments):
    response = http.post(f"{BOOK}/functions/{name}", json=arguments, timeout=30)
    response.raise_for_status()
    body = response.json()
    if body.get("error"):
        raise ValueError(f"{name} returned {body['error']}")
    return body["value"]

monthly = excel_function("pmt", rate=0.05 / 12, nper=36, pv=-25000)
print(round(monthly, 2))

The result is Excel's own PMT: the monthly repayment on a 25,000 loan over three years at 5% a year. The endpoint does not change the workbook; it evaluates the function in the workbook's context, which matters for functions that read ranges, as in the next step. Argument names follow Microsoft's documentation of each function — optional ones such as fv and type for PMT can simply be left out.

Step 2 — Pass ranges from the workbook

Functions such as VLOOKUP, SUMIFS or XIRR take ranges. Pass a range as an object with an Address, and Excel reads the cells from the workbook:

Python
rate = excel_function(
    "vlookup",
    lookupValue="EUR",
    tableArray={"Address": "Rates!A2:C20"},
    colIndexNum=2,
    rangeLookup=False,
)
print(rate)

This is where Graph earns its place: the lookup uses the rate table as it is in SharePoint right now, maintained by whoever owns it, without downloading the workbook. Values and addresses can be mixed freely — a literal lookupValue with a range for tableArray, as here. Reading ranges directly, rather than through a function, is covered in Read and Write Excel Online Ranges with Microsoft Graph.

How arguments are written in the request body A number or text argument is written as a plain JSON value. A range is an object with an Address such as Rates A2 to C20. An array of values is a JSON array. Optional arguments are simply left out. Argument names are the Excel argument names in lower camel case. JSON in the body number or text "rate": 0.004167 range in the workbook "tableArray": {"Address": "Rates!A2:C20"} list of values "values": [-1000, 300, 400] optional argument leave it out

Step 3 — Handle Excel errors as results

A function can succeed as an HTTP request and still return an Excel error — #N/A for a lookup that finds nothing, #NUM! for an impossible calculation. Graph reports these in the error field with a successful status code. Decide per call whether an error is exceptional or an expected outcome:

Python
def excel_function_or_error(name, **arguments):
    response = http.post(f"{BOOK}/functions/{name}", json=arguments, timeout=30)
    response.raise_for_status()
    body = response.json()
    return body.get("value"), body.get("error")

value, error = excel_function_or_error(
    "vlookup", lookupValue="XYZ", tableArray={"Address": "Rates!A2:C20"}, colIndexNum=2, rangeLookup=False)
if error == "#N/A":
    print("currency not in the rate table")

HTTP-level failures — an expired token, a missing file, throttling — still raise through raise_for_status. Keeping the two kinds apart means a missing currency becomes a line in a report, while a broken connection stops the job. Throttling in particular deserves its own handling; see Handle Microsoft Graph Throttling in Excel Automation.

Step 4 — Many calls in one session

Every call without a session makes the service open the workbook afresh. For a loop of hundreds of lookups, create a non-persistent session once and send its ID with each request; the service then keeps the workbook loaded between calls:

Python
created = http.post(f"{BOOK}/createSession", json={"persistChanges": False}, timeout=30)
created.raise_for_status()
http.headers["workbook-session-id"] = created.json()["id"]
try:
    rates = {code: excel_function_or_error("vlookup", lookupValue=code,
                                           tableArray={"Address": "Rates!A2:C20"},
                                           colIndexNum=2, rangeLookup=False)[0]
             for code in ["EUR", "GBP", "USD"]}
finally:
    http.post(f"{BOOK}/closeSession", timeout=30)
    http.headers.pop("workbook-session-id", None)
print(rates)

persistChanges: False guarantees nothing is written back to the file, which suits read-only function calls. Close the session when done; sessions also expire on their own after a period of inactivity. Sessions and recalculation are covered in depth in Recalculate Excel Online Workbooks with Graph Sessions. For very large numbers of lookups, one call that reads the whole rate table and a Python dictionary lookup is faster still — use functions for what Python should not reimplement.

When a function is not exposed

The functions endpoint covers several hundred worksheet functions, but not everything Excel offers; check Microsoft's list of workbook functions before relying on a particular one, especially functions added to Excel recently. The fallback is to let Excel evaluate a formula in a cell: open a non-persistent session, write the formula into an unused cell, read back its value, and close the session so nothing is saved:

Python
created = http.post(f"{BOOK}/createSession", json={"persistChanges": False}, timeout=30)
http.headers["workbook-session-id"] = created.json()["id"]
try:
    cell = f"{BOOK}/worksheets('Scratch')/range(address='A1')"
    http.patch(cell, json={"formulas": [['=TEXTJOIN(", ",TRUE,Rates!A2:A6)']]}, timeout=30).raise_for_status()
    joined = http.get(cell, timeout=30).json()["values"][0][0]
finally:
    http.post(f"{BOOK}/closeSession", timeout=30)
    http.headers.pop("workbook-session-id", None)
print(joined)

The scratch sheet must exist in the workbook — add a hidden one for the purpose — and because the session does not persist changes, the formula disappears when the session closes. This route evaluates anything Excel itself can, at the cost of two requests instead of one.

Step 5 — Check the result once in Python

For well-defined functions, a local cross-check catches mistakes in argument names or signs before they reach a report. PMT has a closed form:

Python
def pmt(rate, nper, pv, fv=0.0, when=0):
    if rate == 0:
        return -(pv + fv) / nper
    factor = (1 + rate) ** nper
    return -(rate * (pv * factor + fv)) / ((1 + rate * when) * (factor - 1))

print(round(pmt(0.05 / 12, 36, -25000), 2))           # 749.27

A spreadsheet's PMT for the same arguments — checked here in LibreOffice — returns 749.27 as well. The sign convention is Excel's: a loan received is a negative present value, so the payment comes out positive. When a local implementation and the Graph result agree for a handful of cases, the call is set up correctly; when they disagree, the argument names or signs are usually to blame.

Excel engine through Graph or plain Python Use Graph functions when the result must match Excel exactly or depends on ranges maintained in a SharePoint workbook. Compute in Python when the logic is simple, the volume is high or the job must run without a network connection. What does the calculation depend on? must match Excel exactly Graph function financial, date rules shared workbook ranges Graph function Address arguments simple logic, high volume Python read the data once offline job Python no network calls

Common pitfalls

  • Personal OneDrive. The workbook API works on OneDrive for Business and SharePoint only; personal accounts return errors.
  • Wrong argument names. Names are the Excel argument names in lower camel case — lookupValue, not lookup_value. An unknown name is rejected with a 400 response that names the problem.
  • Tokens that expire mid-loop. Access tokens last about an hour. For long runs, get a fresh token from MSAL's cache before each batch instead of once at start-up.
  • Assuming the session saved something. A non-persistent session writes nothing back; results must be read from the responses, not from the file afterwards.
  • One call per row. Hundreds of sequential function calls are slow and invite throttling. Use a session, and move bulk logic to Python where exactness does not require Excel.

Conclusion

Microsoft Graph lets Python use Excel's own calculation engine on workbooks in OneDrive for Business or SharePoint: POST the arguments to /workbook/functions/{name} with Excel's argument names in lower camel case, pass ranges as {"Address": …}, read value and treat error as an Excel result rather than a failure. Wrap repeated calls in a non-persistent session, cross-check simple functions locally once, and keep high-volume logic in Python.

Frequently asked questions

Can I run an Excel function without having Excel installed? Yes, if the workbook is stored in OneDrive for Business or SharePoint. Microsoft Graph exposes Excel's calculation engine through POST /workbook/functions/{name}, which evaluates the function in the cloud and returns the result as JSON.

How do I pass a cell range to a Graph function call? Pass an object with an Address property, such as {"Address": "Rates!A2:C20"}. Plain values and arrays of values are passed directly.

What does the response look like? A JSON object with a value field holding the result and an error field that is null on success, or holds the Excel error such as "#N/A" when the function itself returns an error.

Why use Graph instead of calculating in Python? When a result must match exactly what Excel computes — financial functions, date arithmetic with Excel's conventions, or lookups against a maintained workbook — using Excel's engine avoids reimplementing its rules and edge cases.

More on excel online & microsoft graph

More on excel online & microsoft graph →