The Escrow Tracker

The template is the example. The method is the product.

This page installs one specific tracker — the one from the episode. But the skill you’re actually picking up is bigger: describing what you want to Claude, pasting the code it writes into your spreadsheet, and testing it until you trust it.

Step 1. What Apps Script is, and why you care

Apps Script is a free layer inside every Google Sheet where code can live. It’s been sitting under Extensions → Apps Script this whole time. Code that lives there can build tabs, write formulas, add custom menus, react when you edit a cell, run on a schedule, and — with your permission — draft email from your Gmail.

Why you care: it’s the difference between a spreadsheet you maintain and a system that maintains itself. In this tracker, Apps Script stamps dates when you change a status, computes your net through your actual commission waterfall, rebuilds every formula if you break one, and stages your closing protocol the moment a deal closes.

Step 2. Install it

About five minutes. The tracker is a single paste — the complete script is supplied with this episode.

1. Create a fresh, blank Google Sheet at sheets.new. Name it whatever you like.
2. In the menu, open Extensions → Apps Script. Select the function myFunction() stub and delete it.
3. Paste the tracker script in, then hit the save icon.
4. Go back to your spreadsheet tab and refresh the page. After a few seconds a new Escrow Tracker menu appears at the top — that menu is how you talk to the code from now on.
5. Click Escrow Tracker → Set up / repair this sheet and approve the permissions.
6. Click Escrow Tracker → Seed sample test data. That loads about 24 fake deals which exercise every feature, including the tricky commission scenarios.

What you’re actually approving, in plain English

• Edit your spreadsheets — it builds the tabs and writes the formulas.
• Send email as you and manage drafts — the closing protocol drafts your review-request emails and emails teammates their tasks. Nothing sends without a human action.
• Run when you’re not present — the Monday-morning open-task reminder runs on a schedule.

The code is right there in your script editor. Nothing is hidden, nothing leaves your Google account, and you can revoke access any time at myaccount.google.com → Security.

Step 3. Make it yours

About ten minutes, and one rule everywhere: yellow cells mean type here. Everything else is computed. If a formula ever looks broken — errors or blanks where money should be — use Escrow Tracker → Repair formulas. It rebuilds every computed column from scratch, so you genuinely cannot permanently break this sheet.

The Start Here tab

• You — your name and email live here and only here. Don’t add yourself to the team roster.
• Goals — annual units, GCI, volume. Feeds the pace math on the dashboard.
• Lead pillars — rename them to yours; feeds the Lead Source dropdown and the by-source panel.
• Your team — other agents only. The column reads “They keep %”, so when a teammate closes a deal and keeps 45, enter 45. Solo agent? Leave it empty.
• Review links — replace the placeholders or the review email skips them.

The My Splits tab — where your real net comes from

This tab describes what others take out of your share. Whatever’s left is yours, and the order never changes:

Your teammate’s own brokerage split comes out of their cut — their cap, their tracker, not your math. On a team that splits after the brokerage instead, there’s a “team split timing” setting for that. Your broker’s main split is simply the first row of the fee table: one more fee in the waterfall, not a separate setting. And typing 15, 15% or 0.15 on a percentage row all work.

The Deals tab — your daily driver

One row per deal side, from signed to closed. Rows never move: the Status dropdown drives everything — row color, date stamps, the dashboard and the closing handoff. Statuses stamp their date only if the cell is blank, so backdating is safe. Double-ending a deal? Enter it twice, one listing row and one buyer row, each with its own GCI. Two sides, two units, exactly how production counts.

Already have an old tracker? Hand both to Claude

Optional — the sheet works fine starting from zero, and the year-over-year panel stays quiet until you have a prior year in it. But if you want your history carried over, don’t retype a single row.

Here are two spreadsheets: my old production tracker and my new Escrow Tracker. Add
every closed deal from the old tracker into the NEW tracker's Deals tab as historical
rows, then give me back the completed new tracker so I can copy it into my Google Drive.

Rules that keep the new sheet's math correct:
- Every imported deal gets Historical? = TRUE, with its final net in the Hist. Net ($)
  column — the new sheet trusts that number as entered and won't re-run old deals
  through my current splits.
- Closed deals keep Status "Closed" and their close date.
- Match my old columns onto the new Deals columns (Side, Client Name, Property Address,
  Lead Source, Agent, Status, Sales Price, GCI, dates, Historical?, Hist. Net, Notes).
  Anything you can't confidently map goes into Notes, not into a guess.
- If any of my old columns are ambiguous, ask me about them BEFORE you write anything.

When it's done, hand me the finished tracker and remind me to copy it back into my Drive.

Step 4. The closing engine — the status stages, you fire

Watch what happens when a deal’s status flips to Closed: a row appears on the Closings tab automatically, with client, address, email, close date and agent carried over. Then you check the box, and the machine runs — your review-request email is drafted in Gmail, every task in your closing SOP is created and assigned, and your teammates get an email listing exactly what’s theirs.

This guide deliberately doesn’t go deep on the closing protocol — that system has its own complete guide. What matters here is what its presence proves: spreadsheets can hand work to automations, and automations can hand judgment back to humans. Don’t want it? Ignore the Closings tab and never check a box. Staged rows just sit there.

Step 5. The five-minute test drive

Never trust an automation you haven’t watched work. With the sample data seeded:

Step 6. Don’t like this template? Build yours

This entire system was built in about an hour of conversation with Claude — describing, pasting, testing, correcting. No line of code was written by hand, and you can run that same loop on the things only you know about your business.

Maybe you don’t need a cap engine. You need a sign-call log with a callback timer, a vendor list that emails your transaction coordinator, a lead-follow-up board that nags you on day three. If it can live in a spreadsheet, this loop builds it.

I'm a real estate agent and I want a custom tool built in Google Sheets using Apps
Script. I don't write code — you'll write all of it, I'll paste it into Extensions →
Apps Script, and I'll test it and report back what I see.

Here's what I want, in my own words: [DESCRIBE IT — what you track today, where it
lives now, what annoys you about it, and what you wish happened automatically]

Ground rules for how you build it:
1. One complete script I paste once. If we make changes later, you give me the entire
   updated file, not snippets to splice in.
2. Give it a custom menu with a "Set up" item that builds all the tabs, and a
   "Repair formulas" item that rebuilds every formula in case I break something.
3. Every cell I'm supposed to type in gets a yellow background. Everything computed
   stays hands-off.
4. Anything irreversible — sending an email, deleting data — must require a deliberate
   human action like checking a box or clicking a menu item. Never fire it
   automatically.
5. After the code, tell me exactly how to install it, and give me a short test plan
   with sample data so I can verify each feature works before I trust it with real
   business.

Start by interviewing me about what I described — one question at a time — until you're
sure you understand it. Then build.

Those ground rules are the safety rails from this tracker, distilled. Steal them for every build.

Step 7. Troubleshooting