Read an Excel File from SharePoint or OneDrive into pandas

The finance team keeps the source data for your report in a SharePoint document library, and the sales team keeps theirs in someone's OneDrive. Neither is a path your script can open, and asking people to export copies to a network share every morning is how reports end up a day stale. Microsoft Graph turns both locations into HTTP endpoints: resolve the library, ask for the file's content, and hand the bytes to pd.read_excel. This guide, part of Automating Excel Online with Microsoft Graph, covers the whole read path with an app-only token, so it can run unattended.

Prerequisites

pip install msal requests pandas openpyxl

• An app registration with an application permission that covers the file: Sites.Selected with the site granted to the app (the least-privileged choice for SharePoint), or Files.Read.All for broader access including users' OneDrive. Admin consent is required either way.
• An app-only token function such as get_app_token() from Authenticate to Microsoft Graph with MSAL for Excel Automation.

Downloading file content is one of the Graph operations that accepts application permissions, unlike the workbook endpoints that edit ranges and tables. That is what makes this read path suitable for a scheduled job without a signed-in user.

Step 1 — Create a session with the token

Wrap the token in a requests.Session so every call carries the Authorization header and reuses one connection. A small helper that raises on errors with Graph's own message keeps the later steps short.

import os
import msal
import requests

GRAPH = "https://graph.microsoft.com/v1.0"

def get_app_token() -> str:
    app = msal.ConfidentialClientApplication(
        os.environ["GRAPH_CLIENT_ID"],
        authority=f"https://login.microsoftonline.com/{os.environ['GRAPH_TENANT_ID']}",
        client_credential=os.environ["GRAPH_CLIENT_SECRET"],
    )
    result = app.acquire_token_for_client(scopes=["https://graph.microsoft.com/.default"])
    return result["access_token"]

session = requests.Session()
session.headers["Authorization"] = f"Bearer {get_app_token()}"

def graph_get(path: str, **kwargs) -> requests.Response:
    response = session.get(f"{GRAPH}{path}", timeout=60, **kwargs)
    if response.status_code >= 400:
        raise RuntimeError(f"GET {path} failed: {response.status_code} {response.text[:300]}")
    return response

Step 2 — Resolve the site and its document library

SharePoint URLs are for browsers; Graph wants IDs. A site is addressed by its host name and server-relative path, and its default document library (the one called Documents in the UI, Shared Documents in URLs) is the site's drive. For a different library, list drives and pick it by name.

def site_id(hostname: str, site_path: str) -> str:
    return graph_get(f"/sites/{hostname}:/sites/{site_path}").json()["id"]

def drive_id(site: str, library: str | None = None) -> str:
    if library is None:
        return graph_get(f"/sites/{site}/drive").json()["id"]
    drives = graph_get(f"/sites/{site}/drives").json()["value"]
    for drive in drives:
        if drive["name"] == library:
            return drive["id"]
    raise LookupError(f"no library named {library!r}; found {[d['name'] for d in drives]}")

SITE = site_id("contoso.sharepoint.com", "Finance")
DRIVE = drive_id(SITE)
print(SITE, DRIVE)

These IDs are stable, so resolve them once and keep them in configuration rather than looking them up on every run. For a user's OneDrive the drive is simply /users/{user-principal-name}/drive, and everything after this step is identical.

Step 3 — Download the workbook into memory

Address the file by its path under the drive root and append :/content. Graph answers with a redirect to a short-lived download URL, which requests follows automatically, and the body is the .xlsx file itself. Wrap the bytes in BytesIO and pandas reads them exactly as it would a file on disk.

from io import BytesIO
from urllib.parse import quote
import pandas as pd

def read_sharepoint_excel(drive: str, path: str, **read_kwargs) -> pd.DataFrame:
    encoded = quote(path.strip("/"))
    content = graph_get(f"/drives/{drive}/root:/{encoded}:/content").content
    return pd.read_excel(BytesIO(content), **read_kwargs)

sales = read_sharepoint_excel(DRIVE, "Reports/2026/sales.xlsx", sheet_name="Sales")
print(sales.head())

quote matters as soon as a folder or file name contains a space, a # or non-ASCII characters, which SharePoint libraries are full of. The in-memory read is the same technique described for URLs and byte strings in Read an Excel File from a URL or Bytes in Python, and every read_excel option — sheet_name=None, usecols, dtype — passes straight through.

Step 4 — Skip the download when nothing changed

Source workbooks usually change once a day, while a job might run every hour. The file's metadata is a small JSON response that includes its eTag and lastModifiedDateTime; compare the eTag with the one from the last run and only download when it differs.

import json
from pathlib import Path

STATE = Path("sharepoint_state.json")

def changed_since_last_run(drive: str, path: str) -> tuple[bool, dict]:
    meta = graph_get(f"/drives/{drive}/root:/{quote(path)}").json()
    seen = json.loads(STATE.read_text()) if STATE.exists() else {}
    is_new = seen.get(path) != meta["eTag"]
    return is_new, meta

def remember(path: str, meta: dict) -> None:
    seen = json.loads(STATE.read_text()) if STATE.exists() else {}
    seen[path] = meta["eTag"]
    STATE.write_text(json.dumps(seen, indent=2))

is_new, meta = changed_since_last_run(DRIVE, "Reports/2026/sales.xlsx")
if is_new:
    sales = read_sharepoint_excel(DRIVE, "Reports/2026/sales.xlsx", sheet_name="Sales")
    remember("Reports/2026/sales.xlsx", meta)
print(meta["lastModifiedDateTime"], "changed" if is_new else "unchanged")

Record the eTag only after the downstream work has succeeded. If the report build fails halfway, the next run should see the file as new and try again rather than skipping it.

Step 5 — Read every workbook in a folder

Monthly or per-branch files usually share a folder. List its children, keep the .xlsx files, and read each into a frame tagged with its source name. Graph pages long listings with @odata.nextLink, so follow it until it runs out.

def list_folder(drive: str, folder: str) -> list[dict]:
    items, url = [], f"/drives/{drive}/root:/{quote(folder)}:/children"
    while url:
        page = graph_get(url).json()
        items.extend(page.get("value", []))
        next_link = page.get("@odata.nextLink")
        url = next_link.replace(GRAPH, "") if next_link else None
    return items

frames = []
for item in list_folder(DRIVE, "Reports/2026/branches"):
    if item.get("name", "").lower().endswith(".xlsx"):
        frame = read_sharepoint_excel(DRIVE, f"Reports/2026/branches/{item['name']}")
        frames.append(frame.assign(source=item["name"]))
combined = pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()
print(len(frames), "workbooks,", len(combined), "rows")

Branch files are rarely perfectly consistent, so the combining step benefits from the checks in Combine Multiple Excel Files into One in Python.

Step 6 — Stream large workbooks to a temporary file

response.content holds the entire file in memory, and BytesIO then holds a second reference while pandas parses it. For a typical 5 MB reporting workbook that is irrelevant. For a 300 MB extract on a small container it is the difference between a job that runs and one that is killed. Streaming the download to a temporary file keeps memory flat, and pandas reads the file from disk as usual.

import tempfile

def read_large_sharepoint_excel(drive: str, path: str, **read_kwargs) -> pd.DataFrame:
    url = f"{GRAPH}/drives/{drive}/root:/{quote(path.strip('/'))}:/content"
    with tempfile.NamedTemporaryFile(suffix=".xlsx") as tmp:
        with session.get(url, stream=True, timeout=300) as response:
            response.raise_for_status()
            for chunk in response.iter_content(chunk_size=1024 * 1024):
                tmp.write(chunk)
        tmp.flush()
        return pd.read_excel(tmp.name, **read_kwargs)

big = read_large_sharepoint_excel(DRIVE, "Reports/2026/sales.xlsx", sheet_name="Sales")
print(big.shape)

The temporary file is deleted when the with block ends, so nothing is left behind on the worker. If the parse itself is the bottleneck rather than the download, combine this with a faster engine — engine="calamine" — or read only the columns you need with usecols, as in Working with Large Excel Files in Python. On Windows, open the temporary file with delete=False and remove it yourself, because a file held open by one handle cannot be reopened by pandas there.

Common pitfalls

Performance and scale notes

The download is a single streamed response, so the cost is the file size and the network. Reading into BytesIO is fine for typical reporting workbooks; for files in the hundreds of megabytes, stream to a temporary file with stream=True and iter_content instead of holding the whole response in memory. When a job reads many files, run the downloads sequentially and respect any Retry-After header on a 429 response — SharePoint throttles bursts from one app, and a polite client finishes sooner than one that retries immediately.

Conclusion

Reading a SharePoint or OneDrive workbook is three lookups and a download: resolve the site, resolve the library's drive, and request the file's :/content by path, then give the bytes to pd.read_excel through BytesIO. An app-only token is enough, because file content supports application permissions. Cache the site and drive IDs, check the eTag before downloading, follow paging when reading a folder, and record progress only after the report built on the data has succeeded.

Frequently asked questions

Can I read a SharePoint Excel file without downloading it? You can read cell values through the workbook API instead, but it needs a delegated token and a call per range. For loading a whole sheet into pandas, downloading the file is simpler and faster.

How do I find the site path from a SharePoint URL? For a library URL on contoso.sharepoint.com under /sites/Finance/, the host name is contoso.sharepoint.com and the site path is Finance. Everything after the library name is the file path under the drive root.

Does this work for files shared with me from another tenant? Not with an app-only token from your tenant. Cross-tenant sharing is granted to users, so it needs a delegated token for an account that has been given access.

Can pandas read the file directly from the download URL? The redirect target is a pre-authenticated, short-lived URL, so pandas could fetch it, but going through requests keeps error handling, timeouts and retries in one place.

Related

• Up one level: Automating Excel Online with Microsoft Graph — reading, editing and recalculating workbooks in Microsoft 365.
• Authenticate to Microsoft Graph with MSAL for Excel Automation — the tokens used above.
• Upload an Excel Report to SharePoint with Python — the write direction for finished reports.
• Read an Excel File Directly from Amazon S3 with pandas — the same pattern for S3.
• Read and Write Excel Online Ranges with Microsoft Graph — editing the workbook in place instead of downloading it.