x0 Home Excel desktop

Excel desktop setup (screenshot checklist)

Capture these for the guided signup wizard on x0.co.nz. Store under x0/screenshots/excel/.

Step Screen Filename suggestion
1 Excel Data ribbon 01-data-ribbon.png
2 Get Data → From Web → Advanced (Bearer) 02-from-web.png
3 Header Authorization = Bearer x0_… 03-bearer-header.png
4 Expand value to a table 04-expand-value.png
5 Loaded sheet after refresh 05-sheet-loaded.png

Important: Excel “Web API” is not a header

Microsoft’s Web API credential type does not send X-Api-Key as an HTTP header.
It appends a query string: ?api_key=<your key>.

Concept What Excel does What you type
Parameter name ApiKeyName in the M formula Prefer api_key
Parameter value Key on the Web API dialog Full portal token x0_live_….secret

Our keys look like x0_live_<ulid>.<secret> (with a dot). That is valid for x0; Excel’s Web API dialog is flaky with long/dotted keys and often shows:

An API key value wasn't specified

Do not fight a broken Web API dialog. If Excel says “An API key value wasn't specified”, use From Web + Bearer below. Otherwise Web API + api_key is fine.

The portal key label (e.g. excel) is only for your dashboard list — never type it into Excel.

Excel add-in (guided)

Sideload the Office.js add-in: excel-addin.md or portal Excel add-in (/excel/addin/).
It signs you in, creates an excel-addin API token, loads a table into the sheet, and copies Power Query M.

NZ forms + Email PDF

Download A4 tax invoice / PO / quote stationery (excel-forms.md) and email from the add-in (Email PDF). Set From: + SMTP and review Sent forms — email-forms.md.

  1. Data → Get Data → From Web → Advanced
  2. URL:
https://api.x0.co.nz/odata/v1/Accounts
  1. HTTP header:
Name Value
Authorization Bearer x0_live_<id>.<secret>

Paste the full key after Bearer (space after Bearer). Optional: X-Xero-Tenant-Id = your Xero tenant GUID.

  1. Credentials → Anonymous (the Bearer header already carries the key)
  2. Transform / expand the value list into a table if Excel shows a record.

Local canoe: http://127.0.0.1:8001/odata/v1/Accounts.

Alternative — OData.Feed with header in the formula

Puts the secret in the workbook M code (fine for a personal file; avoid sharing the workbook).

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

Credentials → Anonymous.

Optional — Web API query param (?api_key=)

x0 accepts the key as a GET query parameter (what Excel’s Web API mode actually sends):

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

Then Web API → Key = full x0_… token (Excel calls …/odata/v1?api_key=… and …/Accounts?api_key=…).

Also accepted: X-Api-Key, ApiKey, apikey, key as the query name. Prefer api_key.

If Excel still says “An API key value wasn't specified”, clear permissions for api.x0.co.nz and use From Web + Bearer instead.

“Access to the resource is forbidden” / ResourceAccessForbiddenException

HTTP 403, or no credentials when Anonymous was cached.

  1. Data source settings → clear api.x0.co.nz permissions
  2. Confirm key has scope xero.accounts.read
  3. Prefer Bearer From Web (above)

Prove the key in Yaak first: GET …/odata/v1/Accounts + Bearer. Yaak 403 → fix scopes; Yaak 200 → fix Excel.

Auth options on the OData / Web dialog

Option Use with x0?
Anonymous Yes — only when Bearer / Authorization is already in the request headers
Windows No
Basic Yes — Excel-native username/password (see below)
Web API Yes — ApiKeyName = "api_key" → ?api_key= (Excel’s native path)
Organizational account No

Excel Get Data speaks Basic natively. x0 accepts:

Field Value
Username Token id only — x0_live_<ULID> or x0_test_<ULID> (left of the .)
Password Secret only (right of the .)

Also accepted: full x0_… key in password (username anything / blank) or full key in username.

  1. Data → Get Data → From Web (simple URL, no header)
  2. URL: https://api.x0.co.nz/odata/v1/Accounts
  3. Credentials → Basic → paste username + password as above
  4. Expand value into a table

A 401 from x0 includes WWW-Authenticate: Bearer, Basic realm="x0" so Excel shows the login prompt. Portal workbooks list BasicUsername / BasicPassword on the Parameters sheet.

Optional tenant

https://api.x0.co.nz/odata/v1/Accounts?tenant_id=YOUR_TENANT_GUID

Portal workbook (Accounts)

Prefer Dashboard → Download Excel workbook with type Accounts for snapshot + Functions + type sheets. For writes: enable Include scripts, save as .xlsm, import VBA from the MacroSetup sheet (see excel-workbook.md).

For Reports / Attachments (heavy flatten + download catalog), choose workbook type Reports & Attachments — those sheets are not included in Accounts or All-endpoints packs.

Types / enums: source-types.md. Mutations: accounts-write.md.

Sign in to see connection status