The job-costing report QuickBooks Online won’t export, rebuilt from the line-item API.
A working reproduction of the whole pipeline: the Custom Report Builder failure mode where a multi-project bill repeats its full total against every project, the per-line truth the QBO API returns instead, the four business rules from the brief, and the formatted Excel output you can download. Every number on this page is computed live from QBO-API-shaped fixture data by the same engine that generates the spreadsheet.
/api/selftestSee the checksThe report-builder bug, reproduced
One subcontractor bill, three line items, three projects: $100 + $200 + $100, a correctly entered $400.00 bill. The Custom Report Builder reads a header-level figure and repeats it against every tagged project. The API reads Line[] and returns each line’s own amount. Same bill, both readings:
Custom Report Builder (header-level read)
| Project | Amount shown |
|---|---|
| The Cartographer’s Daughter | $400.00 |
| Salt & Cedar | $400.00 |
| A Study in Feathers | $400.00 |
| Summed across projects | $1,200.00 |
A $400.00 bill lands as $1,200.00 of project cost: 3.0x overstated. On a bill spread across 70 projects, the same read overstates 70x.
QBO API (per-line read)
| Project | Line amount |
|---|---|
| The Cartographer’s Daughter | $100.00 |
| Salt & Cedar | $200.00 |
| A Study in Feathers | $100.00 |
| Summed across projects | $400.00 |
Per-line amounts sum to $400.00, exactly the bill’s TotalAmt. Selftest check 1 asserts this identity on every run.
What the API actually returns
The same bill as a QBO API Bill object (schema per Intuit’s v3 reference, minorversion 75). The header carries the vendor and the A/P control account; the money truth lives in Line[], where every line has its own Amount and its own project ref in AccountBasedExpenseLineDetail.CustomerRef. The balance-sheet side of the entry never appears in Line[], which is why the double-entry duplicate-row problem from the report exports disappears on this path.
Bill bill_101, as returned by the API
{ "Id": "bill_101", "TxnDate": "2026-06-05", "VendorRef": { "value": "v_whit", "name": "S. Whitcombe (1099 contractor)" }, "APAccountRef": { "value": "acc_ap", "name": "Accounts Payable (A/P)" }, "TotalAmt": 400, "Line": [ { "Id": "1", "Amount": 100, "DetailType": "AccountBasedExpenseLineDetail", "AccountBasedExpenseLineDetail": { "AccountRef": { "value": "acc_exp_rec", "name": "Production Labor:Recording" }, "CustomerRef": { "value": "p_cart", "name": "The Cartographer’s Daughter" } } }, { "Id": "2", "Amount": 200, "DetailType": "AccountBasedExpenseLineDetail", "AccountBasedExpenseLineDetail": { "AccountRef": { "value": "acc_exp_rec", "name": "Production Labor:Recording" }, "CustomerRef": { "value": "p_salt", "name": "Salt & Cedar" } } }, { "Id": "3", "Amount": 100, "DetailType": "AccountBasedExpenseLineDetail", "AccountBasedExpenseLineDetail": { "AccountRef": { "value": "acc_exp_rec", "name": "Production Labor:Recording" }, "CustomerRef": { "value": "p_feathers", "name": "A Study in Feathers" } } } ] }
Paged extraction, no silent truncation
Pages through Bill, Invoice and Expense queries with STARTPOSITION / MAXRESULTS until a page returns fewer rows than requested, then asserts the fetched count equals COUNT(*).
MAXRESULTS at 1,000 (default 100). This workbench pages at 3 so the loop is visible on a small fixture set; the loop logic is identical at any page size.The four business rules, live
The brief’s rules applied as visible passes over the 42 flattened P&L lines. Toggle any rule off to see what breaks. The raw pull had 8 bills, 10 invoices and 2 expenses, flattened to one row per line item.
The formatted Excel, generated for real
The Phase 4 deliverable made visible: projects as columns, customer in row 1 and project in row 2, customers alphabetical, single-project customers consolidated under Indie Author, a trailing total column per customer, and paired dollar / percent-of-revenue columns. The preview below and the downloadable .xlsx come from the same engine call.
| P&L by Job | Aster & Vale Publishing | Bramblewood Press | Cormorant House | Indie Author | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Glass Orchard | % | Winter Counting | % | Total | Night Harvest | % | Salt & Cedar | % | The Cartographer’s Daughter | % | Total | A Study in Feathers | % | The Last Lighthouse Keeper | % | Total | Driftwood Letters | % | Lagos Frequencies | % | Pocket Storms | % | Total | |
| Income - Production Services | $2,150.00 | 100.0% | $1,700.00 | 100.0% | $3,850.00 | $2,600.00 | 100.0% | $2,900.00 | 100.0% | $3,800.00 | 100.0% | $9,300.00 | $2,450.00 | 100.0% | $1,980.00 | 100.0% | $4,430.00 | $1,400.00 | 100.0% | $1,850.00 | 100.0% | $980.00 | 100.0% | $4,230.00 |
| Income Other - Narrator Fees | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $0.00 | 0.0% | $0.00 | 0.0% | $1,236.00 | 32.5% | $1,236.00 | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $670.00 | 47.9% | $824.00 | 44.5% | $0.00 | 0.0% | $1,494.00 |
| Total Income | $2,150.00 | 100.0% | $1,700.00 | 100.0% | $3,850.00 | $2,600.00 | 100.0% | $2,900.00 | 100.0% | $5,036.00 | 132.5% | $10,536.00 | $2,450.00 | 100.0% | $1,980.00 | 100.0% | $4,430.00 | $2,070.00 | 147.9% | $2,674.00 | 144.5% | $980.00 | 100.0% | $5,724.00 |
| Recording Labor | $330.00 | 15.3% | $0.00 | 0.0% | $330.00 | $410.00 | 15.8% | $510.00 | 17.6% | $320.00 | 8.4% | $1,240.00 | $100.00 | 4.1% | $300.00 | 15.2% | $400.00 | $0.00 | 0.0% | $305.00 | 16.5% | $0.00 | 0.0% | $305.00 |
| Editing Labor | $340.00 | 15.8% | $260.00 | 15.3% | $600.00 | $0.00 | 0.0% | $390.00 | 13.4% | $280.00 | 7.4% | $670.00 | $0.00 | 0.0% | $360.00 | 18.2% | $360.00 | $280.00 | 20.0% | $0.00 | 0.0% | $260.00 | 26.5% | $540.00 |
| Mastering Labor | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $280.00 | 10.8% | $0.00 | 0.0% | $240.00 | 6.3% | $520.00 | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 |
| Proofing & QC Labor | $0.00 | 0.0% | $190.00 | 11.2% | $190.00 | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $220.00 | 9.0% | $0.00 | 0.0% | $220.00 | $0.00 | 0.0% | $0.00 | 0.0% | $150.00 | 15.3% | $150.00 |
| Expense Other - Narrator Fees | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $0.00 | 0.0% | $0.00 | 0.0% | $1,200.00 | 31.6% | $1,200.00 | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $650.00 | 46.4% | $800.00 | 43.2% | $0.00 | 0.0% | $1,450.00 |
| Other Project Costs | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | $48.00 | 2.0% | $0.00 | 0.0% | $48.00 | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 | 0.0% | $0.00 |
| Total Costs | $670.00 | 31.2% | $450.00 | 26.5% | $1,120.00 | $690.00 | 26.5% | $900.00 | 31.0% | $2,040.00 | 53.7% | $3,630.00 | $368.00 | 15.0% | $660.00 | 33.3% | $1,028.00 | $930.00 | 66.4% | $1,105.00 | 59.7% | $410.00 | 41.8% | $2,445.00 |
| Net Profit | $1,480.00 | 68.8% | $1,250.00 | 73.5% | $2,730.00 | $1,910.00 | 73.5% | $2,000.00 | 69.0% | $2,996.00 | 78.8% | $6,906.00 | $2,082.00 | 85.0% | $1,320.00 | 66.7% | $3,402.00 | $1,140.00 | 81.4% | $1,569.00 | 84.8% | $570.00 | 58.2% | $3,279.00 |
OAuth 2.0, documented honestly
This workbench does not fake a live Intuit connection. Below is the exact production flow the monthly script uses, per Intuit’s OAuth 2.0 docs, written so a non-developer can re-authorize without the original developer.
realmId.https://appcenter.intuit.com/connect/oauth2 ?client_id=<from Intuit developer portal> &response_type=code &scope=com.intuit.quickbooks.accounting &redirect_uri=<registered redirect> &state=<csrf token>
x_refresh_token_expires_in: 8726400).POST https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer grant_type=authorization_code&code=<code>&redirect_uri=<registered redirect>
POST https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer
grant_type=refresh_token&refresh_token=<latest stored refresh token>
then: GET https://quickbooks.api.intuit.com/v3/company/<realmId>/query
?query=SELECT * FROM Bill WHERE TxnDate >= '<start>' AND TxnDate <= '<end>'
STARTPOSITION 1 MAXRESULTS 1000.env file next to the script, never in the code. The docs cover: where each value comes from, how to rotate the client secret in the Intuit portal, and the 5-minute re-authorization walkthrough for the one failure that ever needs a human (a refresh token idle past 100 days).sandbox-quickbooks.api.intuit.com) for quickbooks.api.intuit.com and drop in the production client credentials. Since August 2025 the API defaults to minorversion 75; the script pins it explicitly.Selftest: the invariants that make it trustworthy
Correctness is the whole job, so the workbench asserts its own claims. Each check recomputes from the fixtures through the same pipeline that renders this page and generates the Excel. /api/selftest returns this as JSON.
Running selftest...