Braintree settlement reconciliation workbench for month end

I want a settlement reconciliation workbench that our finance team opens at month end and again every Monday morning. The job it does is easy to state and painful to do by hand: line up what Braintree says it settled against what our books say we recorded, find the days that disagree, and sign off the days that match.

The main screen starts with a date range picker. For the selected range, pull our Braintree activity using Search Transactions and bucket it by settlement date rather than by the date the payment was created. Show one row per day with gross settled volume, refunds issued, disputed amounts, transaction count, and the net deposit we should expect to hit the bank. Alongside each day, break those same totals down by card type (Visa, Mastercard, Amex, Discover) and by payment method (card, PayPal, and any wallet types we accept), because when a day is off it is usually one of those buckets that moved.

Next to the Braintree column, show what our books say for the same period. Pull one side from the reconciliation sheet we already keep in Google Sheets using Get Values, and the other from QuickBooks using Run Reports with a Profit and Loss or General Ledger report scoped to the same date range. Compute the variance per day against each source. Let me set a variance threshold inside the app, as a flat amount or a percentage, and highlight any day that breaks it so I can go straight to the mismatch instead of reading down the whole month.

Clicking a day drills into the individual transactions behind that day's total: transaction ID, amount, status, card type, payment method, customer, and whether the transaction carries a refund or a dispute. This is the screen where someone actually finds the transaction causing a gap, so make it filterable and sortable by amount and status.

From the day detail I need two write actions. First, push that day's transaction detail into the reconciliation sheet using Append Values. Every exported row must be keyed on the Braintree transaction ID, and the app must read what is already in the sheet before writing so that pushing the same day twice never duplicates rows. Second, mark the day reconciled using Update Values, writing a short note and the name of whoever signed off along with a timestamp.

For a day that is confirmed clean, I want to record the matching customer payment in QuickBooks using Create Payment, so the deposit is represented in the books and not only in the spreadsheet. That action should only be available on a day I have already marked reconciled.

A few details I care about. Refunds and disputes must be shown as separate negative lines rather than netted silently into gross, so I can always see what came out and why; the netting only happens in the net expected deposit column. The reconciled state needs to persist in the app's own storage so a sign off survives a page refresh and my colleagues see the same state I do. Braintree returns amounts as decimal strings in the transaction currency, so sum them as decimals rather than as floating point numbers. If we have merchant accounts in more than one currency, group the totals by currency rather than adding across them, or let me scope the view to a single currency.

This is an attended tool, not a background job. Nothing should post to QuickBooks or write to the sheet without me clicking, and every write should report back exactly what it wrote and where.