Planday → Excel, Power BI, and Claude

Planday's Timesheet Report — the one with worked hours and staff cost per shift — has no single API endpoint. Most people find that out the hard way, after wiring Power Query to the wrong thing and getting exactly 50 rows back.

This project fixes both problems:

1. A translator that assembles the real Timesheet Report from the three endpoints Planday never combines, and serves it to Excel or Power BI as a live feed.

2. An MCP server covering the entire Planday API — all 125 operations — so you can ask questions in plain English: "what did agency cover cost us in July, by department?"

MIT licensed. Runs on your own infrastructure. Your Planday credentials never leave it.

Not yet proven against a live portal. Everything here works against a realistic sample portal and the API client is generated from Planday's own published specifications, but nobody has yet pointed it at real data. If that is you, read TESTING.md first — it explains how to reconcile against Planday's own report, and is honest about where this is most likely to be wrong. There is a pnpm doctor command that checks each layer and tells a genuine bug apart from a setup problem.

Just want it working?

→ SETUP.md is the step-by-step, written for whoever runs the rota rather than a developer. About 20 minutes, no coding, free to run.

Once it is deployed, opening it in a browser gives you this — it tells you what is configured, runs a real test extract, and writes the Power Query snippet for you with your own URL already in it:

The rest of this file is for developers.

Requirements: Node 20 or newer, and nothing else. pnpm matches the lockfile but npm install works fine.

Everything below runs on realistic sample data. No Planday credentials needed to see it work — this is the fastest way to decide whether it does what you want.

pnpm install && pnpm dummy      # or: npm install && npm run dummy

Planday timesheet  mode=dummy  2026-06-01 -> 2026-07-26

department                  shifts   worked h        cost   cost/h
------------------------------------------------------------------
Events                         160     1137.5   £21398.45    18.81
Kitchen                        167     1159.8   £21169.29    18.25
Front of House                 166     1116.1   £20793.30    18.63
Housekeeping                   133      942.6   £18243.15    19.35
------------------------------------------------------------------
TOTAL                          626     4356.1   £81604.19    18.73

rows: 626   cost source: payroll   portal: Harbour Group
edge cases -> orphan punch-clock: 1, open shifts: 1, no cost attached: 30, edited after approval: 22

626 rows - well past the 50-record cap that catches most people out.

The two things that trip everyone up

1. There is no Timesheet Report endpoint

https://openapi.planday.com/api/absence and its siblings are documentation pages, not API endpoints — an easy and very common mistake. And Absence is Planday's holiday and overtime accounting, unrelated to timesheets.

The Timesheet Report is a join of three endpoints:

Plus hr/departments, hr/employees, hr/employeegroups and scheduling/shifttypes to turn ids into names. That join is src/timesheet/transform.ts.

2. The 50-record cap

Planday's list endpoints declare limit with maximum: 50 in their own specification. Raising it does nothing — the server silently ignores you. The only way through is to loop on offset until you have paging.total records.

The useful twist: the three report endpoints above are not paginated at all. They are bulk date-range calls. So once you are on the right endpoints, the 50-record problem mostly evaporates — it only ever affected the small lookup tables.

Also worth knowing

Every Planday request needs two headers, not one:

Authorization: Bearer <access token>
X-ClientId: <client id>

Miss X-ClientId and you get a 401 that looks exactly like a bad token. Access tokens also expire after an hour, so anything scheduled has to refresh them — which is most of why doing this in raw Power Query is unpleasant, and why the bridge below exists.

Related MCP server: TimeChimp MCP Server

Getting it into Excel or Power BI

Two options. They suit different budgets and both are included.

Option A — the bridge service (recommended)

A small service sits between Planday and Excel. It handles OAuth, the hourly token refresh and all the pagination, so Power Query becomes a single Web.Contents call.

cp .env.example .env      # set BRIDGE_KEY
pnpm bridge

Open http://localhost:8787 for a setup page: it shows what is configured, runs a test extract, and hands you a Power Query snippet with your own URL already in it.

The endpoint carries wages and salaries, so it is authenticated from the first commit — the shared secret goes in the x-bridge-key header. It refuses every write operation outright, regardless of settings: a URL a spreadsheet can refresh must never be able to change a live roster.

Option B — no server at all

powerquery/Timesheet-direct.pq talks to Planday directly from Power Query, including the List.Generate offset loop that fixes the 50-record problem. Slower, and it re-authenticates on every refresh, but it costs nothing to run.

The MCP server

19 tools covering all 125 Planday operations.

pnpm mcp        # stdio; .mcp.json already registers it for Claude Code

For Claude Desktop, add this to claude_desktop_config.json (macOS: ~/Library/Application Support/Claude/, Windows: %APPDATA%\\Claude\\):

{
  "mcpServers": {
    "planday": {
      "command": "pnpm",
      "args": ["--dir", "/absolute/path/to/planday-bridge", "tsx", "apps/mcp/index.ts"],
      "env": {
        "PLANDAY_CLIENT_ID": "your-client-id",
        "PLANDAY_REFRESH_TOKEN": "your-refresh-token",
        "PLANDAY_WRITE_TIER": "read"
      }
    }
  }
}

Leave the two Planday values out entirely to run against the sample portal.

Registering 125 separate tools would swamp most MCP clients and burn tens of thousands of context tokens on descriptions before a single question is asked. So the surface is total but the registration is tiered:

Universal access — 3 tools, all 125 operations

• planday_search_operations — find any endpoint by keyword

• planday_describe_operation — its full signature and response shape

• planday_call — invoke it, validated against the spec, pagination handled

Anything Planday can do is reachable here, including endpoints nobody has thought of yet.

Curated reads — 12 tools for the common path: departments, employees, employee groups, shift types, positions, shifts, punch-clock entries, absence records, payroll, time-and-cost, scheduling history, plus planday_whoami.

Composed — 4 tools doing what the raw API cannot do in one call: planday_get_timesheet (the three-way join), planday_summarise_staff_cost, planday_export_timesheet_csv, planday_explain_columns.

Why it can answer questions rather than just return data

Eight weeks of a mid-sized portal is well over a thousand rows. Handing those to a model as raw JSON exhausts its context and invites arithmetic errors. So aggregation happens server-side: planday_summarise_staff_cost groups by department, employee, employee group, shift type, day, week, cost source or agency-vs-own-staff, and returns a dozen rows. Large extracts leave as a file path, never inline.

Write safety

62 of the 125 operations modify data — including deleting shifts and departments, and clocking employees in and out. Those are discoverable but gated:

Nothing is hidden — but an LLM pointed at a live rostering portal does not get a delete button by accident.

Going live

No Planday credentials are needed for anything above. When you want live data:

1. In Planday: Settings → Integrations → API Access → Create App. Tick the scopes listed in SETUP.md, click Authorise, and copy the Client ID and Refresh Token. Tick as few scopes as you can get away with — see SECURITY.md.

2. Put them in .env as PLANDAY_CLIENT_ID and PLANDAY_REFRESH_TOKEN.

3. pnpm doctor

pnpm doctor goes further: it checks environment, configuration, connection, every Planday scope separately, and then a real report build, so you can see exactly which layer is failing. Its output carries no credentials or staff data and is safe to share. Planday gates each area separately, so a perfectly valid token can still be refused on payroll; when that happens the report degrades to time-and-cost, and then to hours with no cost, rather than failing.

No code changes are needed. Sample and live run the same code path.

Planday offer a 30-day free trial with API access, and will issue a developer demo portal on request — useful for testing an integration without touching a production roster.

How it stays correct

The whole client is generated from Planday's own OpenAPI specifications, which are vendored in specs/. Hand-written endpoint wrappers would drift the moment Planday shipped a change; generated ones are re-derived in seconds.

pnpm gen     # 125 operations, 294 schemas. Asserts no duplicate ids, no unresolved refs.
pnpm test    # 32 tests
pnpm doctor  # diagnose a live connection, layer by layer

The coverage test invokes every one of the 125 operations and validates each response against that operation's own schema. That is what makes "the whole API is available" a verified fact rather than a claim — and if Planday adds an endpoint, it is covered automatically, with no list to remember to update.

test/deploy.test.ts bundles the real serverless entrypoint with esbuild and exercises the routes, because plenty of things pass under tsx and still break once bundled.

The remaining tests pin the join rules against the cases that break a hand-rolled Power Query merge: a punch-clock entry with no shift id, a shift crossing midnight, an end-before-start pair, unpaid break deduction, a monthly-salaried employee with hours but no cost, an unassigned open shift, and a shift edited after it was signed off. The sample portal contains all of them on purpose.

Adapting it

Agency and contract cover. Planday has no first-class concept of agency staff, so this infers it from the employee group or shift type name. If your portal tags it differently, change AGENCY_RULE in src/timesheet/transform.ts. It is one regex.

Column names. src/timesheet/columns.ts is the single source of truth. The CSV header, the /columns route, the explain_columns MCP tool and the generated powerquery/Timesheet.pq all derive from it, so renaming a column there updates everything. Run pnpm gen afterwards.

Currency and locale. Taken from whatever Planday reports for your portal.

Other Planday data. The timesheet is just the best-developed example. Every one of the 125 operations is already reachable through planday_call and the /api/ route — absence balances, revenue, pay rates, punch clock, employee history. If you want another report shaped the way the timesheet is, src/timesheet/ is the pattern to copy.

Security

Read SECURITY.md before you deploy this. Short version: the refresh token reaches payroll data and does not expire on its own, nothing is stored anywhere, writes are refused by default, and there is a private channel for reporting vulnerabilities.

Contributing

Issues and pull requests welcome. If Planday changes their API: re-download the specs into specs/, run pnpm gen, and the diff will show you exactly what moved.

Working on this with an AI coding agent? AGENTS.md is written for that — it carries the domain knowledge and the non-obvious traps that are expensive to rediscover.