Use Gemini in Google Sheets to build a Notice Response Watch spreadsheet

What This Does

Notices from the agency and the court carry dates, and a missed internal target costs you a scramble. This guide builds a small spreadsheet called Notice Response Watch: a Notices tab where you log each notice by matter code, and a Summary tab that counts what is coming up, what is past your own target and what has a missing date. Gemini in Sheets can set up the tabs, the yes or no dropdown and the highlighting. You paste every formula yourself and check it against invented rows with known answers. The Summary tab is also what the Level 4 daily notice response watch reads, so the layout below must match exactly.

Before You Start

• You have a Google account and can create a new spreadsheet in Google Sheets.
• Gemini in Sheets is available. Google's help page says it requires an eligible Google Workspace or Google AI plan. The entry Google Workspace listing on this site is Business Standard at $14/user/month. Confirm on Google's page that your own plan includes Gemini in Sheets, and ask your Workspace administrator if the Ask Gemini button is missing.
• Data line. This spreadsheet holds matter codes, notice type codes (RFE, NOID, OTHER), dates and yes or no only. No client names, A-numbers, receipt numbers, countries or case facts go in any cell or any Gemini prompt. Those details stay in your firm's approved systems. Check Google's data settings for your account, including whether prompts are used for training and how long activity is kept, and confirm your firm approved the tool under your confidentiality duties and your state bar's rules on AI use, which you check yourself.
• The response date comes from the notice itself, and you type it in. The internal target date is your own choice. This guide states no response period and no rule about either.
• The tracker is a backstop. Your case management system and your firm calendar remain the record.

Steps

1. Create the spreadsheet and the tabs

Create a new spreadsheet and name it Notice Response Watch. Name the first tab Notices and add a second tab named Summary. Spell both exactly like this, because the formulas and the later automation refer to them by name.

If you prefer, open Gemini first: Google's page says to open a spreadsheet, select Ask Gemini at the top right, and write your own prompt in the side panel. A prompt such as "Create a table with these columns: Matter code, Notice type, Response date, Internal target date, Filed, Flag" works. Use Insert to add the table, or type the headers yourself.

2. Set the headers

On the Notices tab, Row 1:

• A1 Matter code
• B1 Notice type (RFE, NOID or OTHER)
• C1 Response date (read off the notice)
• D1 Internal target date (your own date, before the response date)
• E1 Filed (yes or no)
• F1 Flag

On the Summary tab, Row 1: A1 Key, B1 Target Soon Count, C1 Target Soon Items, D1 Past Target Count, E1 Past Target Items, F1 Response Date Passed Count, G1 Response Date Passed Items, H1 Missing Date Count. In A2 type the single word summary. In A4 type Alert days and in B4 type 5.

3. Let Gemini add the dropdown and highlighting

Google's page lists adding a dropdown and applying conditional formatting among the actions Gemini can perform. Open Ask Gemini and paste:

On the Notices tab, add a dropdown to E2:E301 with the choices yes and no.
Then add conditional formatting for F2:F301: highlight cells containing TARGET SOON in yellow, PAST TARGET in orange, RESPONSE DATE PASSED in red, and MISSING DATE in grey.

Check the result. Click one E cell and confirm the dropdown offers exactly yes and no. Gemini can misread a range, so look at the rules yourself. Use Undo if it went wrong.

4. Paste the Flag formula

In Notices F2, paste this formula and fill it down to row 301:

=IF(A2="","",IF(E2="yes","",IF(OR(C2="",D2=""),"MISSING DATE",IF(C2<TODAY(),"RESPONSE DATE PASSED",IF(D2<TODAY(),"PAST TARGET",IF(D2-TODAY()<=Summary!$B$4,"TARGET SOON",""))))))

The order matters. A passed response date is checked before the internal target, so a notice past its response date never shows only as "past target".

5. Paste the Summary formulas

On the Summary tab, paste each formula in the cell named:

B2:

=COUNTIF(Notices!F2:F301,"TARGET SOON")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Notices!A2:A301&" "&Notices!B2:B301&" (target "&TEXT(Notices!D2:D301,"yyyy-mm-dd")&")",Notices!F2:F301="TARGET SOON")),"none")

D2:

=COUNTIF(Notices!F2:F301,"PAST TARGET")

E2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Notices!A2:A301&" "&Notices!B2:B301&" (target "&TEXT(Notices!D2:D301,"yyyy-mm-dd")&")",Notices!F2:F301="PAST TARGET")),"none")

F2:

=COUNTIF(Notices!F2:F301,"RESPONSE DATE PASSED")

G2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Notices!A2:A301&" "&Notices!B2:B301&" (response "&TEXT(Notices!C2:C301,"yyyy-mm-dd")&")",Notices!F2:F301="RESPONSE DATE PASSED")),"none")

H2:

=COUNTIF(Notices!F2:F301,"MISSING DATE")

If a cell shows an error, hover over it and select Fix. Google's page says Gemini then reviews the formula and analyzes the issue. Compare what Gemini proposes with the formula above before you accept it. A blank row returns blank and is not counted.

6. Test with invented rows

Enter these invented rows on the Notices tab, rows 2 to 9, with B4 on Summary set to 5. The expected flags assume today is Monday 2026-10-26. To test on another day, work in a copy of the spreadsheet and temporarily replace TODAY() in the F formula with DATE(2026,10,26), then restore it on the working copy.

Expected Summary values:

• B2: 3
• C2: IM-26-044 RFE (target 2026-10-28), IM-26-051 NOID (target 2026-10-31), IM-26-049 RFE (target 2026-10-26)
• D2: 1
• E2: IM-26-038 RFE (target 2026-10-23)
• F2: 1
• G2: IM-26-040 OTHER (response 2026-10-22)
• H2: 1

If any value differs, fix the formula before you delete the test rows and start logging real notices.

7. Keep the calculation mode on Automatic

Open File > Settings > Calculation and check that Calculation Mode is Automatic. Google's calculation page lists only Automatic and Manual, and no scheduled recalculation. If the counts look stale when you open the sheet, open the sheet and look again before you rely on them.

Real Example

Scenario: You have eight open notices across your matters and want one place that tells you which ones need attention this week.

What you do: Build the spreadsheet in Steps 1 to 5 and enter the eight invented rows in Step 6.

What you get: The Summary tab reads 3 target-soon items, 1 past target, 1 response date passed and 1 missing date, with the matter codes listed in text. Open the Notices rows behind each flag. For IM-26-040, go to the notice itself and your case file, not to the sheet, to decide what to do.

Tips

• Go one step past tracking. Ask Gemini in the side panel: "Which notice type appears most often in rows flagged PAST TARGET?" Check the answer against the cells. If one notice type keeps slipping, that tells you where your internal targets are too tight.
• The alert days in B4 are your own choice, not a rule from anyone. Change the number and watch the Summary change.
• The Level 4 daily notice response watch reads the Summary tab and emails your own address. Do not rename the Summary tab, the word summary in A2, or the header row.

Tool interfaces change. If a button has moved, look for the Gemini or Ask Gemini option in Sheets, and check Google's current help page.