Bill every ShipHero shipment as a draft Xero invoice

Build me a code workflow that turns every outbound shipment into a billable draft invoice. Use a ShipHero poll trigger on new shipments, so the workflow runs once for each shipment that ships out of the warehouse. Every step here is deterministic and the amounts must be reproducible, so nothing about the pricing should be left to judgement.

For each shipment, call ShipHero Get shipment to read the shipment id, the ship date, the carrier and service used, the package weight, and the shipped line items with their quantities. Then call ShipHero Get order for the order behind that shipment to read the order number and the customer_account_id. On a 3PL account, customer_account_id identifies the brand the shipment belongs to, and it is the key for everything downstream: which rate card applies, whether the brand is billable, and which Xero contact the invoice is raised against.

Keep the rate card in workflow configuration as structured input rather than in code, so someone can change fees without editing logic. For each brand hold: the brand name, the ShipHero customer_account_id, the Xero contact id to invoice, a pick and pack base fee, a per additional item handling fee, how shipping is charged (pass the carrier cost through, or apply a fixed markup percentage), and a flag for whether the brand is on a flat monthly plan.

Skip shipments for brands on a flat monthly plan, since those clients pay a fixed retainer and must not also be billed per shipment. Also skip, and record as skipped, any shipment whose customer_account_id has no rate card entry. Never invent or estimate a fee for a brand you do not have rates for.

Compute the billable lines from the rate card and the shipment data. Add one pick and pack base fee line for the shipment. Add one additional item handling line priced as the total shipped units minus one, multiplied by the per additional item fee, and omit that line entirely when only one unit shipped. Add one shipping line for the carrier and service used, following the brand's shipping charge rule. Round money to two decimals, and give each line a description naming the fee and the order number so the invoice reads clearly to the client.

Create the invoice with Xero Create Invoices as a sales invoice (type ACCREC) against that brand's Xero contact, with status DRAFT. Do not approve, authorise, or send it. Finance reviews and approves drafts before anything reaches a client. Put the shipment id and the order number in the invoice reference so every invoice traces back to the shipment that produced it.

Then append one row to the fulfillment billing ledger spreadsheet using Google Sheets Append Values, with the shipment id, order number, brand, ship date, carrier and service, package weight, invoiced total, and the resulting Xero invoice id. This ledger is what finance reconciles the Xero drafts against at month end, so it should be a complete running record of what was billed.

Make the workflow safe to re-run. Before creating anything, check whether the shipment id already appears in the ledger sheet, and skip the shipment if it does, so a replayed trigger can never double-bill a brand. If Xero rejects the invoice, do not append a ledger row, because the ledger must only contain shipments that were actually invoiced.