Copy a Template Workbook in SharePoint with Microsoft Graph

Teams that standardise on a workbook template usually keep it in SharePoint: a project tracker, a monthly close checklist, a budget return with locked formulas and approved formatting. Automation then needs to stamp out copies — one per project, client or month — named consistently, saved in the right folder, with a few cells filled in. Downloading the template, editing it locally and uploading it again works, but it moves bytes through your script for no reason and can lose features your Python library does not understand. Microsoft Graph can copy the file inside SharePoint instead. This page finds the template by path, starts the server-side copy, waits for the asynchronous operation to finish, handles a copy whose name already exists, and fills in the new workbook through the workbook API. It belongs to Automating Excel Online with Microsoft Graph.

Prerequisites

pip install requests

You need a Microsoft Graph access token with Files.ReadWrite.All or Sites.ReadWrite.All (or Sites.Selected granted on the site), and the drive ID of the document library holding the template; Authenticate to Microsoft Graph with MSAL for Excel Automation covers the token, and Read an Excel File from SharePoint or OneDrive into pandas shows how to look up a site's document library and its drive ID. The outputs below were produced against a mocked Graph API (the responses library) that returns the documented response shapes, including the asynchronous copy; the code is unchanged against a real tenant.

Step 1 — Find the template and the destination folder

Address both by path, so the script reads like the library's folder structure:

import os, time
import requests
from urllib.parse import quote

GRAPH = "https://graph.microsoft.com/v1.0"
DRIVE = "b!projects-drive"
session = requests.Session()
session.headers["Authorization"] = f"Bearer {os.environ['GRAPH_TOKEN']}"

def by_path(path):
    r = session.get(f"{GRAPH}/drives/{DRIVE}/root:/{quote(path)}", timeout=60)
    r.raise_for_status()
    return r.json()

template = by_path("Templates/Project tracker template.xlsx")
folder = by_path("Projects/2026")
print("template:", template["id"], template["name"], f"({template['size']:,} bytes)")
print("destination folder:", folder["id"], folder["name"])

Keep the template in a library or folder where only its owners have edit rights and everyone else can read; the automation then needs read access to the template and write access to the destination, and nobody edits the master by accident when they meant to fill in a copy. Version history in SharePoint records every change to the template itself, so a broken formula introduced last week can be traced and rolled back.

Resolving paths to IDs once, at the start, gives a clear error if someone has renamed the template or the folder — a 404 here is far easier to understand than one in the middle of a copy. The template and the destination can be in different drives or even different sites; the copy request below says where to put the copy with a parentReference holding both the destination drive ID and folder ID.

Step 2 — Start the copy

A copy is a POST to the template's copy action with the destination and the new name. Graph does not do the copy during the request — it answers 202 Accepted with a Location header pointing at a monitor URL:

def start_copy(new_name, conflict="fail"):
    body = {"parentReference": {"driveId": DRIVE, "id": folder["id"]}, "name": new_name}
    r = session.post(f"{GRAPH}/drives/{DRIVE}/items/{template['id']}/copy",
                     params={"@microsoft.graph.conflictBehavior": conflict}, json=body, timeout=60)
    if r.status_code == 409:
        raise FileExistsError(new_name)
    r.raise_for_status()
    return r.headers["Location"]

monitor_url = start_copy("Project PX-124 tracker.xlsx")
print("202 accepted; monitor:", monitor_url)
try:
    start_copy("Project PX-118 tracker.xlsx")
except FileExistsError as e:
    print("already exists, not copied:", e)

Asking for conflictBehavior=fail is a safe default for template copies: if a tracker for that project already exists, someone may have filled it in, and overwriting it with a blank template would be a serious loss. rename keeps both (adding a number to the new name) and replace overwrites; choose either deliberately. The copy starts with a version history of its own — the template's past versions are not carried over — so the history of each project's workbook begins on the day it was created, which is usually what auditors and owners expect.

Name the copies from data — project code, client, period — so the same input always produces the same file name and a rerun refuses rather than duplicates. The approach is discussed in Make an Excel Report Job Safe to Rerun.

Step 3 — Poll the monitor until the copy completes

The monitor URL reports progress. It is pre-authenticated and not a Graph endpoint, so call it without the bearer token — a plain requests.get — and wait between calls:

def wait_for_copy(url, timeout_s=120, interval=0.2):
    deadline = time.monotonic() + timeout_s
    while time.monotonic() < deadline:
        status = requests.get(url, timeout=30).json()
        print(f"  {status['status']:<11} {status.get('percentageComplete', 0):>5.0f}%")
        if status["status"] == "completed":
            return status["resourceId"]
        if status["status"] == "failed":
            raise RuntimeError(f"copy failed: {status.get('error')}")
        time.sleep(interval)
    raise TimeoutError("copy did not finish in time")

new_id = wait_for_copy(monitor_url)
new_item = session.get(f"{GRAPH}/drives/{DRIVE}/items/{new_id}", timeout=60).json()
print(new_item["name"], new_item["webUrl"])

The status moves through notStarted and inProgress to completed (or failed), and on completion the response includes resourceId, the new item's ID. A short interval works for the mock; against SharePoint, poll every second or two and back off for large files — copies of ordinary workbooks usually finish within a few seconds. Sending the Graph token to the monitor URL can make it reject the request, the same rule as for upload-session URLs. The timeout matters: without one, a stuck copy holds a scheduled job forever.

Step 4 — Fill in the copy without downloading it

With the new item's ID, write the values that make this copy specific — project code, owner, start date — straight into named cells through the workbook API:

from datetime import date

BOOK = f"{GRAPH}/drives/{DRIVE}/items/{new_id}/workbook"
values = [["PX-124"], ["Priya Natarajan"], [date(2026, 10, 5).isoformat()]]
r = session.patch(f"{BOOK}/worksheets('Summary')/range(address='B2:B4')", json={"values": values}, timeout=60)
r.raise_for_status()
print("wrote", patched[-1])

Because the template's formulas, data validation, conditional formatting and protection stay in SharePoint and are never round-tripped through a Python library, nothing is lost — a real advantage over download-edit-upload when the template uses features openpyxl does not preserve, such as slicers or Power Query connections. A date written as an ISO string is parsed by Excel into a date value as if typed, so format the template cell for dates. For more than a handful of cells, write whole ranges in one call rather than one request per cell, and if the template's formulas need recalculating before anyone reads results back, use a workbook session as described in Recalculate Excel Online Workbooks with Graph Sessions.

Keep the cells the script writes in one clearly marked area — a "Summary" or "Setup" sheet with labelled input cells — rather than scattered across the template. Then the template's owner can change layout elsewhere without breaking the automation, and the addresses the script uses can be documented in one place, or better still replaced with named ranges: workbook/names('ProjectCode')/range resolves the address inside the workbook, so moving the cell in the template does not require a code change. If many copies are made in one run, note that each copy and each write counts towards Graph's throttling limits; Handle Microsoft Graph Throttling for Excel covers retrying on HTTP 429 with the Retry-After delay.

Common pitfalls

• Overwriting a filled-in copy. Default to conflictBehavior=fail.
• Treating the 202 as "done". The copy is asynchronous; poll the monitor URL.
• Sending the Graph token to the monitor URL. It is pre-authenticated.
• Polling forever. Set a timeout and handle failed.
• Hard-coding item IDs. Resolve paths at runtime so renames give clear errors.

Conclusion

To create workbooks from a template in SharePoint, resolve the template and destination by path, POST to the template's copy action with a parentReference and the new name, and poll the Location monitor URL — without the Graph token — until the status is completed and you have the new item's ID. Then fill in the copy through the workbook API. The template's features stay intact because the file never leaves SharePoint, and setting the conflict behaviour to fail makes reruns safe.

Frequently asked questions

How do I copy a file in SharePoint with Microsoft Graph? POST to the copy action on the drive item with a parentReference holding the destination drive and folder IDs and an optional new name; Graph returns 202 with a monitor URL.

How do I know when a Graph copy has finished? Poll the URL in the Location header of the 202 response until its status is completed; the response then contains the resourceId of the new item.

Why does polling the copy monitor URL return 401? The monitor URL is pre-authenticated; call it without the Authorization header used for Graph requests.

Can I copy a template to a different SharePoint site? Yes. Set the driveId in parentReference to the destination library's drive ID, which can belong to another site.

Related

• Up one level: Automating Excel Online with Microsoft Graph — Excel in the cloud from Python.
• Create a New Workbook in OneDrive with Microsoft Graph — uploading a workbook built in Python.
• Share an Excel File in OneDrive with a Link Using Graph — sending the copy to people.
• Read and Write Excel Online Ranges with Microsoft Graph — more on the workbook API.