Use Gemini in Google Sheets to build a Lab Results Watch tracker

What This Does

Samples go to the lab, the lab confirms a due date, and somewhere in the next few weeks a batch goes quiet. This guide builds a tracker called Lab Results Watch in Google Sheets. It lists each batch you shipped, flags the ones that are due soon, overdue or missing a date, and totals them on a Summary tab. Gemini in Sheets can set up the tabs, the yes or no dropdown and the colors. You paste every formula yourself and test it on rows whose answers you know.

The tracker holds batch codes, project codes, short lab names and dates. It never holds results, site names or client names.

The Level 4 guide "Daily lab results watch" reads this exact spreadsheet. It looks up the row on the Summary tab and emails the totals. Keep every tab name, header and formula below as written, so that digest works without changes.

Before You Start

• You have a native Google Sheets file open in a browser on a computer. Google's page says Gemini in Sheets works best with native Google Sheets files. For an Excel (.xlsx) file, choose File, then Save as Google Sheets first.
• Your plan includes it. 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, so check for the Ask Gemini button at the top right and ask IT if it is missing.
• You know your firm's AI policy for work files. This tracker needs no results. Use codes and dates only. Lab reports, chain-of-custody forms, site names and client names stay in the firm's approved systems. Check the data settings for your Google account or Workspace, including whether prompts are used for training and how long activity is kept, and ask your IT group if you are unsure.
• The due dates come from the lab's own confirmation. You type them. This guide states no lab turnaround.
• Gemini's suggestions are proposals. It changes nothing until you click Insert or Apply.

Steps

1. Create the Batches tab

Name the spreadsheet Lab Results Watch. Rename the first tab Batches. Type these headers in row 1, in this order:

Format columns D and E as dates (yyyy-mm-dd). Lab holds a short name such as "Lab A".

2. Create the Summary tab

Add a second tab named Summary. Type these in row 1: A1 Key, B1 Due Soon Count, C1 Due Soon Batches, D1 Overdue Count, E1 Overdue Batches, F1 Missing Date Count. In A2 type the word summary, in lowercase. In A4 type Look ahead (days), and in B4 type 2.

3. Open Gemini

At the top right of the spreadsheet, click Ask Gemini. A side panel opens with suggested prompts and a prompt box at the bottom. Type your prompt and press Enter. Google's page says you can focus Gemini on specific tabs with the dropdown in the prompt box, so keep only the tab you are working on selected.

4. Ask Gemini for the dropdown and the colors

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

Add a dropdown to cells F2 to F301 on the Batches tab with the options yes and no.

Gemini shows an action preview card. Read it. Then click Apply. If it lands in the wrong place, click Undo and name the range again. Then ask for the colors:

On the Batches tab, apply conditional formatting to G2 to G301. Make cells that contain OVERDUE red, cells that contain DUE SOON yellow, and cells that contain MISSING DATE orange.

Check the preview. Then click Apply. You can adjust the rules afterwards with the Settings button, if it appears.

5. Paste the Flag formula

Do not rely on Gemini to word this one. Click G2 and paste:

=IF(A2="","",IF(F2="yes","",IF(E2="","MISSING DATE",IF(E2<TODAY(),"OVERDUE",IF(E2-TODAY()<=Summary!$B$4,"DUE SOON","")))))

Fill it down to G301. A blank row returns a blank and is never counted. A batch marked yes in Received returns a blank. If you want Gemini's help, ask it to explain this formula in plain words. Google's page says Gemini can create formulas, and its Fix option appears when a formula shows an error.

6. Paste the Summary formulas

On the Summary tab, paste these into the cells named:

B2:

=COUNTIF(Batches!G2:G301,"DUE SOON")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Batches!A2:A301&" "&Batches!B2:B301&" ("&TEXT(Batches!E2:E301,"yyyy-mm-dd")&")",Batches!G2:G301="DUE SOON")),"none")

D2:

=COUNTIF(Batches!G2:G301,"OVERDUE")

E2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Batches!A2:A301&" "&Batches!B2:B301&" ("&TEXT(Batches!E2:E301,"yyyy-mm-dd")&")",Batches!G2:G301="OVERDUE")),"none")

F2:

=COUNTIF(Batches!G2:G301,"MISSING DATE")

7. Set the calculation mode

The flags depend on TODAY(), so keep recalculation on. Click File, then Settings, then Calculation. Under Calculation Mode, choose Automatic. Then click Save settings. Google's page lists only Automatic and Manual modes. It describes no scheduled recalculation, so if the counts look stale, open the sheet and look again before you rely on them.

8. Test with the seven rows

Type the seven invented test rows from the Real Example. The expected Summary values assume a sheet date of Monday 2026-10-19 (invented). On any other day, temporarily retype G2 with TODAY() replaced by DATE(2026,10,19) and fill it down. Check the Summary against the expected values. Then paste the G2 formula from step 5 back and fill it down again. Delete the test rows when you finish.

If C2 shows none while B2 counts more than zero, or shows only part of the list, wrap the joined text expression inside FILTER in ARRAYFORMULA, retest, and keep the output text the same.

Real Example

Scenario: Project codes P-14, P-15 and P-16, short lab names Lab A and Lab B. Everything below is invented. Look ahead (B4) is 2 days, and the sheet date is Monday 2026-10-19 (invented).

Expected Summary values:

• B2 Due Soon Count: 3
• C2 Due Soon Batches: LB-01 P-14 (2026-10-20), LB-02 P-14 (2026-10-21), LB-07 P-14 (2026-10-19)
• D2 Overdue Count: 1
• E2 Overdue Batches: LB-04 P-15 (2026-10-16)
• F2 Missing Date Count: 1

If any value differs, fix the formula before you connect anything to it.

Tips

• Type the results-due date from the lab's confirmation the day you ship. A blank due date shows as MISSING DATE, which is the prompt to ask the lab.
• When a lab reports a batch, change Received to yes. The flag clears the next time the sheet calculates.
• Change B4 when you want a longer or shorter look ahead. Every flag reads that one cell.
• Go one step past tracking. Ask Gemini in the side panel which project code has the most batches flagged OVERDUE or MISSING DATE, and confirm the answer with a COUNTIFS of your own. A project that keeps showing up deserves a call to the lab about process, not only about one batch.
• Keep this spreadsheet separate from your results files. A separate file means the digest never reads a result.

Tool interfaces change. If a button has moved, look for similar AI or Gemini options in the same area of the window.