Build a cross-client deadline tracker with Gemini in Google Sheets
For Copyeditor / Proofreader (Freelance / Publishing)s ·
What This Does
When three clients each want something this month, the deadline that bites is the one you forgot. A single sheet with one row per stage of each project, a Days Left column and colour on the rows that are late or close puts every client in one view. Gemini in Sheets can build the dropdowns, the formula and the formatting from a plain request, and you check the results.
This is the sheet that the Level 4 weekly deadline digest reads from. The digest adds a Summary tab to this same file, so build the columns exactly as listed here.
Before You Start
- You use Google Sheets. Google says Gemini in Sheets works best with native Sheets files, so a spreadsheet saved as .xlsx needs File, then Save as Google Sheets first.
- Your account has Gemini. Google's page says the feature requires an eligible Google Workspace or Google AI plan. The plan this guide's template names is Business Standard ($14/user/month), and you can ask your admin or check Google's plan page if the Ask Gemini button is missing.
- Decide to keep codes in the sheet. Project is a code such as "P104" and Client is a code such as "Client B". No titles, no author names, no contact details. Deadlines do not need them, and the Level 4 digest later emails this sheet's rows to you.
- Your own client list or a made-up one is all you need. No manuscript text goes into this sheet or into Gemini.
Steps
1. Make the Deadlines tab
Name a tab Deadlines and type these headers in row 1, columns A to E: Project, Client, Stage, Due Date, Done. Leave column F for Days Left.
2. Open Gemini
At the top right of the sheet, click Ask Gemini. A side panel opens with a prompt box at the bottom. Type a prompt and press Enter. Google's page says you can click Undo to reverse an action, so try things freely.
3. Ask for the dropdowns
Create a dropdown in column C from row 2 down with these options: Copyedit, Author review, Cleanup, First proofs, Second proofs, Index check, Other. Create a dropdown in column E from row 2 down with the options No and Yes.
Click one cell in each column and confirm the list appears.
4. Ask for Days Left
In column F, put a formula that gives Due Date minus today's date, and leaves the cell blank when Project or Due Date is blank. Fill it down to row 200.
A formula that does this looks like =IF(OR(A2="",D2=""),"",D2-TODAY()). Gemini may write it another way, which is fine as long as the answers match. If a cell shows an error, hover over it and click Fix, as Google's page describes.
5. Ask for the colours
Add conditional formatting to rows 2 to 200. Highlight the whole row red when Done is No and Days Left is below 0. Highlight it amber when Done is No and Days Left is 0 to 7. Do not highlight any row where Done is Yes or where Days Left is blank.
6. Test with rows whose answers you know
Do not trust the formula until it has passed tests where you know the answer. Type these into rows 2 to 5, with the date cells as formulas so the test stays valid on any day:
| Project | Client | Stage | Due Date | Done | Days Left should be | Colour should be |
|---|---|---|---|---|---|---|
| P104 | Client B | Copyedit | =TODAY()+3 | No | 3 | Amber |
| P105 | Client C | Cleanup | =TODAY()-2 | No | -2 | Red |
| P106 | Client B | First proofs | =TODAY()-5 | Yes | -5 | None |
| (blank) | (blank) | (blank) | (blank) | (blank) | blank | None |
All four must pass. Then replace the test rows with real due dates, and delete the formulas in the Due Date column.
Real Example
Scenario: Invented sample, with an invented current date of 2026-10-14.
| Project | Client | Stage | Due Date | Done | Days Left | Colour |
|---|---|---|---|---|---|---|
| P104 | Client B | Copyedit | 2026-10-17 | No | 3 | Amber |
| P105 | Client C | Cleanup | 2026-10-12 | No | -2 | Red |
| P106 | Client B | First proofs | 2026-10-09 | Yes | -5 | None |
| P107 | Client D | Author review | 2026-10-30 | No | 16 | None |
From 2026-10-14 to 2026-10-17 is three days. From 2026-10-12 to 2026-10-14 is two days, so P105 is overdue by 2. From 2026-10-09 to 2026-10-14 is five days, and the row is done, so it stays plain. From 2026-10-14 to 2026-10-30 is sixteen days, beyond the seven-day window.
Tips
- Gemini may say it cannot do something, or do half of it. Click Retry or rephrase the request, and test again.
- Keep the column order. The Level 4 digest guide expects Project, Client, Stage, Due Date, Done in columns A to E.
- Gemini's conversation history is lost when you reload the page, so the sheet is the record. Write down the formula you settle on.
- Dates in the sheet are your dates from your contracts and emails. Gemini never sets one.
Tool interfaces change. If a button has moved, look for the Gemini icon or Ask Gemini near the top right of Sheets.