Log every Shippo shipping label in QuickBooks and Sheets

I want every shipping label I buy to land in the books automatically instead of being reconciled by hand at month end. Build this as a strictly deterministic code workflow: the same source fields map to the same destination fields on every run, with one status check and no interpretation anywhere.

Trigger: a webhook from Shippo subscribed to the transaction_created event. Shippo posts the Transaction object as the request body. Take the transaction object_id from that payload and treat the rest of the payload as untrusted, because label purchase in Shippo is asynchronous and the settled amount and label details are only populated once the purchase resolves.

Step 1, re-read the label. Call Shippo Retrieve Transaction (Label) with the object_id from the webhook. Gate on status. If status is SUCCESS, continue. If status is ERROR, stop immediately and record nothing. If status is still QUEUED, wait a few seconds and retrieve it again, up to five attempts total, then stop without writing anything. Queued and errored purchases must never reach QuickBooks or the spreadsheet.

Step 2, collect the fields. From the confirmed transaction gather: the Shippo transaction object_id, the final purchase amount and its currency, the carrier, the service level name, the tracking number, the label purchase date, and the destination address (name, city, state or province, postal code, country). If the transaction returns the rate or the shipment as an id reference rather than an expanded object, resolve them with Shippo Retrieve Rate and Shippo Retrieve Shipment. The amount, carrier, and service level come from the rate; the destination comes from the shipment address_to.

Step 3, record the expense in QuickBooks Online with Create Purchase. The vendor is the carrier, resolved through a carrier-to-vendor mapping I configure once (for example USPS to one vendor id, UPS to another). If a carrier has no entry in that mapping, stop and do not guess a vendor. The expense line is coded to the shipping expense account id I configure, the payment account and payment type are configured constants, the amount is the final label amount, and the transaction date is the label purchase date. Write the Shippo transaction id into the memo or private note field so the entry is traceable back to the exact label.

Step 4, append the same details to a Google Sheets ledger with Append Values, one row per label, always in this fixed column order: purchase date, Shippo transaction id, carrier, service level, tracking number, destination city, destination state or province, destination postal code, destination country, amount, currency, and the QuickBooks purchase id returned in step 3. Append only, never overwrite existing rows, and write values so dates and amounts land as real dates and numbers rather than text.

Ordering matters: create the QuickBooks purchase first, then append the spreadsheet row including the resulting QuickBooks purchase id, so the ledger is a log of what actually made it into the books. If the QuickBooks step fails, do not write the spreadsheet row. The Shippo transaction id appearing on both the purchase and the row is what makes any accidental duplicate obvious when reconciling against the monthly carrier invoice.

Configuration I should be able to set in one place: the spreadsheet id and ledger tab name, the QuickBooks shipping expense account id, the payment account id and payment type, and the carrier-to-vendor id mapping.