# sharepoint-excel-mcp

MCP server that gives an AI agent read access to Excel files stored in SharePoint
and OneDrive for Business, turning a permission-matrix spreadsheet into structured
JSON.

Built for a QA automation flow: a test-case-writing agent reads Jira stories, each
story references a permission matrix as a SharePoint link, and this server is what
turns that link into data the agent can reason over — unattended, with no user
sign-in.

---

## Contents

- [Quick start](#quick-start)
- [Azure app registration](#azure-app-registration)
- [Permissions](#permissions) ← **read this one**
- [Registering with Claude](#registering-with-claude)
- [Tools](#tools)
- [Behaviour worth knowing](#behaviour-worth-knowing)
- [Troubleshooting](#troubleshooting)
- [Limitations](#limitations)
- [Development](#development)

---

## Quick start

```bash
npm install
cp .env.example .env      # then fill in TENANT_ID / CLIENT_ID / CLIENT_SECRET
npm run build

npm run doctor -- "<your sharepoint link>"   # verify the whole chain
```

No Azure tenant yet? Everything below runs offline:

```bash
npm run mock              # full tool flow against fixtures
npm run mock -- --wac     # same, forcing the download fallback
npm run doctor -- --mock "https://contoso.sharepoint.com/:x:/s/QA/T?e=1"
npm test
```

---

## Azure app registration

1. **Entra admin centre → App registrations → New registration.** Name it, choose
   *Accounts in this organizational directory only*, leave the redirect URI blank —
   this is an app-only flow with no sign-in.
2. Copy the **Directory (tenant) ID** and **Application (client) ID** from the
   Overview page into `.env`.
3. **Certificates & secrets → New client secret.** Copy the secret **Value**
   immediately — not the Secret ID. It is shown once and cannot be retrieved later.
4. **API permissions → Add a permission → Microsoft Graph → Application
   permissions.** Add one of the permissions described below.
5. **Grant admin consent** for the directory. Without this, every Graph call fails
   with 403 no matter how correct the code is.

> **Admin consent is an external dependency.** If you are not a Global Admin or
> Privileged Role Admin you cannot grant it yourself, and no amount of retrying
> will work around it. A 403 almost always means this step is outstanding.

---

## Permissions

This is the part that decides how the server behaves, so it is worth understanding
rather than copying.

### The situation

Microsoft's own documentation for the Excel endpoints
([worksheets](https://learn.microsoft.com/en-us/graph/api/worksheet-list),
[usedRange](https://learn.microsoft.com/en-us/graph/api/worksheet-usedrange))
states **"Application: Not supported"**, and the
[Excel overview](https://learn.microsoft.com/en-us/graph/api/resources/excel)
documents only delegated scopes.

In practice app-only access *does* work with tenant-wide `Sites.Read.All`. Under
`Sites.Selected` it commonly fails with:

```
403 — Could not obtain a WAC access token
```

So the server has **two read paths** and picks automatically.

### Option A — `Sites.Selected` (default, least privilege)

| | |
|---|---|
| Graph permission | `Sites.Selected` (Application) |
| Access scope | Only sites you explicitly grant |
| Excel Workbook API | Usually **unavailable** (WAC 403) |
| Read path used | Download `.xlsx` + parse |

After granting admin consent, grant the app access to each site:

```bash
# Find the site ID
GET https://graph.microsoft.com/v1.0/sites/{tenant}.sharepoint.com:/sites/{siteName}

# Grant this app read access to that site
POST https://graph.microsoft.com/v1.0/sites/{siteId}/permissions
{
  "roles": ["read"],
  "grantedToIdentities": [
    { "application": { "id": "<CLIENT_ID>", "displayName": "sharepoint-excel-mcp" } }
  ]
}
```

**A WAC 403 under this option is expected and is not a misconfiguration.** It means
the download fallback engaged and everything is working. `npm run doctor` reports it
as an informational branch, not a failure.

### Option B — `Sites.Read.All` (workbook API available)

| | |
|---|---|
| Graph permission | `Sites.Read.All` (Application) |
| Access scope | **Every** SharePoint site in the tenant |
| Excel Workbook API | Available |
| Read path used | `usedRange` directly |

More capable and avoids downloading files, but it is a tenant-wide read grant. Your
security team may reasonably refuse it.

### Which to choose

Start with **A**. Both paths produce byte-identical output — there is a test
asserting exactly that — so the only differences are latency and breadth of access.
Move to **B** only if the download fallback proves too slow for your file sizes.

### OneDrive / personal sites

Files under `https://{tenant}-my.sharepoint.com/personal/...` are **not supported**.
`Sites.Selected` does not reach personal OneDrive, and it resolves through a
different Graph path. Put the matrix in a SharePoint site document library.

---

## Registering with Claude

```bash
claude mcp add sharepoint-excel \
  --env TENANT_ID=<tenant-id> \
  --env CLIENT_ID=<client-id> \
  --env CLIENT_SECRET=<client-secret> \
  -- node /absolute/path/to/share-point-mcp/dist/index.js
```

Run `npm run build` first. For development you can point at `tsx src/index.ts`
instead of the built output.

---

## Tools

### `resolve_link(link)`

Converts a link into stable `driveId` / `itemId`.

```json
{ "driveId": "b!…", "itemId": "01…", "name": "matrix.xlsx", "webUrl": "https://…" }
```

Call this **once** and reuse the IDs. Sharing links expire and get revoked;
resolving one costs an extra Graph round trip on every call.

Three link shapes are accepted:

| Shape | Example |
|---|---|
| Sharing link | `…/:x:/s/QA/EYt_abc123?e=xY9z` |
| Direct path | `…/sites/QA/Shared Documents/matrix.xlsx` |
| Library view | `…/AllItems.aspx?id=%2Fsites%2FQA%2F…` |

### `list_excel_sheets(link | driveId+itemId)`

Worksheet names. Uncapped.

### `describe_sheet(ref, sheet?, headerRow?)`

Headers, total row count, and up to 3 sample rows — **no full data dump**. This is
the intended first call against an unfamiliar matrix: see the shape, then build a
targeted filter.

### `get_permission_matrix(ref, sheet?, headerRow?, maxRows?, offset?, columns?, filter?)`

The main tool. Uses the header row as keys.

```json
{
  "file": "matrix.xlsx", "sheet": "Permission Matrix",
  "headers": ["Role", "Module", "Delete"],
  "rowCount": 8, "returnedCount": 3, "offset": 0, "truncated": true,
  "hint": "Showing 3 of 8 matching rows. Narrow with `filter` or `columns` first…",
  "records": [ { "Role": "Admin", "Module": "Billing", "Delete": true } ]
}
```

- `maxRows` — default 500
- `offset` — for paging
- `columns` — project to a subset; large saving on wide matrices
- `filter` — `{ column, equals? , contains? }`, case-insensitive

`rowCount` is the number of rows **matching the filter**, not the sheet size.

### `get_cell_range(ref, sheet, range)`

Raw values for an explicit A1 range like `"A1:D20"`, for sheets whose layout does
not fit the header-row model.

---

## Behaviour worth knowing

### `maxRows` / `offset` / `filter` / `columns` are applied client-side

They bound **what the agent receives**, not what crosses the network. `usedRange`
returns the entire grid regardless, so `maxRows: 10` on a 50,000-row sheet still
transfers all 50,000 rows.

This matters because the parameter names strongly imply server-side paging, and the
gap only shows up as unexplained latency on a large sheet. If you need to genuinely
bound the transfer, use **`get_cell_range`** with an explicit address — that is the
tool that actually does it.

### Dates come back as Excel serial numbers

A date cell reads as `45292`, not `"2024-01-01"`. That is what Graph's `usedRange`
returns, and the download fallback normalizes to match so the two paths cannot be
told apart. Convert on the consuming side:
`new Date(Date.UTC(1899, 11, 30) + serial * 86400000)`.

> Dates before 1900-03-01 are off by one, because Excel reproduces a Lotus 1-2-3 bug
> that treats 1900 as a leap year. Not corrected — see the test that pins it.

### Blank cells are empty strings

`""`, not `null` — again matching Graph. Fully blank rows are dropped entirely.

### Header normalization

Real matrices have messy header rows, and both failure modes are silent:

| Input | Becomes | Why |
|---|---|---|
| `"  Role  "` | `Role` | trimmed |
| `""` (blank) | `column_C` | named by its real sheet column |
| `Role`, `Role` | `Role`, `Role_2` | duplicates would otherwise overwrite |

### The 25 MB download ceiling

Under the fallback path, files above `MAX_DOWNLOAD_BYTES` (default 25 MB) are
refused **before** the transfer starts. exceljs loads the whole workbook into
memory; an OOM would kill the stdio transport and surface to the agent as a
connection drop rather than a readable error.

### Timeouts

30 s on Graph calls, 60 s on the download. A timeout is reported as its own error
type naming the request, and is **not** retried — 429 and 5xx are retried up to 3
times, 403 never is.

---

## Troubleshooting

**Run the doctor first.** It walks every leg and tells you which one broke:

```bash
npm run doctor -- "<your link>"
```

```
1. Configuration          2. Link                3. Access token
4. Resolve to driveItem   5. Workbook API        6. usedRange
7. Download fallback      → Verdict
```

It exits non-zero only when **neither** read path works.

| Symptom | Meaning |
|---|---|
| `Missing required environment variable` | `.env` not filled in |
| 401 at step 3 | Wrong tenant/client ID, or the secret's **ID** was copied instead of its **Value**, or it expired |
| 403 at step 4 | Admin consent not granted, or the site not granted under `Sites.Selected` |
| 404 at step 4 | Link revoked or expired. Confirm it opens in a browser — a revoked link and a bad share-ID encoding are indistinguishable from the response |
| Yellow `•` at steps 5–6 | **Expected under `Sites.Selected`.** Fallback engaged, nothing to fix |
| 403 at step 5 that is *not* yellow | A genuine consent problem, not a WAC issue |

`npm run encode -- "<link>"` prints the share ID and the exact Graph request to
paste into [Graph Explorer](https://developer.microsoft.com/graph/graph-explorer) —
useful for isolating an encoding problem from a permissions one.

---

## Limitations

- **`.xlsx` only.** `.xls` and `.csv` are not supported by the Excel REST APIs.
- **No consumer OneDrive.** Business platform only.
- **No personal OneDrive sites** (`/personal/…`). Use a SharePoint site library.
- **Read-only.** No write tools, by design.
- Dates are Excel serials; pre-1900-03-01 dates are off by one.

---

## Development

```bash
npm test          # 181 tests, no network
npm run typecheck # strict, includes tests and scripts
npm run lint
npm run dev       # run the server from source
npm run fixtures  # regenerate the .xlsx test fixture
```

### Layout

```
src/
  index.ts          MCP server + tool registration, nothing else
  config.ts         env parsing, fail-fast validation
  logger.ts         stderr-only logger
  auth.ts           token acquisition, caching, single-flight
  graph.ts          authed fetch, retry, error mapping, redaction
  links.ts          pure: link classification + share-ID encoding
  sharepoint.ts     resolution + the two-path read
  xlsx-fallback.ts  download + exceljs parse + cell normalization
  transform.ts      pure: grid → records
```

### Testing approach

Pure logic (share-ID encoding, link parsing, the transform pipeline, cell
normalization) is unit tested with no network. Graph interactions run against a
routing stub and recorded fixtures. `server.test.ts` drives the real server through
an in-memory MCP client, which catches registration mistakes unit tests cannot see.

The most important test is **cross-path equivalence**: the same fixture is read via
the workbook API and via the download fallback, and the results must be deeply
equal. Testing each path alone cannot catch a divergence between them — and it
caught a real one during development.

Nothing in `npm test` touches the network. Only `npm run doctor` talks to Azure.

### Stdout is the protocol

Under `StdioServerTransport`, stdout carries JSON-RPC frames. A stray `console.log`
in `src/` corrupts the stream and breaks the server with an opaque parse error on
the client. All logging goes to stderr via `logger.ts`, enforced by an eslint
`no-console` rule on `src/**`. Scripts under `scripts/` are exempt — they are CLIs.