Contract register with deadline tracking in Gemini for Sheets

A contract register earns its keep in one moment: the week before a contract lapses or silently renews. Google Sheets with Gemini covers that well. The sheet does the date arithmetic with a formula and colors, and Gemini reads the whole register and sorts it into overdue, expiring, open-ended and suspicious contracts, each with a recommended action and a priority. The initial setup takes about 30 minutes; after that, the weekly review is a few minutes.

Columns that the review depends on

Create a table with these columns: number, counterparty, type, signing date, valid until, amount, status, owner, auto-renewal, and days to expiry. The last one is a formula: the "valid until" date minus TODAY(). Two columns do most of the work. "Owner" makes sure every warning lands on a person. "Auto-renewal" catches the contracts nobody thinks about, the ones that roll over for another year because no one sent a notice in time.

Before adding anything else, check that "valid until" contains real dates. Imported registers often store dates as text, and then the days column shows errors or, worse, plausible wrong numbers. Gemini will read those numbers and build its whole analysis on them without complaint.

Colors first, AI second

Add conditional formatting: red for overdue, yellow for under 30 days. This part needs no AI and works every time you open the file. Gemini adds judgment on top: which open-ended contracts deserve a review, which rows look like duplicates, where the amount or dates look odd for the type of contract.

The prompt

Role: you are a corporate lawyer who controls contract deadlines. Context: a contract register in the selected range with columns number | counterparty | type | signing date | valid until | amount | status | owner | auto-renewal | days to expiry. Today is {{date}}. Task: identify 1) overdue contracts, 2) those expiring within 30 days, 3) open-ended and auto-renewing contracts worth reviewing, 4) possible duplicates or anomalies in amounts and dates. For each give a recommended action and a priority. Format: a table number | counterparty | valid until | days | category | action | priority (high, medium, low). At the end, a plan for the week for the three highest-priority contracts.

Always fill {{date}} explicitly. The model has no reliable sense of today's date, and a review run on the wrong date shifts every category.

What to do

1. Create the register with number, counterparty, type, signing date, valid until, amount, status, owner, auto-renewal.
2. Convert "valid until" to real dates and add days to expiry with a TODAY() formula.
3. Add conditional formatting: red for overdue, yellow for under 30 days.
4. Select the range, open the Gemini panel and run the prompt with today's date.
5. Recalculate the term of one contract by hand and compare it with the result.
6. Enter confirmed actions in an "action" column and set reminders for the owners.
7. Repeat the review every week.

Signs the register works

Every contract expiring within 30 days has an owner and an action, your manual check of one term matched, and the register gets updated weekly. If the Gemini table lists a contract as expiring and your red and yellow formatting disagrees, trust the formula and look at the date format in that row.

Duplicates deserve a human look. Two rows with the same counterparty and amount may be a real duplicate, or an annex that someone entered as a new contract. Gemini flags them; the decision comes from the documents.

Where to keep it and what it costs

A register with real counterparties and amounts belongs in a corporate Workspace. Business Standard costs 14 USD per user with annual billing and keeps company data out of model training; the personal option is Google AI Pro at 19.99 USD per month. See Gemini in Google Sheets pricing. Accountants often own this register too; their version is on the contract register page for accountants. If your company runs on Microsoft, compare Gemini in Google Sheets with Copilot in Excel.