How We Stopped Live Updates From Breaking Excel on SharePoint: Using Microsoft Graph the Right Way

Our software writes live purchase order data into a client's Excel workbooks on SharePoint. These are large workbooks full of formulas, partly protected, and edited by hand by the team all day. When we started writing into them automatically, almost everything failed: requests timed out, big updates were rejected, product codes lost their leading zeros, and protected sheets refused the data.

The fix wasn't one setting, but a different way of working with Excel: open the workbook once, write in one quick burst, pause the heavy recalculation, touch only what the software owns, and tell the team clearly when it's done. Today the workbook works as a live dashboard, and the team's own work stays exactly where they left it.

What broke when software started writing to the workbook

Software talks to Excel on SharePoint through Microsoft Graph, Microsoft's official gateway for reading and changing Microsoft 365 files. Each request through it is one API call.

On our client's workbooks, it broke in several ways at once:

• Every API call timed out, even reading a single cell.

• Large updates were rejected. Adding a few hundred new rows at once was refused.

• Product codes changed. A SKU (product code) like 084781 arrived as 84781.

• Protected sheets refused the data. The team protects key sheets against accidental edits, and that blocked our software too.

• Nobody could tell whether the update had run. A failed run looked exactly like a successful one.

Together, they made the dashboard unusable.

Why it kept breaking: heavy workbooks and Microsoft Graph's limits

The root cause was the weight of the workbook. Every formula has to be loaded and calculated before Excel can answer anything.

Without a session (one open connection to the workbook that later requests can reuse), Microsoft Graph opens the workbook afresh for each API call. On a heavy workbook, opening alone takes longer than Graph allows for a single request, so the call times out before it starts.

Large writes failed for a related reason. By default, Excel recalculates the whole workbook every time a cell changes. Write a few hundred rows into a formula-heavy workbook, and Graph runs out of time and rejects the request.

There are published limits too. Microsoft Graph allows 1,500 Excel API calls per 10 seconds for each organization, and 5,000 per 10 seconds across all organizations an app works with. But Microsoft says Excel throttling (refusing requests when there are too many) can't be reduced to a simple number, because the cost depends on the request and the workbook's size. It advises one request at a time per workbook, and names parallel changes as a common cause of throttling.

We weren't hitting a counter. We were asking a very heavy workbook to do too much, too often.

The fix: how we write to Excel through Microsoft Graph safely

1. Open the workbook once, then write in one quick burst. Each run now opens a single session, which takes about a minute on the client's main workbook. After that, every API call reuses it and answers quickly.

There was a catch. A session left idle for a few minutes expires. Some of our work is slow, such as pulling data from our database or having AI draft supplier messages, and doing it with a session open meant the session expired mid-run. So all the slow work now happens first, and the session opens only when everything is ready to write. The writes then go in one quick burst, one request at a time, as Microsoft recommends.

2. Pause recalculation during large writes. We switch the workbook's calculation to manual, write in chunks, then switch it back, so Excel recalculates once at the end instead of hundreds of times. If anything fails, the setting is still restored, so the team's formulas never stay frozen.

3. Keep codes as text. Excel sees 084781, decides it's a number, and drops the zero for good. So we set those cells to text before writing, and the codes arrive exactly as they are in the purchase order system.

4. Only touch what the software owns. Some columns are filled by our software, others by the team. We only ever write the software's columns, even when they sit right beside the team's, and only update a row when something in it has actually changed. The team's notes, checkboxes and formulas are never disturbed. As an extra safety net, a copy of the sheet is saved before each update.

5. Unlock protected sheets, then lock them again exactly as they were. Our software notes how a sheet is protected (for example, whether the team can still sort and filter), removes the protection, writes, and puts the same protection back, even if the write fails. One honest limit: Microsoft Graph can't remove a password, so this only works on sheets protected without one.

6. Show the result on the sheet. The top of the sheet now shows a status and a last-run time. A successful update writes SUCCESSFUL and the time; a failed one writes UNSUCCESSFUL, so a problem is never mistaken for "nothing changed".

The team can trust the dashboard, and check at a glance that it's current.

The "in progress" marker comes from the sheet's own button, the moment someone clicks it. Our software could only write it after opening a session, leaving the old SUCCESSFUL on screen for about a minute. This way the team sees "in progress" instantly.

Finally, when Microsoft's servers or the network have a brief problem, our software tries again automatically, waiting at least as long as Microsoft asks.

Future-proofing: lessons for any business that lets software write into shared spreadsheets

Here is what we'd tell any business connecting shared spreadsheets to other systems:

• Decide who owns each column. Software and people can share a sheet safely when everyone knows which columns belong to whom.

• Write only what changed. It's faster, gentler on the workbook, and leaves people's work alone.

• Plan for the workbook to get heavier. Every new formula and row makes each change more expensive. Design for next year's workbook, not today's.

• Respect the team's safeguards. If they protect a sheet, the software should restore that protection exactly.

• Make success visible. If people can't see that an update ran, they'll stop trusting the data, even when it's right.

The results

• The workbook works as a live dashboard. Fresh purchase order data reaches the sheet the team already uses.

• The team's work is safe. Manual columns, notes, formulas and protection stay as they were.

• Product codes stay correct. Leading zeros survive every update.

• Everyone can see the update ran, and when.