Contract Queue Digest: A Monday Email of Every Contract That Needs Action

What This Builds

Every Monday morning an email lands in your own inbox listing only the contracts that need you: overdue, due this week, missing a due date, or untouched for longer than you allow. Everything else stays quiet. You keep one tab, update a row when something moves, and stop rebuilding the "where is everything" list from memory each week.

The sheet does all the thinking. Zapier only reads one finished row and emails it to you, so no contract data goes to an AI vendor.

Prerequisites

• A Google account with Sheets and Gmail, normally through Google Workspace (Business Standard, $14/user/month) if your company provides it
• A Zapier account on the Professional plan ($29.99/month). Zapier's free plan allows only two-step Zaps, and this build has three steps (the trigger and two actions), so it needs a paid plan
• Approval from your company's IT or security team to connect Zapier to your Google account, if your company requires it
• Total ongoing cost: the Zapier plan above, plus the Google Workspace plan if your company does not already pay for it

Data note. Everything you type in the Queue tab passes through Zapier and Gmail. Zapier keeps the data from each step, including the full text of your digest, in its Zap History. Use short contract codes such as "Northwind MSA" and a few words for the next action. Never type privileged analysis, negotiation strategy, deal values or employee names into the sheet. Check that your company allows Zapier for this kind of data before you turn the Zap on.

The Concept

Think of the sheet as a clerk who prepares one briefing note every night. The clerk reads every row, decides which contracts need attention, and writes the note on a single card. Zapier is the courier who picks up that card every Monday and drops it in your inbox. The courier never reads the contracts and never decides anything.

There is a reason for the single card. A Zapier lookup that finds no matching row stops the Zap: Zap History shows "Safely halted", no later step runs, and no email goes out. If you pointed the lookup at the contracts themselves, a week with nothing overdue would produce silence, and you could not tell silence from a broken Zap. The Summary row always exists, so the Zap always has something to send, even if that something is "Nothing needs action this week".

Build It Step by Step

Part 1: Lay out the two tabs

Create a Google Sheet with two tabs named exactly Queue and Summary. Row 1 of each tab holds headers. Data starts in row 2.

Queue tab (one row per open contract)

Set up the dropdown on C2:C200 with Data, Data validation, Dropdown. Format E and F as dates and G as a plain number.

Summary tab (one data row)

Stale Days is your setting. A contract counts as stale when it has gone that many days with no touch. Do not leave F2 empty: an empty cell reads as zero and every contract would be marked stale.

Part 2: Add the Queue formulas

Put each formula in row 2 and copy it down to row 200. Rows below your last contract stay blank, because every formula starts by checking whether Contract is empty.

G2, Days Waiting

=IF(OR(A2="",F2=""),"",TODAY()-F2)

This returns nothing when there is no contract or no Last Touched date. Otherwise it returns today minus the last touch, in days.

H2, Stage

=IF(A2="","",IF(OR(C2="Signed",C2="Dead"),"Closed",IF(E2="","Missing date",IF(E2<TODAY(),"OVERDUE",IF(E2<=TODAY()+7,"Due this week",IF(AND(G2<>"",G2>=Summary!$F$2),"Stale","OK"))))))

The formula has six IF functions and ends with six closing brackets. It reads the tests in a fixed order and stops at the first one that is true.

I2, Digest Line

=IF(H2="","",A2&" | "&C2&" | "&D2&" | due "&IF(E2="","none",TEXT(E2,"yyyy-mm-dd"))&" | "&H2)

The TEXT function turns the date into text such as 2026-10-09. Without it, the date joins as a serial number like 46300. The IF around it handles a missing due date, because TEXT on an empty cell returns a date in 1899.

Part 3: Why the tests run in this order

Closed comes first because a signed contract keeps its old due date forever. If the date tests ran first, a contract signed last month with a due date last month would show OVERDUE every Monday until you deleted the row. Testing Status first means setting Status to Signed (or Dead) takes the row out of the digest on its own.

Missing date comes before the date comparisons because an empty Due Date counts as zero in a comparison, which would look like a date in 1899 and flag the row as OVERDUE for the wrong reason. OVERDUE comes before Due this week so a past date never gets the softer label. Stale is last because a contract with a real deadline problem should say so, and "Stale" only matters when nothing sharper applies.

Part 4: Walk the formula through each case

Assume today is Monday 2026-10-05 and Stale Days is 10. Due this week means a due date from today through 2026-10-12.

The Days Waiting figures check out by hand. From 2026-09-28 to 2026-10-05 is 7 days. From 2026-10-01 is 4. From 2026-09-21 is 9 days to the end of September plus 5, which is 14. From 2026-10-02 is 3. From 2026-09-26 is 9 days (4 to the end of September plus 5).

Part 5: Add the Summary formulas

B2, Action Count

=COUNTIF(Queue!H2:H200,"OVERDUE")+COUNTIF(Queue!H2:H200,"Due this week")+COUNTIF(Queue!H2:H200,"Missing date")+COUNTIF(Queue!H2:H200,"Stale")

C2, Open Count

=B2+COUNTIF(Queue!H2:H200,"OK")

Open Count adds the "OK" rows to the four action labels. It counts labels the formula writes only for filled, open rows. A formula that counted every non-empty cell in column H would also count the blank-looking cells that formulas leave in unused rows, and Closed rows would slip in.

D2, Digest Lines

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Queue!I2:I200,(Queue!H2:H200="OVERDUE")+(Queue!H2:H200="Due this week")+(Queue!H2:H200="Missing date")+(Queue!H2:H200="Stale"))),"Nothing needs action this week")

FILTER keeps the Digest Line of every row whose Stage is one of the four action labels. Every range runs from row 2 to row 200, so they are the same height. TEXTJOIN stacks the kept lines with a line break (CHAR(10)) between them and skips any blank. When nothing matches, FILTER returns an error and IFERROR substitutes the "Nothing needs action this week" message.

With the sample rows above, the Summary row reads:

• Action Count: 4 (Northwind, Contoso, Fabrikam, Tailspin)
• Open Count: 5 (those 4 plus Litware; Adventure Works is closed)
• Digest Lines:

Northwind MSA | With counterparty | Chase redline | due 2026-10-02 | OVERDUE
Contoso NDA | With legal | Send first turn | due 2026-10-09 | Due this week
Fabrikam DPA | With counterparty | Await comments | due 2026-10-30 | Stale
Tailspin renewal | Awaiting signature | Chase signature | due none | Missing date

Also open File, Settings, Calculation and set recalculation to "On change and every hour", so TODAY() is fresh when the Zap reads the sheet on Monday.

Part 6: Build the Zap

Create a new Zap with three steps.

1. Trigger: Schedule by Zapier, Every Week. Choose Monday and a time before you start work, and set your time zone.
2. Action: Google Sheets, Lookup Spreadsheet Row. Connect your Google account. Choose your spreadsheet and the Summary worksheet. Set Lookup Column to Key and Lookup Value to the text summary. If the step offers an option to create a row when none is found, leave it off. Test the step: it should return Action Count, Open Count and Digest Lines.
3. Action: Gmail, Send Email. In To, type your own address and no one else's. Leave Cc and Bcc empty. Set the subject to something like Contract queue: followed by the Action Count field from step 2. In the body, add the Open Count and Action Count fields from step 2, then a blank line, then the Digest Lines field.

Nothing in this Zap sends to a business team, a counterparty or outside counsel. The digest goes to you only.

Part 7: Test and refine

Run the Zap test from step 3 and check your inbox. Compare each line with the Queue tab. If the lines run together, look in the Send Email step for a body format setting. Then change one Status to Signed, wait for the sheet to update, and confirm the row disappears from Digest Lines. Turn the Zap on.

On billing: Zapier counts a task for each successful action step. A run that completes uses two tasks (the lookup and the email). The schedule trigger is not an action.

Real Example: A Sole Counsel's Monday

Setup: The queue holds six contracts for a mid-size company, and Stale Days is 10. Input: On Monday 2026-10-05 the Zap fires on schedule. Output: The email subject reads "Contract queue: 4". The body states 5 open contracts and 4 needing action, followed by the four lines shown above. Litware does not appear because it is "OK", and Adventure Works does not appear because it is signed. Time saved: Most of the Monday scan through the inbox and the contract folder. Estimate your own figure after a month.

What to Do When It Breaks

• No email arrived on Monday → Silence is the failure to watch for, because nothing alerts you. In Zapier, open Zap History and check the Monday run. If there is no run, the Zap is switched off or the schedule was edited. If the run shows "Safely halted" at the lookup, the Key cell no longer says summary or the worksheet name changed. If the run shows an error, reconnect your Google account, because the connection expires from time to time. Put a recurring Monday reminder on your calendar for the first month so a missing email gets noticed.
• Every contract shows Stale → The Stale Days cell (Summary F2) is empty or holds text. Enter a number.
• A contract still shows after you signed it → The Status cell does not match "Signed" or "Dead" exactly. Re-select it from the dropdown.
• Stage shows #REF! or an error → Check that the tabs are named exactly Queue and Summary. A renamed tab breaks the formulas.
• Digest dates look like 46300 → The TEXT wrapper was lost when you edited the formula. Restore it.
• A contract past row 200 never appears → Extend the formulas and all the Summary ranges together, so every range stays the same height.

Variations

• Simpler version: Skip Zapier. Open the Summary tab every Monday and read the Digest Lines cell yourself.
• Extended version: Add a second Zap on Friday that reads the same Summary row, and let business owners request a review through a Google Form. Form answers land on their own responses tab, so copy each new request into the Queue tab yourself. The Zaps still run on a schedule.

What to Do Next

• This week: Enter every open contract and run the Zap once by hand.
• This month: Adjust Stale Days until the digest is short enough to act on.
• Advanced: Build the entity and filing deadline reminder on the same pattern.

Advanced guide for corporate attorney professionals. These techniques use more sophisticated features that may require paid subscriptions. The digest is a reminder list for your own review and does not replace your judgment about any contract.