Checkout.com to Google Sheets payment reconciliation log

PromptCreate

Every morning at 6am, run a deterministic sync that logs the previous day's Checkout.com payments to a Google Sheet for reconciliation and bookkeeping. This is an append-only job: pull exactly one day's window, write one row per payment, and never modify existing rows, so finance can pivot the log and match it against the bank.

Use Checkout.com Search Payments to retrieve every payment from yesterday's window. Build the window as the full previous calendar day (00:00:00 to 23:59:59) in our reporting timezone and filter on the payment's created/requested timestamp. Search Payments is cursor-paginated, so page through all results until the cursor is exhausted and collect the complete set of the day's payments before writing anything.

For each payment, build one row capturing: payment id, the payment date, the amount, currency, status, card scheme, client reference (the reference field), and customer (customer id or email). Convert the amount from Checkout.com minor units to a decimal before writing. Checkout.com amounts are integers in the currency's smallest unit (1000 = 10.00 GBP), so divide by 100 for standard two-decimal currencies, do not divide for zero-decimal currencies like JPY and KRW, and divide by 1000 for three-decimal currencies like BHD, KWD, and OMR. Write the amount as a plain decimal value so finance can sum and pivot it.

Append the rows to our finance spreadsheet with Google Sheets Append Values, which appends rows after the last row of the table. Write one row per payment in a stable column order matching the header (payment id, date, amount, currency, status, card scheme, client reference, customer). Append only; never overwrite or clear existing rows.

Because each run pulls only a single day's window and appends, the daily log is naturally deduplicated across runs. If a run is retried, key on the payment id so the same payment isn't written twice (for example, read the existing id column and skip ids already present). Keep the mapping deterministic: every payment in the window becomes exactly one row.

What does this prompt do?

Each morning, automatically gathers all of the previous day's Checkout.com payments.
Adds one row per payment to your finance spreadsheet, with the amount shown as a normal decimal instead of raw pennies.
Captures the details finance needs on every row: payment ID, date, amount, currency, status, card type, your reference, and the customer.
Only ever appends new rows, so your running log stays intact and easy to pivot or match against the bank.

What do I need to use this?

A Checkout.com account with access to your payments.
A Google account and a spreadsheet to collect the log.
A tab set aside for the daily payment rows, ideally with a header row already in place.

How can I customize it?

Change the run time, for example later in the morning once the prior day has fully settled.
Choose which spreadsheet and tab the rows land in.
Adjust which columns you capture or the order they appear in.

FAQs

Which payments get logged each day?

Everything Checkout.com processed the previous calendar day, from midnight to midnight in your reporting timezone.

Are amounts shown as real currency values?

Yes. Checkout.com stores amounts as whole numbers of the smallest currency unit, and we convert them to a normal decimal before writing (so 1000 becomes 10.00), handling currencies like Japanese yen and Bahraini dinar correctly.

Will it create duplicate rows if it runs more than once?

Each run pulls only a single day's window and appends, so a normal daily schedule will not double up. Payment IDs are logged too, so repeats are easy to spot if a run is ever retried.

Does this change my Checkout.com data?

No. It only reads your payments. Nothing is modified in Checkout.com.

Can I use this to reconcile against my bank?

Yes, that is the whole point. The sheet gives finance a clean, one-row-per-payment log to pivot and match against bank deposits.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.