Use Gemini in Google Sheets to build an Event Prep spreadsheet for earnings and company events

What This Does

Earnings releases, investor days and annual meetings arrive on dates the companies choose, and the prep you want before each one is easy to forget. This guide builds an "Event Prep" spreadsheet with one row per event, a dropdown for the event type, a dropdown for whether your prep notes are done, and a flag that reads PREP NEEDED when an undone event falls inside your alert window. A Summary tab counts and lists the flags. Gemini helps with dropdowns, formulas and colours, and you paste every formula in full so you can see what the sheet does.

The Level 4 weekly earnings and event prep digest reads the Summary tab of this exact spreadsheet. Keep the tab names, columns and cells as written.

The guide states no reporting calendar. Every event date comes from the company's own announcement, and you type it in yourself.

Before You Start

• A Google account with Gemini in Google Sheets. Google's help page says the feature needs an eligible Google Workspace or Google AI plan, for example Business Standard ($14/user/month) for business use or Google AI Pro ($19.99/month) for personal use. Look for the Ask Gemini button at the top right of an open spreadsheet. If it is not there, ask your Workspace admin.
• Holding codes in your own scheme (H-01 and so on) and the event dates you have seen announced publicly.
• An answer from your CCO on which AI tools the firm approved and how chats are kept. Look at the tool's data settings as well, in particular whether chats are used for training and how long they are retained.

Data caution. The sheet takes codes, event types, dates and yes or no. That is all. Names, tickers, position sizes, notes from calls with management and anything that could be material non-public information stay out of the sheet and out of the Ask Gemini panel, and they stay out of every prompt. Holdings you have not disclosed, trade ideas, models, investor names and fee terms belong in the firm's systems unless the firm approved this tool for that data and your CCO allows it. Anything you copy from a licensed terminal or research service needs a licence check first. The practice rows below are invented.

Steps

1. Create the spreadsheet and the tabs

Create a Google Sheets file named "Event Prep". Rename the first tab "Events" and add a second tab named "Summary".

On Events, type these headers in row 1:

• A1: Holding code
• B1: Event type
• C1: Event date
• D1: Prep notes done
• E1: Flag

Format C2:C301 as dates.

2. Add the dropdowns with Gemini

Open Ask Gemini at the top right. Enter:

"Create a dropdown in B2:B301 with the options EARNINGS, INVESTORDAY and AGM."

Name the range in every prompt. Google's page notes that when you do not give a location, the dropdown goes into the rightmost empty column. Review the Action preview card and click Apply. Then enter:

"Create a dropdown in D2:D301 with the options yes and no."

Apply that one as well. Use the exact capitalisation above, because the formulas compare against it.

3. Set up the Summary tab

On Summary, type these headers in row 1:

• A1: Key
• B1: Prep Needed Count
• C1: Prep Needed Items
• D1: Missing Date Count

In A2 type the word summary, all lowercase. The Level 4 digest looks for exactly that word.

In A4 type "Alert days" and in B4 type 14. This is your own choice of how far ahead you want a reminder. Change it whenever it suits you.

4. Add the Flag formula

Ask Gemini for a first version: "In E2, return nothing if A2 is empty, MISSING DATE if C2 is empty, nothing if C2 is before today or D2 says yes, PREP NEEDED if C2 minus today is no more than the alert days in Summary!B4, and nothing otherwise." Compare its answer with the formula below. Then paste this one into E2:

=IF(A2="","",IF(C2="","MISSING DATE",IF(C2<TODAY(),"",IF(D2="yes","",IF(C2-TODAY()<=Summary!$B$4,"PREP NEEDED","")))))

If the cell shows an error, hover over it and click Fix.

Copy E2, select E3:E301 and paste. The formula now runs to row 301. A blank row returns blank and is not counted.

5. Add the Summary formulas

B2:

=COUNTIF(Events!E2:E301,"PREP NEEDED")

C2:

=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Events!A2:A301&" "&Events!B2:B301&" ("&TEXT(Events!C2:C301,"yyyy-mm-dd")&")",Events!E2:E301="PREP NEEDED")),"none")

D2:

=COUNTIF(Events!E2:E301,"MISSING DATE")

6. Colour the flags

Ask Gemini: "Apply conditional formatting to A2:E301 so a row turns amber when column E says PREP NEEDED and grey when it says MISSING DATE." Review the Action preview card and click Apply. Click Undo if the rule is wrong.

7. Test with the practice rows

Type these invented rows into Events with Summary!B4 set to 14. The expected results assume an invented Monday, 2026-10-26, because the Flag column compares with TODAY(). On any other day, add the number of days between 2026-10-26 and today to every date you type. The flags stay the same.

Expected Summary values:

• B2: 2
• C2: H-01 EARNINGS (2026-10-29), H-03 EARNINGS (2026-11-09)
• D2: 1

H-06 is a reminder row. Leave the date empty until the company announces it. After that, type the date from the announcement.

8. Keep Calculation Mode on Automatic

Click File > Settings > Calculation. Under Calculation Mode, choose Automatic and click Save settings. Google's calculation settings page lists only Automatic and Manual and says TODAY changes every new day. It describes no scheduled recalculation, so if the counts look stale, open the sheet before relying on them.

9. Replace the practice rows

Delete the invented values in columns A to D (leave the formulas in column E). Enter your own codes, event types and dates. When your prep notes for an event are finished, set Prep notes done to yes.

Real Example

Scenario: It is Monday morning and three of your invented holdings report within the next few weeks. You want to know which ones still need prep notes before the week starts.

What you type: The practice rows above. Then, in Ask Gemini: "How many events on the Events tab have Prep notes done set to no and an event date after today? Show the analysis steps."

What you get: A count and the steps behind it. With the practice rows and today set to 2026-10-26, the expected answer is three (H-01, H-03 and H-04). H-05 is already past and H-06 has no date, so neither counts. The Summary tab says two, because H-04 sits one day outside the 14-day alert window. That gap is the point of the check: Gemini answers the question you asked, and the Summary answers the question your alert days define. If Gemini gives a different number, the sheet wins. Read the flags on the Events tab.

Tips

• Go one step past the reminder. Ask Gemini to list the dates on the Events tab that fall within the same week, so you can see where prep will pile up. Check its list against the Event date column.
• Keep AGM and INVESTORDAY rows alongside EARNINGS. They are easy to miss, and the dropdown keeps the spelling consistent for the Summary text.
• Gemini's history disappears on a reload, so insert anything you want to keep into the sheet.
• Prep notes themselves belong in your own research file, not in this spreadsheet.

Tool interfaces change. If Ask Gemini has moved, look for a Gemini or sparkle icon near the top right of Google Sheets.