Build a Quote Follow-Up Tracker in Google Sheets with Gemini
For Franchise Owner-Operator (Service Business)s ·
What This Does
Quotes go out on a Tuesday and slip out of mind by Friday. This sheet lists every quote you sent, marks the ones that are due for a nudge and counts them on a Summary tab. Gemini can set up the tabs and dropdowns for you. You paste the formulas yourself, so you know exactly what they do.
The Summary tab is also what the Level 4 daily quote follow-up digest reads, so build it exactly as written below.
Before You Start
- You have a Google account with Google Sheets open in a browser on a computer
- Your plan includes Gemini in Sheets. Google's help page says the feature requires an eligible Google Workspace or Google AI plan. For Google Workspace plans, such as Business Standard at $14/user/month, see workspace.google.com and confirm which plans include Gemini. If you cannot see an Ask Gemini button, ask whoever manages your Google account
- You use your brand's required system (CRM or field service app) for customer details. This sheet holds only lead codes, service type and dates
Data caution. Customer names, phone numbers, addresses and quote amounts stay in your brand's system. The sheet uses lead codes such as "L-104" that only you can match back to a customer. Before you type anything about real leads into Gemini, check your Google account's Gemini data settings, including whether chats are used to improve models and how long they are kept. For anything beyond codes and dates, check with your franchisor and your own policy first.
Steps
1. Create the spreadsheet and the two tabs
- In Google Sheets, create a new spreadsheet and name it Quote Follow-Up.
- Rename the first tab Quotes.
- Add a second tab and name it Summary.
2. Set up the Quotes tab with Ask Gemini
- On your computer, open the spreadsheet. At the top right, click Ask Gemini.
- In the side panel, type a prompt like this one, then press Enter:
On the Quotes tab, put these headings in row 1: Lead code, Service, Quote sent date, Follow-up done, Booked, Flag. Add a dropdown with the options yes and no to the Follow-up done and Booked columns, rows 2 to 301.
- Gemini shows an action preview card. Read it and click Apply if it looks right. If the result is wrong, click Undo.
Google's page says Gemini can add a dropdown from a prompt and that a new dropdown lands in the rightmost empty column if you do not name a location. Check that the dropdowns ended up in columns D and E.
You can also build all of this by hand. Type the headings in A1 to F1 and use Data > Data validation for the dropdowns.
3. Paste the Flag formula
Click cell F2 on the Quotes tab, paste this formula and press Enter:
=IF(A2="","",IF(OR(D2="yes",E2="yes"),"",IF(C2="","MISSING DATE",IF(TODAY()-C2>=Summary!$B$4,"FOLLOW UP",""))))
Then copy F2 down to row 301. Here is what it does, in order:
- A blank row stays blank.
- A quote that is followed up or booked stays blank.
- A quote with no sent date shows MISSING DATE.
- A quote that is at least as many days old as the number in Summary B4 shows FOLLOW UP.
If you want to understand a formula, open Ask Gemini and ask it to explain the formula in F2 in plain words. Google's page lists creating formulas as a Gemini function, and it shows a Fix option when a formula cell shows an error.
4. Build the Summary tab
Type these on the Summary tab. Use exactly these cells, because the Level 4 digest looks them up.
| Cell | Content |
|---|---|
| A1 | Key |
| B1 | Follow Up Count |
| C1 | Follow Up Codes |
| D1 | Missing Date Count |
| A2 | summary |
| B2 | =COUNTIF(Quotes!F2:F301,"FOLLOW UP") |
| C2 | =IFERROR(TEXTJOIN(", ",TRUE,FILTER(Quotes!A2:A301,Quotes!F2:F301="FOLLOW UP")),"none") |
| D2 | =COUNTIF(Quotes!F2:F301,"MISSING DATE") |
| A4 | Follow up after (days) |
| B4 | 3 |
A2 holds the lowercase word summary, with nothing else in the cell. B4 is the number of days you wait before a nudge. Change it whenever your own routine changes.
5. Test it with seven invented rows
Type these into the Quotes tab, starting in row 2. All of them are invented. The test assumes today is Wednesday 2026-10-14 and B4 is 3.
| Lead code | Service | Quote sent date | Follow-up done | Booked | Flag you should see |
|---|---|---|---|---|---|
| L-101 | [Service A] | 2026-10-06 | yes | no | (blank) |
| L-103 | [Service A] | 2026-10-07 | no | no | FOLLOW UP (7 days) |
| L-104 | [Service B] | 2026-10-09 | no | no | FOLLOW UP (5 days) |
| L-105 | [Service B] | 2026-10-10 | no | yes | (blank) |
| L-106 | [Service A] | 2026-10-11 | no | no | FOLLOW UP (3 days, the boundary) |
| L-107 | [Service B] | 2026-10-12 | no | no | (blank, 2 days) |
| L-108 | [Service A] | (no date) | no | no | MISSING DATE |
On the Summary tab you should see B2 = 3, C2 = L-103, L-104, L-106, and D2 = 1. The empty rows below row 8 return blank and are not counted. Delete the test rows when your real quotes go in.
If your test date is not 2026-10-14, the day counts will differ. The formula uses the real date from TODAY(), so recompute by hand: subtract the sent date from today's date.
6. Keep the counts fresh
The flag depends on TODAY(), which changes every day. Check at File > Settings > Calculation that Calculation Mode is Automatic. Google's page for these settings lists only Automatic and Manual modes and describes no scheduled recalculation. If a count looks stale, open the sheet once and look again before you rely on it.
Real Example
Scenario: It is Wednesday. You sent six quotes since last week and you want to know who needs a nudge before you start your calls.
What you do: Open the sheet and look at the Summary tab. B2 says how many quotes are due and C2 lists the codes. In the invented test above, that is 3 quotes: L-103, L-104 and L-106.
What you get: A short list of codes. Open your brand's CRM, find each lead by code and make the call or send the message there. The sheet never contacts anyone.
Tips
- Add each quote the day you send it. A quote with no date shows up as MISSING DATE, so the sheet catches the gaps for you.
- Mark Follow-up done as yes the moment you reach out. The code drops off the list on its own.
- The Level 4 quote digest reads this Summary tab and emails you the same list each morning. Build this sheet first and test it before you set the Zap up.
- Read every formula Gemini writes before you trust it. Test with the seven invented rows.
Tool interfaces change. If a button has moved, look for similar AI options in the same menu area.