Authenticate to Microsoft Graph with MSAL for Excel Automation

Every Microsoft Graph call needs a bearer token, and for Excel automation the kind of token matters more than usual. Graph has two quite different ways to touch a workbook in OneDrive or SharePoint. The file endpoints download and upload the .xlsx as bytes, and they work with an app-only token from a client secret. The workbook endpoints — reading and writing ranges, appending table rows, recalculating — operate on the workbook inside Excel's online service, and Microsoft's reference pages list their application permissions as not supported: they need a token issued on behalf of a signed-in user. This guide, part of Automating Excel Online with Microsoft Graph, sets up both, including the token cache that lets a delegated job run on a schedule.

Prerequisites

pip install msal requests

You also need an app registration in Microsoft Entra ID (the tenant's admin centre, under App registrations):

• Delegated access — add the Microsoft Graph delegated permission Files.ReadWrite (or Files.ReadWrite.All if the job must reach files other people shared rather than the signed-in user's own), and under Authentication enable Allow public client flows so the device code flow is permitted.
• App-only access — add the application permission you need for file content, such as Sites.Selected or Files.Read.All, grant admin consent, and create a client secret or upload a certificate.

Keep the tenant ID, client ID and any secret in environment variables, never in the script. The examples read GRAPH_TENANT_ID, GRAPH_CLIENT_ID and, for app-only, GRAPH_CLIENT_SECRET.

Step 1 — Sign in once with the device code flow

The device code flow suits scripts and servers because it needs no browser on the machine running the code. MSAL asks Entra ID for a short code, prints a message telling a person to open a sign-in page on any device and type it, and waits until they have done so.

import os
import msal

TENANT_ID = os.environ.get("GRAPH_TENANT_ID", "contoso.onmicrosoft.com")
CLIENT_ID = os.environ.get("GRAPH_CLIENT_ID", "00000000-0000-0000-0000-000000000000")
AUTHORITY = f"https://login.microsoftonline.com/{TENANT_ID}"
SCOPES = ["Files.ReadWrite"]

def sign_in_interactively(app: msal.PublicClientApplication) -> dict:
    flow = app.initiate_device_flow(scopes=SCOPES)
    if "user_code" not in flow:
        raise RuntimeError(f"could not start device flow: {flow.get('error_description')}")
    print(flow["message"])            # "To sign in, use a web browser to open … and enter the code …"
    return app.acquire_token_by_device_flow(flow)

Request only the resource scopes you need. MSAL adds openid, profile and offline_access itself, and offline_access is what makes Entra ID return a refresh token — the piece that lets later runs happen without a person.

Step 2 — Persist the token cache between runs

By default MSAL keeps tokens in memory, so a new process would need a new sign-in. A SerializableTokenCache written to a file keeps the account and its refresh token, and acquire_token_silent then returns a valid access token — refreshing it behind the scenes when the old one has expired — without any prompt.

import json
import stat
from pathlib import Path

CACHE_PATH = Path(os.environ.get("GRAPH_TOKEN_CACHE", ".graph_token_cache.json"))

def load_cache() -> msal.SerializableTokenCache:
    cache = msal.SerializableTokenCache()
    if CACHE_PATH.exists():
        cache.deserialize(CACHE_PATH.read_text())
    return cache

def save_cache(cache: msal.SerializableTokenCache) -> None:
    if cache.has_state_changed:
        CACHE_PATH.write_text(cache.serialize())
        CACHE_PATH.chmod(stat.S_IRUSR | stat.S_IWUSR)     # readable by this user only

def get_delegated_token(interactive: bool = False) -> str:
    cache = load_cache()
    app = msal.PublicClientApplication(CLIENT_ID, authority=AUTHORITY, token_cache=cache)
    result = None
    accounts = app.get_accounts()
    if accounts:
        result = app.acquire_token_silent(SCOPES, account=accounts[0])
    if not result:
        if not interactive:
            raise RuntimeError("no cached Graph sign-in; run once with interactive=True")
        result = sign_in_interactively(app)
    save_cache(cache)
    if "access_token" not in result:
        raise RuntimeError(f"token request failed: {result.get('error')}: {result.get('error_description')}")
    return result["access_token"]

The interactive flag is the detail that keeps scheduled jobs healthy. A cron job must never sit waiting for somebody to type a device code at 06:00; it should fail immediately with a message that says what to do. Run the script once by hand with interactive=True to seed the cache, then schedule it.

Step 3 — Treat the cache file as a credential

The cache holds a refresh token that can obtain new access tokens for the signed-in account, so it deserves the same care as a password. The chmod above restricts it to the owning user on Linux and macOS. On a shared server, keep it on an encrypted volume or use a platform keyring through the msal-extensions package, which stores the cache with the operating system's protection.

def describe_cache() -> dict:
    cache = load_cache()
    app = msal.PublicClientApplication(CLIENT_ID, authority=AUTHORITY, token_cache=cache)
    return {
        "cache_file": str(CACHE_PATH),
        "accounts": [a.get("username") for a in app.get_accounts()],
        "private": CACHE_PATH.exists() and oct(CACHE_PATH.stat().st_mode & 0o777) == "0o600",
    }

print(describe_cache())

Use a dedicated account for the job rather than a person's own. A service account whose password is not rotated by a departing employee — and whose access is limited to the report library — is much easier to reason about. Check it against your organisation's conditional access policies: a policy that requires a compliant device or periodic re-authentication will eventually force a new interactive sign-in, and it is better to know that schedule in advance.

Step 4 — Get an app-only token for file content

Downloading a workbook to read with pandas, or uploading a finished report, uses the file endpoints, which accept application permissions. There is no user and no cache to maintain: a confidential client exchanges its secret for a token on every run, and MSAL caches it in memory for the life of the process.

def get_app_token() -> str:
    app = msal.ConfidentialClientApplication(
        CLIENT_ID,
        authority=AUTHORITY,
        client_credential=os.environ.get("GRAPH_CLIENT_SECRET", "set-me"),
    )
    result = app.acquire_token_for_client(scopes=["https://graph.microsoft.com/.default"])
    if "access_token" not in result:
        raise RuntimeError(f"app token failed: {result.get('error')}: {result.get('error_description')}")
    return result["access_token"]

The .default scope means "whatever application permissions an administrator consented to for this app"; you cannot request individual application permissions at token time. The same pattern is used for publishing in Upload an Excel Report to SharePoint with Python, where Sites.Selected limits the app to one site.

Step 5 — Verify the token before doing real work

A token that was issued is not necessarily a token that works for your file. Make one cheap call at the start of the job and turn the common failures into messages a person can act on.

import requests

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

def check_delegated(token: str) -> str:
    response = requests.get(f"{GRAPH}/me", headers={"Authorization": f"Bearer {token}"}, timeout=30)
    if response.status_code == 401:
        raise RuntimeError("token rejected — expired or for the wrong tenant; re-run the interactive sign-in")
    if response.status_code == 403:
        raise RuntimeError("signed in, but consent for the requested permissions is missing")
    response.raise_for_status()
    return response.json()["userPrincipalName"]

/me only exists for delegated tokens, which makes it a neat check that the job is not accidentally using the app-only path for workbook calls. A workbook endpoint called with an app-only token fails with an error even though the same token downloads the file perfectly — a confusing combination unless you know the rule from the start of this page.

token = get_delegated_token()
print("signed in as", check_delegated(token))

Common pitfalls

Performance and scale notes

Silent token acquisition from a warm cache is a local operation until the access token expires, typically after about an hour; then one refresh request is made. Create the MSAL application object once per process and reuse it, so that its in-memory cache avoids repeated file reads. For many workbooks in one run, reuse a single requests.Session with the Authorization header set, and refresh the header only when a call returns 401.

Conclusion

Pick the token by the endpoint. File content — downloading a workbook for pandas, uploading a finished report — works with an app-only token from client credentials. The workbook API that edits ranges, appends table rows and recalculates needs a delegated token, so use MSAL's device code flow once, persist its token cache as a protected credential, and acquire tokens silently on every scheduled run, failing fast with a clear message when the cached sign-in is gone. Verify with a cheap call before doing real work, and use a dedicated account whose access is limited to the files the job needs.

Frequently asked questions

Why can't I use client credentials for the Excel workbook API? Microsoft's permission tables for the workbook endpoints list application permissions as not supported. Those calls act on a workbook session on behalf of a user, so they need a delegated token.

How long does a cached sign-in last? Access tokens last about an hour and are refreshed automatically. The refresh token behind them is long-lived but can be revoked by a password reset, conditional access policy, administrator action or a long period without use, so monitor for silent-acquisition failures.

Can I use a username and password instead of the device code? MSAL supports the resource owner password flow, but Microsoft discourages it and it fails for accounts with multi-factor authentication. The device code flow with a cached token is the more robust choice.

Do I need admin consent for Files.ReadWrite? Usually not for the delegated Files.ReadWrite permission, which users can consent to themselves unless the tenant restricts user consent. Application permissions always need an administrator.

Related

• Up one level: Automating Excel Online with Microsoft Graph — the full workflow for workbooks in OneDrive and SharePoint.
• Read an Excel File from SharePoint or OneDrive into pandas — the app-only file path in use.
• Read and Write Excel Online Ranges with Microsoft Graph — the first delegated workbook calls.
• Send an Excel Report with the Microsoft Graph API — another Graph workload with its own permissions.
• Keep Excel Report Settings in a Config File — keeping IDs and paths out of code.