x0 OData API (Excel native)

The x0 API exposes an OData v4 service for Excel, Power BI, and other Microsoft clients.

Service root

https://api.x0.co.nz/odata/v1

The portal dashboard lists the same catalog from SourceEndpoint (seed: manage.py seed_sources). See it as an ER diagram: Data schema.

Legacy JSON: GET /v1/tables/{name} for entity cache tables (e.g. accounts, invoices).

Authentication

Any one of:

• Header: Authorization: Bearer x0_live_<id>.<secret>
• Header: X-Api-Key: x0_live_<id>.<secret>
• Query (Excel Web API): ?api_key=x0_live_<id>.<secret>
  Also accepted as query names: X-Api-Key, ApiKey, apikey, key

Query-string keys appear in access logs / proxies — prefer Bearer when you control the client. Excel’s native Web API mode only knows how to send query params, so we support that on purpose.

Tenant selection

Accounts, Settings, Contacts, Invoices, and other source entity sets are tenant-specific. Provide one of:

• Query: ?tenant_id=<tenant-guid>
• Header: X-Xero-Tenant-Id: <xero-tenant-guid>

If omitted, the first connected tenant is used.

X0 also accepts an optional tenant (fills TenantId / ConnectedAt / LastSyncAt) but still returns a row if none is connected.

OData query options

Supported on all entity sets:

Example:

GET /odata/v1/Accounts?tenant_id=...&$filter=Status eq 'ACTIVE'&$select=Code,Name&$orderby=Code asc&$top=500

Xero-compatible query params (cache filter)

x0 also accepts Xero Accounting GET–style options on the same OData URLs. These filter the warm cache (no Xero call per request). See Xero HTTP GET.

Response headers when used: X-X0-Modified-Filter: applied, X-X0-Summary-Only: true.

Not in v1: full Xero where= expressions (timeout-prone). Prefer $filter.

Diagnostic: ?xero_pass=1 forwards supported params to live Xero for that request only (does not replace cache unless you also pass refresh=true). Quota-sensitive — not for Excel schedules.

Probe / refresh summary field seeds:

python manage.py probe_xero_summary_only --all
python manage.py probe_xero_summary_only --entity Accounts --customer-id N --print-python

Excel desktop — From Web + Bearer (recommended)

Excel’s Web API credential type appends ?api_key=… to the URL — it does not set an HTTP header.
It also often rejects our x0_live_<ulid>.<secret> keys with “An API key value wasn't specified.”

Use From Web instead:

1. Data → Get Data → From Web → Advanced
2. URL: https://api.x0.co.nz/odata/v1/Accounts
3. Header: Authorization = Bearer x0_live_….secret (full key after Bearer)
4. Credentials → Anonymous

Optional — OData.Feed with Bearer in the formula

let
    Source = OData.Feed(
        "https://api.x0.co.nz/odata/v1",
        [#"Authorization" = "Bearer PASTE_FULL_KEY_HERE"],
        [Implementation = "2.0"]
    ),
    Accounts = Source{[Name = "Accounts", Signature = "table"]}[Data]
in
    Accounts

Optional — Web API query param (ApiKeyName = "api_key")

let
    Source = OData.Feed(
        "https://api.x0.co.nz/odata/v1",
        null,
        [Implementation = "2.0", ApiKeyName = "api_key"]
    ),
    Accounts = Source{[Name = "Accounts", Signature = "table"]}[Data]
in
    Accounts

“Access to the resource is forbidden”

HTTP 403. Usual causes: missing scope xero.accounts.read, expired key, or no credential sent.

Confirm in Yaak: GET /odata/v1/Accounts + Bearer.

See excel-addin.md (guided Office.js wizard) or excel-desktop.md for the full checklist.

Scopes

Entity sets enforce API key scopes:

Wildcard grants such as xero.*.read are supported.

X0 metadata entity

GET /odata/v1/X0 returns one row so Excel and agents can see the portal connection:

• Service / version / ApiBaseUrl / PortalUrl / DocsUrl
• Customer name, email, plan, created at
• Resolved tenant id/name, ConnectedAt, LastSyncAt (when a Xero org is connected)
• Presenting API key label, prefix, environment, scopes
• ExcelHint (e.g. prefer Journals_Table)

This is an x0-only entity — not a Xero resource.

Data freshness

OData reads are served from x0’s local cache when warm (not live Xero on every Excel refresh). Pass ?refresh=true to force a Xero pull (see data-freshness.md). Responses may include X-X0-Cache, X-X0-Fetched-At, X-X0-Sync, and X-X0-Stale headers.

Journals vs Journals_Table

Xero’s GET /Journals returns nested JournalLines. x0 keeps that raw shape in cache and on /odata/v1/Journals.

For Excel, use /odata/v1/Journals_Table: one row per line, with header fields (JournalID, JournalDate, JournalNumber, CreatedDateUTC, plus Reference / Source*) repeated on every line. That is an x0-derived entity — not a separate Xero resource.

Requires Xero OAuth scope accounting.journals.read on the connected org, and API key scope xero.journals.read.

Note: accounting.journals.read needs Advanced+ and use-case approval (unlikely on Starter — see xero-quirks.md). x0’s app is Starter; Connect does not request GL Journals. Product path: stitch a pseudo-journal from transaction/report data (incomplete vs real Journals). Manual journals use accounting.manualjournals.read.

Attachments

Xero has no top-level /Attachments list. On sync, x0 crawls warm parent caches (Invoices, Contacts, Accounts, …) where HasAttachments is true, calls GET /{Parent}/{id}/Attachments, and stores metadata in the Attachments OData entity.

Each row includes:

• RowKey — stable {ParentType}:{ParentID}:{AttachmentID}
• ParentType, ParentID, FileName, MimeType, ContentLength
• DownloadPath — e.g. /v1/attachments/Invoices/{id}/{file_id}

Download bytes (any parent type):

GET https://api.x0.co.nz/v1/attachments/Invoices/{InvoiceID}/{FileID}
Authorization: Bearer x0_live_….…

Account-specific write/list routes remain on /v1/accounts/{id}/attachments (see accounts-write.md).

Contacts & Invoices writes

REST + thin OData aliases for Contacts, draft Invoices, Items, draft PurchaseOrders, and CreditNotes — see contacts-invoices-write.md, items-purchase-orders-write.md, and credit-notes-payments-write.md. Lifecycle actions (authorise/void/payments) are REST-only on /v1/invoices, /v1/purchase-orders, and /v1/payments.

PUT  /v1/contacts?tenant_id=…
POST /odata/v1/Contacts
PUT  /v1/invoices?tenant_id=…

Requires xero.contacts.write / xero.invoices.write.

Requires parent caches to be warm first (sync Invoices, Contacts, etc.), then refresh Attachments.

Excel: metadata + download help live in the Reports & Attachments workbook (excel-workbook.md). Account write/upload macros stay on the Accounts “include scripts” pack.

Reports vs Reports_Table

On sync, x0 fetches a configured set of reports (Balance Sheet, P&L, Trial Balance, Bank Summary, Budget Summary, Executive Summary, Aged AR/AP) using default date windows (prior month-end / month-to-date for P&L-style reports).

• /odata/v1/Reports — one row per report type (nested Rows stripped for Excel scalars)
• /odata/v1/Reports_Table — one row per nested report row/cell group (ReportRowID, Cell0, Cell1, Account, Amount, …)

Use Reports_Table in Excel Power Query. Re-sync with ?refresh=true to pull fresh report dates.

Requires Xero OAuth report scopes (accounting.reports.read umbrella or individual accounting.reports.*.read scopes matching each report type).

Excel: use the dashboard workbook type Reports & Attachments (not the basic Accounts / All-endpoints packs) — see excel-workbook.md.

HistoryNotes

Like Attachments, Xero has no top-level History list. Sync crawls warm parent caches (Invoices, Contacts, Payments, …) and calls GET /{Parent}/{id}/History, storing rows in HistoryNotes (RowKey, ParentType, ParentID, Changes, User, Details, DateUTC).

Cap: ~80 parents per type per sync (not a full org audit dump). Warm the parent tables first.

PaymentServices

GET /PaymentServices → OData PaymentServices. Requires Xero OAuth scope paymentservices (partner / certification). Simulator seeds a Custom service for local testing.

BankStatements vs BankStatements_Table

Bank statement lines come from the Bank Feeds API (bankfeeds.xro/1.0), not Accounting api.xro/2.0.

• Sync pages GET /Statements (and expands GET /Statements/{id} when list rows omit lines)
• /odata/v1/BankStatements — one row per statement (balances + counts; nested lines stripped for Excel scalars)
• /odata/v1/BankStatements_Table — one row per StatementLines entry (StatementLineID, PostedDate, Amount, PayeeName, …)

Requires OAuth scope bankfeeds (partner certification). Optional env XERO_BANKFEEDS_BASE_URL (defaults derived from XERO_API_BASE_URL). Simulator serves /bankfeeds.xro/1.0/Statements.

Kept out of the basic All endpoints Excel pack (heavy). Prefer Power Query on BankStatements_Table.

Organisation, Types, ResponseCodes, ReleaseNotes

Related

• Documentation index · Glossary
• data-schema.md — dashboard diagram + mermaid
• excel-desktop.md · excel-addin.md · excel-workbook.md
• data-freshness.md · date-transform.md
• gzip-responses.md · yaak.md · ai-agents.md