Add, Rename and Delete Worksheets with Microsoft Graph

Workbooks that live in OneDrive or SharePoint and are updated by automation often change shape over time: a new sheet for each month, last month's Current sheet renamed to its period, sheets older than a year hidden or removed. Downloading the file, changing it with openpyxl and uploading it again would work, but it risks overwriting edits people make in Excel for the web in the meantime and drops features openpyxl does not support. Microsoft Graph's workbook API changes the sheets in place, the same way a person would in the browser. This page lists a workbook's worksheets, adds one, renames, reorders and hides sheets, deletes old ones, groups the changes in a workbook session, validates names against Excel's rules before sending them, and puts it all together as a monthly rotation job — tested against a mocked Graph API so the requests can be checked without a tenant. It belongs to Automating Excel Online with Microsoft Graph.

Prerequisites

pip install requests responses

You need a Graph token with Files.ReadWrite (or Files.ReadWrite.All for application access) and the drive and item IDs of the workbook; getting a token is covered in Authenticate to Microsoft Graph with MSAL for Excel Automation. The outputs on this page come from a mock of the Graph workbook API built with the responses library that keeps a list of sheets and applies each request to it, so the sequence of calls and their effect can be shown and checked without a tenant.

Step 1 — A small client with a workbook session

import os
import requests
from urllib.parse import quote

GRAPH = "https://graph.microsoft.com/v1.0"
BOOK = f"{GRAPH}/drives/b!reports-drive/items/01TRACKER/workbook"

class Workbook:
    def __init__(self, token):
        self.s = requests.Session()
        self.s.headers["Authorization"] = f"Bearer {token}"

    def __enter__(self):
        r = self.s.post(f"{BOOK}/createSession", json={"persistChanges": True}, timeout=60)
        r.raise_for_status()
        self.s.headers["workbook-session-id"] = r.json()["id"]
        return self

    def __exit__(self, *exc):
        self.s.post(f"{BOOK}/closeSession", timeout=60)

    def call(self, method, path, **kw):
        r = self.s.request(method, f"{BOOK}/{path}", timeout=60, **kw)
        if r.status_code >= 400:
            err = r.json()["error"]
            raise RuntimeError(f"{method} {path}: {err['code']}: {err['message']}")
        return r.json() if r.content else None

    def sheets(self):
        return self.call("GET", "worksheets")["value"]

    def ref(self, name):
        return "worksheets/" + quote(f"'{name}'")

with Workbook(os.environ["GRAPH_TOKEN"]) as wb:
    print([(s["position"], s["name"]) for s in wb.sheets()])

A workbook session — createSession with persistChanges: True — groups the following requests on the server: Excel for the web opens the workbook once instead of for every call, which is faster, and the changes are saved together. Every request in the session carries the workbook-session-id header. Worksheets can be addressed by ID or by name; names are put in single quotes and URL-encoded, so 2026-09 or a name with spaces is safe in the path. The tracker workbook starts with an Instructions sheet, a Current sheet and twelve monthly sheets from September 2025 to August 2026. Sessions in more depth are covered in Recalculate Excel Online Workbooks with Graph Sessions.

Step 2 — Validate names before sending them

Excel restricts sheet names, and Graph rejects invalid ones. Checking locally gives a clearer error and saves a round trip:

import re

def check_sheet_name(name, existing):
    problems = []
    if not 1 <= len(name) <= 31:
        problems.append("must be 1–31 characters")
    if re.search(r"[\[\]:*?/\\]", name):
        problems.append("may not contain [ ] : * ? / \\")
    if name.startswith("'") or name.endswith("'"):
        problems.append("may not start or end with an apostrophe")
    if name.lower() == "history":
        problems.append("'History' is reserved")
    if name.lower() in {n.lower() for n in existing}:
        problems.append("already exists (names are case-insensitive)")
    return problems

existing = ["Instructions", "Current", "2026-08"]
for candidate in ["2026-09", "Sales: Sept", "current", "A very long sheet name for September 2026"]:
    print(f"{candidate!r:<46} {check_sheet_name(candidate, existing) or 'ok'}")

Sheet names are limited to 31 characters, may not contain [ ] : * ? / \, may not begin or end with an apostrophe, and must be unique ignoring case — current clashes with Current. History is reserved by Excel. The same rules apply to sheets created with openpyxl, as covered in Fix Invalid Worksheet Name Errors.

Step 3 — Add, rename, move, hide and delete

with Workbook(os.environ["GRAPH_TOKEN"]) as wb:
    names = [s["name"] for s in wb.sheets()]
    assert not check_sheet_name("2026-09", names)
    wb.call("PATCH", wb.ref("Current"), json={"name": "2026-09"})                 # last month's working sheet keeps its period
    new = wb.call("POST", "worksheets/add", json={"name": "Current"})            # a fresh working sheet
    wb.call("PATCH", wb.ref("Current"), json={"position": 1})                     # straight after Instructions
    wb.call("PATCH", wb.ref("2025-09"), json={"visibility": "Hidden"})            # older than a year: hide
    try:
        wb.call("POST", "worksheets/add", json={"name": "Current"})
    except RuntimeError as e:
        print("second add:", e)
    print([(s["position"], s["name"], s["visibility"][0]) for s in wb.sheets()][:5], "…")

Renaming is a PATCH with the new name; the sheet keeps its content, formatting and any formulas pointing at it, which Excel updates to the new name. worksheets/add creates a sheet at the end, and a second PATCH with position moves it — positions count from zero, so 1 is straight after Instructions. visibility takes Visible, Hidden or VeryHidden; hidden sheets are kept, with all their data, out of the way of readers. Adding a sheet whose name already exists fails with an error object carrying a code and a message — the mock returns one in Graph's error format — which the client turns into one readable line. The renamed 2026-09 sheet stays where Current used to be, now just after the new Current; if the monthly sheets should be in date order, one more PATCH with a position files it where it belongs.

Step 4 — A monthly rotation job

Put it together: once a month, keep last month's sheet under its period name, start a fresh Current sheet, hide sheets older than a year and delete those older than two:

from datetime import date

def months_old(name, today):
    y, m = map(int, name.split("-"))
    return (today.year - y) * 12 + today.month - m

def rotate(token, today):
    period = f"{today.year}-{today.month - 1:02d}" if today.month > 1 else f"{today.year - 1}-12"
    with Workbook(token) as wb:
        names = [s["name"] for s in wb.sheets()]
        if period in names:
            return f"{period} already rotated — nothing to do"
        wb.call("PATCH", wb.ref("Current"), json={"name": period})
        wb.call("POST", "worksheets/add", json={"name": "Current"})
        wb.call("PATCH", wb.ref("Current"), json={"position": 1})
        for s in wb.sheets():
            if re.fullmatch(r"\d{4}-\d{2}", s["name"]):
                age = months_old(s["name"], today)
                if age > 24:
                    wb.call("DELETE", wb.ref(s["name"]))
                elif age > 12 and s["visibility"] == "Visible":
                    wb.call("PATCH", wb.ref(s["name"]), json={"visibility": "Hidden"})
        return [s["name"] for s in wb.sheets() if s["visibility"] == "Visible"]

calls.clear()
print(rotate(os.environ["GRAPH_TOKEN"], date(2026, 11, 1)))
print(rotate(os.environ["GRAPH_TOKEN"], date(2026, 11, 1)))
print([c[:2] for c in calls])

On 1 November the job turns Current into 2026-10, starts a new Current sheet after the instructions and hides the month that has just passed a year old. Run again the same day it finds 2026-10 already present and does nothing — the job is safe to rerun, which matters for anything triggered by a scheduler. The call log shows exactly what was sent, all within one session: the rename, the add, the move and one PATCH hiding 2025-10, then on the second run only the session request before the job stops. Because the mock records every request, the same log is the basis of a unit test for the rotation logic — assert the sequence of calls for a given date and starting set of sheets, and the job's behaviour on the first of each month is pinned down without touching a real workbook. Deleting is the only irreversible step; keeping deletion at two years and hiding at one gives a long window to notice a mistake, and the workbook's version history in OneDrive covers the rest.

Common pitfalls

• Download, edit, upload. It can overwrite edits made online; change the workbook through the API.
• Names unquoted in the path. Quote and URL-encode sheet names, or use IDs.
• Invalid or duplicate names. Validate locally; names are unique ignoring case.
• No session for many changes. Group calls in a workbook session.
• Deleting too eagerly. Hide first; delete only well past retention.

Conclusion

Microsoft Graph manages worksheets in place: GET worksheets lists them, POST worksheets/add creates one, PATCH changes name, position and visibility, and DELETE removes one. Group the calls in a workbook session, address sheets by quoted, URL-encoded name or by ID, check names against Excel's rules first, and build rotation jobs that check what has already been done so they are safe to rerun.

Frequently asked questions

How do I add a worksheet with Microsoft Graph? POST to /workbook/worksheets/add on the drive item with a JSON body containing the new sheet's name.

How do I rename or move a worksheet through Graph? PATCH the worksheet, addressed by ID or quoted name, with a body containing name for a rename or position for a move.

How do I hide a worksheet with Microsoft Graph? PATCH the worksheet with visibility set to Hidden, or VeryHidden to hide it from Excel's Unhide list.

Why use a workbook session for several changes? A session keeps the workbook open on the server between calls, which is faster and saves the changes together.

Related

• Up one level: Automating Excel Online with Microsoft Graph — Excel in the cloud from Python.
• Read and Write Excel Online Ranges with Microsoft Graph — filling the new sheet.
• Copy a Template Workbook in SharePoint with Microsoft Graph — whole new workbooks instead of sheets.
• Rename, Reorder and Delete Excel Sheets with openpyxl — the same for local files.