Append Rows to an Excel Online Table with Microsoft Graph

A shared tracker in SharePoint is often the least painful place for data to land: people already open it, it has filters and a pivot built on it, and Power BI or Power Automate may already read from it. What it lacks is a feed. Every morning somebody pastes yesterday's orders underneath the table and hopes the formulas followed. The Graph rows/add endpoint does that paste properly — the table grows, calculated columns and formatting extend to the new rows, and nobody has to close the file first. This guide, part of Automating Excel Online with Microsoft Graph, builds a reliable append job around it.

Prerequisites

pip install msal requests pandas

• A delegated token with Files.ReadWrite — the table endpoints do not accept application permissions. See Authenticate to Microsoft Graph with MSAL for Excel Automation.
• An .xlsx workbook in OneDrive or SharePoint containing an Excel table (created with Insert → Table) with a name such as Orders, and its drive and item IDs.

Step 1 — Set up the client

The same small helper used throughout this section: a session carrying the token and a function that raises with Graph's error text.

import os
from urllib.parse import quote
import pandas as pd
import requests

GRAPH = "https://graph.microsoft.com/v1.0"
BOOK = f"{GRAPH}/drives/{os.environ['GRAPH_DRIVE_ID']}/items/{os.environ['GRAPH_ITEM_ID']}/workbook"
TABLE = f"{BOOK}/tables/{quote('Orders')}"

session = requests.Session()
session.headers["Authorization"] = "Bearer " + os.environ.get("GRAPH_DELEGATED_TOKEN", "token")

def call(method: str, url: str, **kwargs) -> dict:
    response = session.request(method, url, timeout=120, **kwargs)
    if response.status_code >= 400:
        raise RuntimeError(f"{method} {url} -> {response.status_code}: {response.text[:400]}")
    return response.json() if response.content else {}

Addressing the table by name through /workbook/tables/{name} works regardless of which worksheet it sits on, and it keeps working when somebody moves the table or renames the sheet.

Step 2 — Align your columns to the table header

rows/add takes positional arrays. It has no idea that your DataFrame's third column is Amount; it writes whatever is third into the table's third column. Read the header and reorder the frame to match it, failing loudly if a column is missing.

def table_header() -> list[str]:
    return call("GET", f"{TABLE}/headerRowRange")["values"][0]

def align_to_table(frame: pd.DataFrame, header: list[str]) -> pd.DataFrame:
    missing = [c for c in header if c not in frame.columns]
    if missing:
        raise ValueError(f"incoming data lacks table columns {missing}")
    extra = [c for c in frame.columns if c not in header]
    if extra:
        print("ignoring columns not in the table:", extra)
    return frame[header]

header = table_header()
incoming = pd.DataFrame({
    "Amount": [1320.0, 540.5, 1200.0],
    "Rep": ["Cara", "Dev", "Ana"],
    "Region": ["West", "South", "North"],
    "Source": ["crm", "crm", "crm"],
})
aligned = align_to_table(incoming, header)
print(header, aligned.columns.tolist())

Tables often include calculated columns — a Margin column defined by a formula, for example. You cannot simply leave them out, because every row array must cover every column of the table. Add them to the frame with an empty string instead, and Excel fills the column formula in for each new row, exactly as it does when a person types below the table.

def add_calculated_placeholders(frame: pd.DataFrame, header: list[str], calculated: set[str]) -> pd.DataFrame:
    for column in calculated & set(header):
        frame = frame.assign(**{column: ""})
    return frame

aligned = align_to_table(add_calculated_placeholders(incoming, header, {"Margin"}), header)

Step 3 — Skip rows that are already in the table

A scheduled job will eventually run twice for the same day — a retry after a timeout, a manual rerun after a fix. Without a check, the table gets duplicate orders. Read the key column from the table and drop incoming rows whose key already exists. The rows endpoint returns each row's values, which is enough to rebuild the keys.

def existing_rows() -> pd.DataFrame:
    rows = []
    url = f"{TABLE}/rows"
    while url:
        page = call("GET", url)
        rows.extend(item["values"][0] for item in page.get("value", []))
        url = page.get("@odata.nextLink")
    return pd.DataFrame(rows, columns=header)

current = existing_rows()
KEY = ["Region", "Rep", "Amount"]           # use a real order ID column when the table has one

def keys(frame: pd.DataFrame) -> pd.Series:
    normalised = frame[KEY].copy()
    for column in KEY:
        numeric = pd.to_numeric(normalised[column], errors="coerce")
        text = normalised[column].astype(str).str.strip()
        normalised[column] = numeric.map("{:.10g}".format).where(numeric.notna(), text)
    return normalised.apply(tuple, axis=1)

is_new = ~keys(aligned).isin(set(keys(current)))
to_add = aligned[is_new]
print(f"{len(aligned)} incoming, {len(to_add)} new")      # 3 incoming, 2 new

The composite key here is only for the example; a real feed should carry a unique identifier — an order number, an invoice ID — and use that single column. The normalisation step is not optional, though. Excel returns 1200 for a whole number while pandas holds 1200.0, and a naive string comparison treats those as different keys and appends the row again. Formatting every numeric key the same way, and trimming text keys, makes the comparison reflect what a person would call the same record.

Step 4 — Send rows in batches

Microsoft's guidance for rows/add is explicit: adding one row per call degrades performance, so collect rows and send many in each request. A few hundred to a couple of thousand rows per call is a good starting range; adjust from there based on how long each request takes.

def to_json_value(value):
    if pd.isna(value):
        return ""
    return value.item() if hasattr(value, "item") else value

def append_rows(frame: pd.DataFrame, batch_size: int = 500) -> int:
    added = 0
    for start in range(0, len(frame), batch_size):
        chunk = frame.iloc[start:start + batch_size]
        values = [[to_json_value(v) for v in row] for row in chunk.itertuples(index=False)]
        call("POST", f"{TABLE}/rows/add", json={"index": None, "values": values})
        added += len(values)
    return added

print("added", append_rows(to_add))

"index": None appends at the end; an integer inserts at that zero-based position and shifts later rows down. Each batch is its own request, so if batch three fails, batches one and two are already in the table — which is exactly why the duplicate check in Step 3 runs before every attempt, not just the first.

Step 5 — Retry throttling and gateway timeouts

The table endpoint can return 504 Gateway Timeout occasionally, and Microsoft's reference says the right response is to repeat the request. Heavy use also brings 429 Too Many Requests with a Retry-After header. Wrap the POST in a retry that honours that header and backs off otherwise.

import time

def call_with_retry(method: str, url: str, attempts: int = 5, **kwargs) -> dict:
    for attempt in range(1, attempts + 1):
        response = session.request(method, url, timeout=120, **kwargs)
        if response.status_code in (429, 503, 504) and attempt < attempts:
            wait = float(response.headers.get("Retry-After", 2 ** attempt))
            print(f"{response.status_code}; retrying in {wait:.0f}s")
            time.sleep(wait)
            continue
        if response.status_code >= 400:
            raise RuntimeError(f"{method} {url} -> {response.status_code}: {response.text[:400]}")
        return response.json() if response.content else {}
    raise RuntimeError("unreachable")

result = call_with_retry("POST", f"{TABLE}/rows/add", json={"values": [["North", "Ana", 99.0]]})
print(result.get("index"))

A 504 is ambiguous: the rows may or may not have been added before the gateway gave up. Re-reading the keys before retrying a batch — or accepting that the next scheduled run's duplicate check will catch it — keeps an ambiguous timeout from becoming duplicate data. Keep requests to one workbook sequential; Microsoft recommends against parallel writes to the same file, which cause throttling and merge conflicts rather than speed.

Common pitfalls

Performance and scale notes

Reading every existing row to build the key set is the expensive part for a large table. When the table is big, read only the key column through its range — /tables/{name}/columns/{column}/range — or keep a local record of keys already sent. Use a workbook session for the whole job so the service keeps the workbook open between the header read, the key read and the appends. And keep perspective: an Excel table in SharePoint is a comfortable home for thousands or tens of thousands of rows; beyond that, a database with the workbook as a view is the more durable design, as in Refresh an Excel Report from a Database on a Schedule.

Conclusion

Appending to an Excel Online table is a positional operation, so align the DataFrame to the table's header first. Make the job idempotent by removing rows whose keys already exist, send rows in batches rather than one at a time, and retry 429 and 504 responses with a delay — re-checking keys before a retried batch. Use a delegated token and a workbook session, keep writes to one workbook sequential, and the shared tracker gains a feed that people can keep using exactly as before.

Frequently asked questions

Do calculated columns fill in for rows added through Graph? Yes. Send an empty value for the calculated column and Excel applies the column formula to each new row, just as it does when a person types below the table.

Can I insert rows at the top instead of the end? Yes. Set index to 0 in the request body. Rows below are shifted down, which is slower on large tables than appending at the end.

Can I update or delete an existing table row? Rows can be addressed by index under the table's rows collection, updated through their range and deleted. Because indexes shift as rows change, locate rows by key immediately before changing them.

Does this work with Power Automate reading the same table? Yes. Rows added through Graph are ordinary table rows, so flows and Power BI datasets reading the table see them on their next run.

Related

• Up one level: Automating Excel Online with Microsoft Graph — reading, editing and recalculating workbooks in Microsoft 365.
• Read and Write Excel Online Ranges with Microsoft Graph — range updates, dates and blanks.
• Recalculate Excel Online Workbooks with Graph Sessions — sessions that make multi-call jobs faster.
• Create an Excel Table with Python — building the table in the first place.
• Retry a Failed Excel Report Job in Python — retry strategies for whole jobs.