Use Gemini in Google Sheets to build status formulas for a contract tracker

What This Does

You keep a contract tracker and want two calculated columns: how many days each contract has been waiting, and a stage label. Gemini in Sheets writes formulas from a plain description. Gemini can get a formula wrong in ways that look right, so this guide ends with a test on five sample rows before you rely on any of it.

Before You Start

• Google's support page on collaborating with Gemini in Google Sheets says formula creation requires an eligible Google Workspace or Google AI plan. Plans are described at gemini.google.com. If you do not see Ask Gemini at the top right of a spreadsheet, ask your Workspace admin.
• The page also says Gemini works best with native Google Sheets files. If your tracker is an Excel file, open it and use File, then Save as Google Sheets.
• Use short codes or generic names in the Contract column if the sheet is shared widely. Counterparty names, deal terms and notes about a negotiation can be confidential, and an NDA may restrict what you share with a service provider. Check your company's policy on using Gemini with confidential material.

Steps

1. Set up the tracker

Create these columns in row 1: Contract, Status, Due Date, Last Touched. Put dates in as real dates, not text. Use a fixed list of Status values such as New, In review, With counterparty, and Closed, so a formula can rely on them.

2. Open Gemini and describe the tracker

At the top right, click Ask Gemini. Google's page says you can also start from a cell by entering = and using a keyboard shortcut (Ctrl + Alt + g on Windows, Command + Control + g on Mac). Write a prompt that refers to your columns:

"My tracker has Contract in column A, Status in B, Due Date in C and Last Touched in D, with headers in row 1. Create a formula for a Days Waiting column that shows the number of days between Last Touched and today. It must be blank when Last Touched is blank or Status is Closed."

Gemini proposes a formula. To place it, click the cell you want and click Insert, as the page describes. Use Retry if you want a different version.

3. Ask for the Stage column

Open a new prompt:

"Create a formula for a Stage column. If Status is Closed, show Done. If Status is With counterparty, show Waiting on them. If the Due Date is filled in and earlier than today, show Overdue. If none apply, show Active."

State the order of the rules, because the first rule that matches wins. Insert it in the Stage column and fill it down the rows.

4. Test the formulas on five rows

Add five test rows at the bottom (you can delete them afterward) and work out the right answer for each before you look at the formula result:

1. Status In review, Last Touched five days ago (enter it as today minus 5). Expect a Days Waiting of 5 and a Stage of Active, or Overdue if you give it a past due date.
2. Status With counterparty, no due date. Expect Waiting on them.
3. A completely blank row. Expect blanks in both columns. A common failure shows a very large number, because a blank date reads as zero.
4. Status Closed with a Last Touched date. Expect a blank Days Waiting and a Stage of Done.
5. Status In review with a Due Date of yesterday. Expect Overdue.

If any result is wrong, tell Gemini what you entered, what you expected and what you got, and ask it to fix the formula. Then run all five tests again. Google's page also lists a Fix option when a cell shows an error message.

5. Remove the test rows and use the tracker

Delete the test rows. Keep a note on the sheet saying which formulas Gemini wrote and the date you tested them.

Real Example

Scenario: Your tracker has 40 rows, and you suspect two stale contracts are hidden among them.

What you do: Ask Gemini for the two formulas, insert them, and test with the five rows above.

What you get: The Days Waiting formula passes four tests but returns a large number on the blank row, so you ask Gemini to treat a blank Last Touched as blank. You re-run all five, they pass, and you sort by Days Waiting. The two stale contracts rise to the top.

Tips

• Gemini's formulas depend on what you wrote. If your Status values change, check that the formulas still match.
• A formula that uses today's date changes every day. That is what you want here, but it means saved numbers in an old copy will be out of date.
• This guide is a stand-alone tracker. A later guide builds a weekly digest on the same kind of sheet.

Tool interfaces change. If a button has moved, look for the Ask Gemini option or similar AI options in the same area of the spreadsheet.