Build a Proposal Deadline Tracker with Gemini in Google Sheets
For Grant Manager / Sponsored Programs Administrators ·
What This Does
A tracker that shows what is overdue and what is due this week saves you from reopening every proposal. Gemini in Sheets can add a status dropdown, write a days-to-deadline formula and set up conditional formatting from plain-English requests. You end up with a Proposals tab that a person can read at a glance.
This is the same tab the Level 4 weekly deadline digest reads. That guide adds a Summary tab to this same spreadsheet, so build the columns below exactly as listed.
Before You Start
- You have an eligible Google Workspace or Google AI plan. Google's help page says Gemini in Sheets requires one. For reference, Business Standard is listed at $14/user/month, but check Google's page and ask your IT group which plans your institution holds.
- Your spreadsheet is a native Google Sheet. Google's page says Gemini works best on native Sheets files, and that an Excel file (.xlsx) needs File, then Save as Google Sheets first.
- The tracker holds codes and dates only: proposal codes, PI codes, short sponsor program codes. No names, amounts or salaries. Whether even that may go into Gemini depends on your institution's AI guidance and an approved account. If it does not allow it, build the tab by hand from the formulas below, or practice on the invented sample.
Steps
1. Find the AI feature
Open the spreadsheet from Google Sheets and click Ask Gemini at the top right. A side panel opens with a prompt box. Type a request and press Enter. Google's page also lists Undo and Retry in that panel, so you can reverse a change or ask for a different version.
2. Tell it what you need
First, set up the headers yourself in row 1 of a tab named Proposals, in this order:
| Column | Header |
|---|---|
| A | Proposal |
| B | PI |
| C | Sponsor Program |
| D | Internal Deadline |
| E | Sponsor Due Date |
| F | Missing Items |
| G | Status |
| H | Days to Internal |
Format columns D and E as dates. Then make three requests, one at a time.
Status dropdown. "In column G, rows 2 to 200, add a dropdown with these options: In progress, Routed, Submitted, Withdrawn." Google's page lists adding a dropdown among the actions Gemini can perform.
Days to Internal. "In column H, add a formula for each row from row 2 to row 200 that shows the number of days from today to the date in column D. Leave the cell blank if column A or column D is blank." A correct answer looks like this:
=IF(OR(A2="",D2=""),"",D2-TODAY())
Gemini may suggest something different. Click Insert only after you read the formula and confirm it does the same thing.
Conditional formatting. "Highlight the whole row in red when column H is below 0, and in amber when column H is from 0 to 7. Do not highlight rows where column G is Submitted or Withdrawn." Google's page lists applying conditional formatting among the actions.
3. Review and use the result
Test every formula and rule on rows whose answers you already know, using the sample below. Then open Format, then Conditional formatting and read each rule. Gemini builds the rule, but you confirm that the range, the conditions and the colors say what you meant.
Keep column headers exactly as listed. The Level 4 digest looks columns up by name, so renamed or reordered headers can break it.
Real Example
Scenario: Test rows (all invented). Assume today is 2026-10-10.
| Proposal | PI | Sponsor Program | Internal Deadline | Sponsor Due Date | Missing Items | Status |
|---|---|---|---|---|---|---|
| P-101 | PI A | Sponsor X | 2026-10-13 | 2026-10-20 | Biosketch | In progress |
| P-102 | PI B | Sponsor Y | 2026-10-08 | 2026-10-15 | Letters | Routed |
| P-103 | PI C | Sponsor X | 2026-10-05 | 2026-10-12 | Submitted | |
| (blank row) | ||||||
| P-104 | PI A | Sponsor Z | 2026-10-30 | 2026-11-06 | Biosketch, Letters | In progress |
Expected results, worked out by hand:
- P-101: Days to Internal is 3, and the row turns amber.
- P-102: Days to Internal is -2, and the row turns red (2 days past).
- P-103: Days to Internal is -5, but the row stays unhighlighted because Status is Submitted.
- Blank row: Days to Internal stays blank, with no highlight.
- P-104: Days to Internal is 20, with no highlight.
What you do: Type the five rows into a copy of the tab and compare. If P-103 turns red, the Submitted exclusion is missing, so ask Gemini to correct the rule and test again. Delete the test rows, or save them on a separate tab, before you enter real proposals.
Tips
- Dates must be real dates, not text. A left-aligned date cell is a warning sign.
- TODAY() recalculates when the sheet recalculates, so the Days to Internal values for your real rows move each day.
- Google's page notes that Gemini conversation history is lost when you reload or close the sheet, so insert the output you want to keep.
- Tell your team to use the dropdown values exactly. The Level 4 digest reads the Status text.
Tool interfaces change. If a button has moved, look for Ask Gemini or a Gemini icon near the top right of Google Sheets, and check Google's current help page for your plan.