Use Gemini in Google Sheets to Build a Document Request Tracker

What This Does

Records you have asked for, and who owes them, are easy to track badly and hard to track well. This guide builds a small Requests tab where each row is one request, a Days Left column does the date math, and conditional formatting makes overdue and soon-due rows stand out. Gemini writes the dropdown, the formula and the formatting rules from plain-language descriptions. You test each one on rows whose answer you already know.

This is the tab the Level 4 weekly request digest reads. That guide adds a Summary tab, a Flag column and a Digest Line column to this same sheet, so build it with the layout below.

Before You Start

• Gemini in Sheets is available to you. Google's page says the feature requires an eligible Google Workspace or Google AI plan. The registry lists Business Standard ($14/user/month) as a Google Workspace starting point, but eligibility is Google's call. Look for the Ask Gemini button at the top right of a spreadsheet, and ask your IT admin if it is missing.
• Your employer's AI policy allows Gemini in Workspace on the document you are building. This sheet holds codes, short labels and dates only, which keeps it low risk. No names, account numbers, amounts or findings go in it, and none go into a prompt. Check how your Workspace handles Gemini conversation history and whether prompts are used for training, because settings differ by plan and by admin.
• You work in a native Google Sheets file. Google's page says Gemini works best with native Sheets files, with a separate option for Excel (.xlsx) files.

Steps

1. Set up the tab and the headers

Create a new spreadsheet. Name the first tab Requests. In row 1, type these headers in columns A through G:

Request is a code such as "R-07". Case is a code such as "2026-014". Holder is a short label such as "Bank B" or "AP". Leave columns H and I empty for the Level 4 guide. The due dates are the ones you set from your own procedures and your supervisor. The sheet states no deadline rule.

2. Open Ask Gemini

At the top right of the spreadsheet, click Ask Gemini. A side panel opens with suggested prompts and a prompt box. Per Google's page, you can select a suggestion or write your own. Click Undo if Gemini changes something you did not want, and use Show code under a data answer if you want to see how it got there.

3. Ask for the Received dropdown

In the Requests tab, add a dropdown to column F, rows 2 to 200, with the choices Yes and No.

Google's page lists adding a dropdown among the actions Gemini can perform. If it does not, Sheets' own data validation settings produce the same list, and you can describe the goal to Gemini again.

4. Ask for the Days Left formula

In column G of the Requests tab, write a formula for row 2 that gives the Due Date in column E minus today's date. Leave the cell blank when column A is blank or column E is blank.

Gemini should return something like =IF(OR(A2="",E2=""),"",E2-TODAY()). Read it before inserting it. Insert it in G2 and fill it down to row 200. If the result shows as a date, change the column format to a plain number.

5. Ask for the conditional formatting

Describe both rules and name the columns by header and letter.

In the Requests tab, rows 2 to 200: highlight the whole row in red when Request is not blank, Received is not Yes, Days Left is not blank and Days Left is less than 0. Highlight the whole row in yellow when Request is not blank, Received is not Yes, Days Left is not blank and Days Left is between 0 and 7.

Google's page lists conditional formatting among the Gemini actions. Open the formatting rules after Gemini applies them and read the conditions. They should resemble =AND($A2<>"",$F2<>"Yes",$G2<>"",$G2<0) for red, and the same test with $G2>=0,$G2<=7 for yellow. The 7 is your choice of window, and the Level 4 digest has its own setting cell for it.

6. Test on rows whose answer you know

Type four test rows below your headers, using formulas for the dates so the answer holds on any day:

Only the first six columns are typed. The last two columns are what you should see. If any row differs, paste the formula back into the Gemini panel with the row and what you expected, or fix the formula by hand. Add one more row due in exactly 7 days and one in 8 to confirm the boundary: the 7-day row should be yellow and the 8-day row should not. Delete the test rows when you are done.

Real Example

Scenario: You track requests across two cases. Every value here is an invented code or label.

What you type into the sheet: R-07, Case 2026-014, Holder Bank B, a requested date, a due date, Received No. Then R-11, Case 2026-014, Holder AP, Received No.

What you get: Days Left calculates itself, a past-due row turns red, and a row due in the next 7 days turns yellow. When a record arrives, change Received to Yes and the row loses its highlight, though Days Left keeps showing its number. If you want that to read blank for received rows, the Level 4 guide's Flag column handles it.

Tips

• Keep amounts, names and findings out of this tab. A code, a short holder label and two dates are enough to run the follow-up.
• If a Gemini formula uses a function you do not know, ask it to explain the formula, then test the explanation on the test rows.
• Gemini's conversation history may be lost when you close the spreadsheet, per Google's page. Your formulas and rules stay in the sheet, so save the prompts you liked in your own notes.

Tool interfaces change. If a button has moved, look for Ask Gemini, a Gemini icon or a sparkle icon near the top right of the sheet. Source: Google's "Collaborate with Gemini in Google Sheets" help page (support.google.com/docs/answer/14356410), reviewed 2026-10-13.