Use Gemini in Google Sheets to Build a Data Collection Tracker With Overdue Flags
For Sustainability Consultants ·
What This Does
During a greenhouse gas inventory you chase data from many owners. A tracker tab shows who owes what and what is late. Gemini in Google Sheets can write the overdue formula, add the Status dropdown and set a color rule. You test each one on rows where you already know the answer.
Before You Start
- A Google Sheet open in Google Sheets. Google's page says Gemini works best with native Google Sheets files, and for an Excel file you choose File, then Save as Google Sheets.
- An eligible Google Workspace or Google AI plan, as the Google page states. The Gemini in Sheets help page for Workspace accounts is separate from the Workspace Experiments page, which is for personal accounts. If Ask Gemini does not appear, ask your Workspace admin.
- Your tracker holds no more than it needs. Use a site code and not a street address. Use a role or team label for Owner, not a person's name. No utility account numbers. If the client is under an NDA, its terms decide what client data may go into a Google account.
Steps
1. Set up the Requests tab
Create a tab called Requests with these headings in row 1:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| Site | Data Type | Owner | Requested On | Due | Status | Flag |
Site is a site code. Data Type is a short label such as Electricity or Fuel. Owner is a role or team label, such as "Facilities team".
2. Open Gemini
At the top right, click Ask Gemini. A side panel opens with a prompt box. Type a prompt and press Enter, as the Google page describes. Gemini shows a result. For formulas, click Insert to put it in the sheet. Retry gives a different version.
3. Ask for the Status dropdown
Prompt: "In column F of the Requests tab, rows 2 to 200, create a dropdown with the options Not sent, Requested, Received, Checked and Not applicable." The page lists creating dropdowns as an action Gemini can perform. It shows an action preview card first. Click Apply and use Undo if the result is wrong.
4. Ask for the overdue flag formula
Prompt: "In G2 write a formula. If A2 is blank, show nothing. If F2 is Received, Checked or Not applicable, show nothing. If E2 is blank, show No due date. If E2 is before today, show Overdue. If E2 is within the next 7 days, show Due soon. Otherwise show nothing." Gemini may give you something like this, which is the result to compare with:
=IF(A2="","",IF(OR(F2="Received",F2="Checked",F2="Not applicable"),"",IF(E2="","No due date",IF(E2<TODAY(),"Overdue",IF(E2<=TODAY()+7,"Due soon","")))))
With G2 selected, click Insert and fill the formula down the column. If a cell shows an error, the page says you can hover over it and click Fix.
5. Ask for the conditional format
Prompt: "Highlight the whole row in light red when column G says Overdue, and in light yellow when it says Due soon." The page lists conditional formatting as an action and says a Settings button may appear so you can adjust the rule. Click Apply on the preview card.
6. Test on rows whose answer you know
Enter these test rows. For the dates use formulas so the test stays valid on any day:
| Site | Data Type | Owner | Requested On | Due | Status | Expected flag |
|---|---|---|---|---|---|---|
| S01 | Electricity | Facilities team | =TODAY()-20 | =TODAY()-5 | Requested | Overdue |
| S01 | Natural gas | Facilities team | =TODAY()-3 | =TODAY()+7 | Requested | Due soon |
| S02 | Water | Site operations | (blank) | (blank) | Not sent | No due date |
| (blank row) | (nothing) | |||||
| S02 | Waste | Site operations | =TODAY()-30 | =TODAY()-10 | Received | (nothing) |
Every flag must match the last column, and the colors must appear on the first two rows only. If any row is wrong, fix the formula before you use the tracker with real data. Delete the test rows afterward.
Real Example
Scenario (all data invented): Company A has two sites, S01 and S02. You are chasing electricity, gas, water and waste data for the year.
What you do: Build the tab, apply the dropdown and formula, and enter the five test rows above.
What you get: The first row reads Overdue and is shaded red. The second reads Due soon. The third reads No due date. The blank row stays empty. The closed waste row stays empty even though its due date has passed. Now you replace the test rows with the real requests and share the tab with your team, with this same sheet feeding any digest you build later.
Tips
- Save generated formulas in the sheet. The Google page says you lose Gemini conversation history when you reload, close the sheet or go offline.
- Ask Gemini for one change at a time. You can see which instruction caused which result.
- Keep the Status words exactly as in the dropdown, because the formula compares text.
Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area. Google's current page: support.google.com/docs/answer/14356410, "Collaborate with Gemini in Google Sheets".