Add a Chart to an Excel Online Workbook with Microsoft Graph

When a report workbook lives in OneDrive or SharePoint and people open it in the browser, the cleanest way to update it is in place, through Microsoft Graph's Excel API: write the new numbers into the range, and the charts that people look at should follow. Graph can create those charts too — a native Excel chart, not a picture — and later move, retitle and re-point them, or render one to a PNG for a summary email. This page builds a chart on a workbook's sales sheet, positions it, updates its data range when the table grows, and fetches an image of it. Because live Graph calls need a tenant and credentials, the code was tested against a mocked endpoint that records each request, which is shown in the last step. It belongs to Automating Excel Online with Microsoft Graph.

Prerequisites

pip install requests msal responses

You need an access token with Files.ReadWrite (delegated) or Files.ReadWrite.All / Sites.ReadWrite.All (application) permission, obtained as described in Authenticate to Microsoft Graph with MSAL for Excel Automation. The workbook's drive item ID and a sheet called Sales with month names in A2:A13 and two series in B and C are assumed. responses is only needed for the offline test at the end.

Step 1 — A small client with a workbook session

Every chart call goes to the workbook's /workbook endpoint. A persistent session keeps the workbook open on the server between calls, which is faster and makes a series of changes behave like one editing session:

import requests

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

class Workbook:
    def __init__(self, token, item_id, drive="me/drive"):
        self.base = f"{GRAPH}/{drive}/items/{item_id}/workbook"
        self.http = requests.Session()
        self.http.headers["Authorization"] = f"Bearer {token}"
        r = self.http.post(f"{self.base}/createSession", json={"persistChanges": True}, timeout=30)
        r.raise_for_status()
        self.http.headers["workbook-session-id"] = r.json()["id"]

    def call(self, method, path, **kw):
        r = self.http.request(method, f"{self.base}/{path}", timeout=60, **kw)
        r.raise_for_status()
        return r.json() if r.content else None

    def close(self):
        self.call("POST", "closeSession")

createSession with persistChanges: true returns a session ID that every later request sends in the workbook-session-id header; closeSession ends it. Without a session, each call opens and closes the workbook on the server, which works but is slower and can hit concurrency limits in busy files — the trade-offs are explained in Recalculate Excel Online Workbooks with Graph Sessions.

Step 2 — Add the chart

def add_chart(wb, sheet, chart_type, source, name, title):
    chart = wb.call("POST", f"worksheets/{sheet}/charts/add",
                    json={"type": chart_type, "sourceData": source, "seriesBy": "Columns"})
    wb.call("PATCH", f"worksheets/{sheet}/charts/{chart['name']}", json={"name": name})
    wb.call("PATCH", f"worksheets/{sheet}/charts/{name}/title", json={"text": title, "visible": True})
    return name

charts/add takes the chart type — ColumnClustered, Line, BarClustered, Pie and the other Excel chart types — and the source range in A1 notation including headers. seriesBy: "Columns" makes each column a series (North, South) with the months as categories; "Auto" lets Excel guess, which is usually but not always the same. The new chart gets a generated name such as Chart 1, so the next call renames it to something stable: later runs address the chart by that name. The title is a sub-resource with its own PATCH.

Step 3 — Put it where people expect it

A chart added through Graph appears at a default position, often on top of the data. setPosition places it over a block of cells, which keeps it aligned with the grid when column widths change:

def place_chart(wb, sheet, name, start_cell, end_cell):
    wb.call("POST", f"worksheets/{sheet}/charts/{name}/setPosition",
            json={"startCell": start_cell, "endCell": end_cell})

Placing the chart over E2:L18 puts it beside the A:C data, about eight columns wide and sixteen rows high. The chart also has top, left, width and height properties in points that can be patched directly, but cell-based placement is easier to reason about and survives zoom levels.

Step 4 — Point an existing chart at new data

Next month the table has a thirteenth row. Rather than deleting and re-creating the chart — which loses any formatting someone applied by hand — re-point it:

def update_chart_source(wb, sheet, name, source):
    wb.call("POST", f"worksheets/{sheet}/charts/{name}/setData",
            json={"sourceData": source, "seriesBy": "Columns"})

def ensure_chart(wb, sheet, name, source, title):
    existing = {c["name"] for c in wb.call("GET", f"worksheets/{sheet}/charts")["value"]}
    if name in existing:
        update_chart_source(wb, sheet, name, source)
    else:
        add_chart(wb, sheet, "ColumnClustered", source, name, title)
        place_chart(wb, sheet, name, "E2", "L18")

ensure_chart makes the job safe to run repeatedly: it lists the sheet's charts, updates the source range if the named chart exists, and creates and places it only on the first run. The size of the data range usually comes from the table itself — if the data is an Excel table, read its range first (GET tables/{name}/range) and pass that address, so the chart always covers exactly the table's rows. Appending those rows through Graph is covered in Append Rows to an Excel Online Table with Microsoft Graph.

Step 5 — Get a picture of the chart

Graph can render a chart to an image, returned as base64 text — useful for putting the current chart into an email or a Teams message without screenshots:

import base64

def chart_png(wb, sheet, name, width=800, height=400, path="chart.png"):
    data = wb.call("GET", f"worksheets/{sheet}/charts/{name}/image(width={width},height={height},fittingMode='fit')")
    with open(path, "wb") as fh:
        fh.write(base64.b64decode(data["value"]))
    return path

The image function takes the size in pixels and a fitting mode; the response's value is the PNG as base64, decoded and written to a file. Embedding it in an email body is shown in Embed a Chart Image in the Body of a Report Email.

Step 6 — Test the calls without a tenant

The responses library intercepts requests calls and returns canned answers, so the whole flow can run in a test and the requests can be inspected:

import json
import responses

ITEM = "01ABCDEF"
BASE = f"{GRAPH}/me/drive/items/{ITEM}/workbook"
png = base64.b64encode(b"\x89PNG fake image bytes").decode()

with responses.RequestsMock(assert_all_requests_are_fired=False) as mock:
    mock.post(f"{BASE}/createSession", json={"id": "sess-123"})
    mock.get(f"{BASE}/worksheets/Sales/charts", json={"value": []})
    mock.post(f"{BASE}/worksheets/Sales/charts/add", json={"name": "Chart 1", "id": "{C1}"})
    mock.patch(f"{BASE}/worksheets/Sales/charts/Chart 1", json={"name": "SalesByMonth"})
    mock.patch(f"{BASE}/worksheets/Sales/charts/SalesByMonth/title", json={"text": "Sales by month"})
    mock.post(f"{BASE}/worksheets/Sales/charts/SalesByMonth/setPosition", body=b"")
    mock.get(f"{BASE}/worksheets/Sales/charts/SalesByMonth/image(width=800,height=400,fittingMode='fit')",
             json={"value": png})
    mock.post(f"{BASE}/closeSession", body=b"")

    wb = Workbook("test-token", ITEM)
    ensure_chart(wb, "Sales", "SalesByMonth", "A1:C13", "Sales by month")
    out = chart_png(wb, "Sales", "SalesByMonth")
    wb.close()

    for call in mock.calls:
        body = json.loads(call.request.body) if call.request.body else ""
        print(call.request.method, call.request.url.split("/workbook/")[1], body)
    print("session header sent:", mock.calls[1].request.headers.get("workbook-session-id"))
with open(out, "rb") as fh:
    print(fh.read(4))

The printout also shows a detail of URL handling: the generated name Chart 1 was sent as Chart%201, because requests percent-encodes the space — another reason to rename charts to simple, stable names. The printout lists every request in order with its JSON body — charts/add with ColumnClustered and A1:C13, the rename, the title, setPosition with E2 and L18 — and confirms that the session header was sent after createSession. A test like this catches broken URLs and bodies without credentials; a final check against a real test workbook is still worth doing once, because a mock only knows what you told it.

Common pitfalls

• Addressing charts by generated names. Chart 1 changes between files; rename the chart and use the stable name.
• Recreating instead of updating. Deleting and re-adding loses manual formatting; use setData.
• Names with spaces in URLs. requests encodes spaces, but special characters such as # or / in chart or sheet names break the path; keep names simple.
• No session for many calls. Use createSession for a series of edits and close it afterwards.
• Throttling. Busy workbooks return 429 or 503; retry with back-off as in Handle Microsoft Graph Throttling for Excel.

Conclusion

Microsoft Graph can create native charts in a workbook stored online: open a session, POST charts/add with a type and source range, rename the chart to a stable name, set its title, and place it over a cell block with setPosition. On later runs, update its range with setData rather than recreating it, fetch a PNG through the image function when the chart needs to travel, and test the request flow offline with responses before pointing it at a real workbook.

Frequently asked questions

How do I add a chart to an Excel file in OneDrive with Python? Send a POST request to the worksheet's charts/add endpoint under the workbook's drive item, with a JSON body containing type, such as ColumnClustered, sourceData, such as A1:C13, and seriesBy. The response contains the new chart's name and ID.

How do I change the data range of an existing Excel Online chart? Call POST .../charts/{name}/setData with the new sourceData range and seriesBy. This keeps the chart and its formatting instead of deleting and re-creating it.

How do I position a chart created with Microsoft Graph? Call POST .../charts/{name}/setPosition with startCell and endCell, for example E2 and L18, to place it over that block of cells. You can also patch top, left, width and height in points.

Can Microsoft Graph return a chart as an image? Yes. Call the chart's image function with a width, a height and a fitting mode; the response's value field holds the chart rendered as a base64-encoded PNG.

Related

• Up one level: Automating Excel Online with Microsoft Graph — editing cloud workbooks in place.
• Read and Write Excel Online Ranges with Microsoft Graph — updating the data the chart shows.
• Creating Charts in Excel with openpyxl — native charts in files.
• Run a Report When an Excel File Changes in OneDrive — triggering the refresh.