Skip to content
P
AI for Paraplanner

Use Gemini in Google Sheets to build a custodian paperwork tracker tab

For Paraplanners ·

Tool:Google Sheets
AI Feature:Gemini in Sheets
Time:20 to 30 minutes
Difficulty:Beginner
Google Sheets

What This Does

Transfers, beneficiary changes and distribution requests sit in different states at once: waiting on a signature, submitted, bounced as not in good order (NIGO). A Paperwork tab with status dropdowns and highlighting shows what needs a nudge today. Gemini in Sheets builds the dropdowns, the highlighting and a small status count from plain-language prompts.

Before You Start

  • You are signed in to Google Sheets with the firm's Google Workspace account, and an Ask Gemini button appears at the top right. Gemini in Sheets needs an eligible plan such as Business Standard. If the button is missing, ask your admin.
  • Rows use a household code and a short item label such as "Transfer, Client A Rollover IRA". Leave out account numbers, names, dates of birth and addresses. Confirm with your CCO that Gemini is approved for a sheet like this.
  • A blank tab named Paperwork.

Steps

1. Type the headers

In row 1, columns A to F: Household, Item, Form Type, Sent On, Follow-up Due, Status.

2. Open Gemini and ask for the dropdowns

Click Ask Gemini at the top right. Google's help page for Gemini in Sheets says edits come back as an action preview card. Click Apply to accept it, and use Undo if the result is wrong.

Prompt

In the Paperwork tab, create a dropdown in column C, rows 2 to 300, with the options Account opening, Transfer, Beneficiary change, Distribution, Other. Create a dropdown in column F, rows 2 to 300, with the options Not started, Sent for signature, Signed, Submitted, NIGO, Complete, Cancelled.

3. Ask for the highlights

Prompt

Add conditional formatting to rows 2 to 300. Highlight the whole row red when column F is NIGO. Add a second rule that highlights the row yellow when column E has a date earlier than today and column F is Sent for signature or Submitted. Leave rows with a blank column E unhighlighted.

Google's page lists conditional formatting among the actions Gemini can apply. A Settings button appears afterward for adjusting the rules. Open it and read the two rules. A custom formula along the lines of =AND($E2<>"",$E2<TODAY(),OR($F2="Sent for signature",$F2="Submitted")) is what you are looking for.

4. Ask for a count by status

Prompt

Next to the table, in columns H and I, list each Status option in column H and the number of rows with that status in column I, using COUNTIF.

Google's page also mentions creating pivot tables by prompt. A COUNTIF block is simpler to audit and updates as you type.

5. Test on answers you already know

Enter five test rows (labelled as tests):

  1. Status Submitted, Follow-up Due two days ago. Expect a yellow row.
  2. Status NIGO. Expect a red row.
  3. Status Sent for signature, Follow-up Due five days from now. Expect no highlight.
  4. Status Complete, Follow-up Due in the past. Expect no highlight.
  5. Status Submitted, Follow-up Due blank. Expect no highlight.

The counts should read Submitted 2, NIGO 1, Sent for signature 1, Complete 1. That adds to five, matching the five rows. If any highlight or count is off, fix the rule with a follow-up prompt and test again. Then delete the test rows.

Real Example

Scenario: Household 112 has a transfer for the Client A Rollover IRA that went to the custodian nine days ago and came back NIGO (invented).

What you do: Add the row with Form Type "Transfer" and set Status to NIGO. The row turns red. You open the rejection notice, fix the paperwork, resubmit and change Status to Submitted with a new Follow-up Due date. The red clears, and the row turns yellow only if that date passes without the status moving.

Tips

  • Keep the header names and status labels exactly as written. A digest that reads this tab matches on them.
  • Put the Follow-up Due date in when you send the form. The yellow rule cannot flag what has no date.
  • Rejected paperwork reasons stay in the custodian's notice. This tab only tracks the state.

Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area. Source for the Gemini steps: support.google.com/docs/answer/14356410, read 2026-10-06.