Google Sheets automation · portfolio sample

Cash Over/Short Reconciliation

Every night it matches each store’s end-of-day cash sales to the bank deposits, then emails the owner whatever doesn’t add up: short deposits, deposits that never arrived, and deposits entered twice.

Based on the daily cash check I run in my own building-supply stores. The stores and amounts below are fictional example data.

$
Cash sales From the Z-reports
Deposited From the bank statement
Net over/short  
Items to review  

Reconciliation

StatusStoreSales dayCash salesDeposited onDepositOver/shortNote

StoreDaysCash salesDepositedOver/shortTo review

The data behind it

Try it: change an amount or remove a deposit. Everything above updates as you type.

Needs columns Date, Store and Amount. Reference is optional.

Planted in the sample

Four things were hidden in the example data on purpose. This is how the check handles each one right now.

    How it runs in Google Sheets

    1. Sales tab

      Paste or import the end-of-day export from the POS. One row per store per day, or per register.

    2. Deposits tab

      Paste the bank statement CSV. Column names like Location, Deposit Amount or Memo are recognised.

    3. Nightly run

      At 9 PM the script fills a Results tab and colours each row by status.

    4. Email

      The owner gets the summary above, with a link back to the sheet.

    Permissions

    This spreadsheet only, sending email from your account, the nightly schedule and its own menu. It cannot open anything else in your Google Drive.

    Tested

    21 automated tests cover the matching rules, weekend deposits, US and day-first dates, messy amounts and CSV files, and a simulated Google Sheets run of the nightly email.