How to Use Gemini in Google Sheets for AP Spend Analysis

If your finance team lives in Google Workspace, this one's for you

You've probably heard about using ChatGPT AP spend analysis prompts. And it works well. But it requires you to export your data, open a separate tool, upload a file, run your prompts, and then bring the output back into whatever you're actually working in. For teams that spend their day in Google Sheets, that context-switching is friction that adds up.

What's different about Gemini in Google Sheets

Gemini, Google's AI, is built directly into Google Sheets. Your data stays where it already lives. Your analysis happens in the same tab as your invoice export. Your outputs land directly in your spreadsheet, ready to use. This is Post 1 of the Gemini for AP Teams series, practical guides for finance and AP professionals who want to use Gemini as a working tool inside the Google Workspace they already use every day. We'll cover how to set Gemini up in Google Sheets, the exact prompts for AP spend analysis, how to build a repeatable monthly process, and, if you're already using ChatGPT for this, an honest comparison of where each tool performs better.

What Gemini in Google Sheets actually does

Before getting into the prompts, it's worth being precise about what Gemini can do inside Google Sheets, because it's different from what you might expect if you're used to ChatGPT's Advanced Data Analysis.

What Gemini in Google Sheets can do

• Read and analyse data already in your spreadsheet without you needing to describe it
• Write and run Google Apps Script functions on your behalf
• Generate formulas from plain-language descriptions
• Summarise and interpret data ranges in natural language
• Create new columns, apply categorisations, and flag anomalies directly in your sheet
• Draft text content, summaries, emails, reports based on your data

What it cannot do natively

• Run Python code the way ChatGPT's Advanced Data Analysis does
• Generate charts and visualisations directly from a prompt (you'll need to create these separately using Google Sheets' native chart tools)
• Access data outside the current spreadsheet unless you explicitly share it

The practical implication

Gemini in Google Sheets is stronger than ChatGPT for tasks that involve writing directly into your spreadsheet, adding columns, categorising rows, applying formulas across a dataset. ChatGPT is stronger for visual outputs and Python-based statistical analysis. For AP spend analysis, both get you to useful answers; Gemini just keeps you inside the tool you're already using.

What you need to get started

Google Workspace plan: Gemini in Google Sheets is available on Google Workspace Business Standard, Business Plus, Enterprise, and Education plans. It's also available as a Gemini add-on for personal Google accounts. If you're on a free Google account or a basic Workspace plan, check whether your plan includes Gemini, not all do.

How to access it

Open Google Sheets. Look for the Gemini icon in the toolbar, it appears as a star or sparkle icon in the top right. Click it to open the Gemini side panel. This is where you'll enter your prompts. Alternatively, use the Ask Gemini option that appears when you select a data range.

Your data

Export your AP invoice data as a CSV from your ERP or AP system and open it in Google Sheets. The fields you need for spend analysis are the same regardless of which AI tool you're using: invoice date, invoice number, vendor name, invoice amount, currency, due date, payment date, payment status, and GL code or cost centre. If you're already running AP analysis in ChatGPT, you likely have this export ready. The same file works here, just open it in Google Sheets instead of uploading it to ChatGPT.

Step 1: Clean your data before analysing it

This step is the same whether you're using ChatGPT, Gemini, or any other AI tool. Garbage in, garbage out. Spend five minutes on this before running any analysis prompt. Use this Gemini prompt to check your data quality first:

Look at the data in this spreadsheet.

Tell me:

1. How many rows are there, excluding the header?
2. Are there any rows where the vendor name is blank or appears to say "unknown" or "miscellaneous"?
3. Are there any rows where the invoice amount is zero, negative, or blank?
4. Are there any duplicate invoice numbers in the dataset?
5. What date range does this data cover, earliest invoice date to latest?
6. How many unique vendors are in the dataset?

Return your findings as a plain list. Flag anything that needs attention before analysis begins.

Fix what Prompt 1 flags

Run this first. Fix anything it flags. Standardise vendor names, "Acme Ltd" and "Acme Limited" need to be the same string or every vendor analysis will split them incorrectly. Remove journal entries and internal recharges if they're in the export. Once the data is clean, you're ready to analyse.

Step 2: Baseline spend overview

Start every monthly AP analysis session with this prompt. It gives you the top-line picture before you drill into anything specific.

Analyse the AP invoice data in this spreadsheet and give me a baseline spend overview.

Include:

1. Total invoice value for the period
2. Total number of invoices
3. Average invoice value
4. Breakdown by payment status: how many and what value are paid, pending, overdue, on hold
5. Top 10 vendors by total invoice value, show vendor name, invoice count, total value, and percentage of total spend
6. Spend breakdown by GL code or cost centre if that column exists
7. Month-by-month invoice volume and total value if the data spans multiple months

Write the summary in plain English as if presenting to a CFO. Keep it under 200 words. Then show the supporting data as a table below the summary.

Why the CFO framing matters

Gemini will adjust the language and emphasis based on who you tell it the audience is. "Present to a CFO" produces a tighter, more strategic summary than a generic "summarise this data" instruction. Use audience framing in every prompt where the output is going to someone other than you.

Step 3: Vendor price drift detection

This is the prompt that surprises finance teams the most. Vendor pricing drift, where a supplier gradually bills more per invoice over time without a formal price increase, is one of the most common and least-detected forms of cost overrun in AP.

For each vendor with 4 or more invoices in this dataset, calculate their average invoice value per quarter.

Show:
- Vendor name
- Q1 average invoice value
- Q2 average invoice value
- Q3 average invoice value
- Q4 average invoice value (use whichever quarters exist in the data)
- Change from first to last quarter ($ and %)
- Flag: YES if the change is more than 10% increase, NO if not

Sort the results by % change, highest first.

Add a column called DRIFT_RISK with values HIGH, MEDIUM, or LOW:
- HIGH: more than 20% increase
- MEDIUM: 10 to 20% increase
- LOW: less than 10% change

Write the results directly into a new sheet called "Vendor Drift Analysis".

Why "write to a new sheet" matters

The instruction to write to a new sheet is one of Gemini's most useful capabilities in this context. Rather than reading output in the side panel, you get a structured table directly in your spreadsheet, ready to filter, sort, and share with a budget owner or CFO.

Step 4: Duplicate payment detection

Run this every month before your payment run. Duplicate payment detection saves cash, duplicates are recoverable but the recovery process is time-consuming and often incomplete. Catching them before payment is always preferable.

Check this invoice dataset for potential duplicate payments.

Look for:

TYPE 1: EXACT DUPLICATES
Same vendor name AND same invoice number appearing more than once.

TYPE 2: AMOUNT DUPLICATES
Same vendor name AND same invoice amount within a 45-day window. Flag even if invoice numbers differ.

TYPE 3: NEAR-DUPLICATES
Same vendor name AND invoice amount within 3% of each other AND submitted within 30 days of each other. Flag with REVIEW status, these may be legitimate but need verification.

For each potential duplicate, show:
- Duplicate type (1, 2, or 3)
- Vendor name
- Invoice number(s)
- Invoice date(s)
- Amount(s)
- Payment status of each
- Risk level: HIGH, MEDIUM, or LOW
- Recommended action

Write results to a new sheet called "Duplicate Flags". If no duplicates are found in a category, note that explicitly in the sheet.

Step 5: Cash flow projection

This prompt turns your AP data into a forward-looking cash flow view, showing what's due to go out over the next 30, 60, and 90 days based on your outstanding invoices and historical payment patterns.

Using the unpaid invoices in this dataset, build a cash flow projection.

Step 1: OUTSTANDING PAYMENTS
List all invoices where payment status is pending, overdue, or on hold.

Group them by:
- Due within 30 days
- Due in 31 to 60 days
- Due in 61 to 90 days
- Overdue (past due date)

For each bucket show:
- Invoice count
- Total value
- Vendor names (top 5 by value in each bucket)

Step 2: PAYMENT PATTERN BASELINE
From the paid invoices in the dataset, calculate the average number of days between invoice date and payment date. Show this overall and by top 10 vendors.

Step 3: PROJECTED OUTFLOW
Based on the outstanding invoices and the average payment pattern, estimate total cash outflow for the next 30, 60, and 90 days.

Show the projection as three summary figures at the top of a new sheet called "Cash Flow Projection", followed by the supporting invoice detail below.

Flag any overdue invoices from vendors where late payment may attract a finance charge, mark these as PRIORITY.

Step 6: Vendor concentration risk

Vendor concentration risk, where a disproportionate share of your spend is with a small number of vendors, is a supply chain and financial risk that most AP teams don't track formally. This prompt surfaces it in under a minute.

Analyse vendor concentration in this AP dataset.

Calculate:

1. What percentage of total spend goes to the top 5 vendors?
2. What percentage goes to the top 10 vendors?
3. What percentage goes to the top 20 vendors?
4. Are there any single vendors that represent more than 15% of total spend? Flag these as HIGH CONCENTRATION.
5. Are there any spend categories (by GL code) where a single vendor represents more than 60% of category spend? Flag these as CATEGORY RISK.

Write a one-paragraph plain-English interpretation of the concentration risk profile for a CFO audience.

Then show the full vendor concentration table below, sorted by spend percentage descending.

Step 7: The monthly CFO summary

Once you've run the analysis prompts above, use this to pull everything into a single executive summary, ready to screenshot, copy into a slide, or paste into an email.

Based on all the analysis sheets in this workbook, including Vendor Drift Analysis, Duplicate Flags, and Cash Flow Projection, write a monthly AP summary for the CFO.

Structure it as follows:

HEADLINE METRICS (3 bullet points):
- Total AP value for the period
- Total outstanding and overdue
- 30-day projected cash outflow

KEY FINDINGS (4 to 6 bullet points):
- Most significant vendor drift findings
- Any duplicate payment flags
- Top concentration risks
- Any overdue items above a material threshold

IMMEDIATE ACTIONS REQUIRED (numbered list, maximum 4 items):
- Actions that need to happen this week

WATCH LIST (maximum 3 items):
- Issues to monitor next month that don't require action yet

Keep the entire summary under 250 words. Plain English. No jargon. No bullet points longer than one sentence. Write it as if presenting to a board audit committee.

Gemini vs ChatGPT for AP spend analysis: the honest comparison

If you're already using ChatGPT's Advanced Data Analysis for AP spend, here's the direct comparison so you can decide which tool fits your workflow better.

Where Gemini in Google Sheets wins

Your data stays in Google Sheets. No exporting, no uploading, no switching tools. If your AP export already lives in Google Drive, Gemini accesses it directly. The "write to a new sheet" capability is genuinely more useful than ChatGPT's output in a conversation window, the results are immediately usable in your existing spreadsheet without any copy-pasting. For teams where the CFO, Controller, and AP Manager are all working in the same Google Sheet, Gemini's outputs are instantly shared and visible without any additional steps.

Where ChatGPT wins

ChatGPT's Advanced Data Analysis runs Python under the hood, which means it handles complex statistical calculations, standard deviations, regression analysis, time series modelling, more reliably than Gemini's formula-based approach. It also generates charts and visualisations directly from prompts, which Gemini cannot do natively in the same way. If you need a visual dashboard output, charts, histograms, trend lines, ChatGPT Advanced Data Analysis is still the stronger tool. If you need the results to live in a spreadsheet that your whole team can access and work from, Gemini is the more practical choice.

Building a repeatable monthly process

Once you've run these prompts once, the process takes under 30 minutes every month. Here's the exact sequence:

Week 1 of each month, after AP close

• Export the prior month's invoices from your ERP, same fields, same format every time
• Open in Google Sheets and run Prompt 1 (data quality check), fix anything flagged
• Run Prompt 2 (baseline overview), review and save the summary
• Run Prompt 3 (vendor drift), review the Vendor Drift Analysis sheet, flag HIGH and MEDIUM vendors for follow-up
• Run Prompt 4 (duplicate detection), review the Duplicate Flags sheet before the next payment run
• Run Prompt 5 (cash flow projection), share the Cash Flow Projection sheet with the CFO
• Run Prompt 6 (concentration risk), update your vendor concentration tracker
• Run Prompt 7 (CFO summary), copy into your monthly finance report

Month two onwards

Append the new month's data to your master sheet rather than starting fresh. This gives the vendor drift and cash flow prompts a longer data window to work with, and makes the trend analysis more meaningful over time. The vendor drift prompt is particularly powerful once you have 3+ months of data, drift that looks like noise in a single month becomes a clear pattern over a quarter. The same is true for AP exceptions, once you have multiple months of data, recurring exception patterns are easier to surface.

Where this guide stops, and where Blackbee AI picks up

The prompts in this post give you a powerful monthly AP analysis process. What they can't give you is continuous monitoring, the kind that flags a vendor pricing anomaly the moment an invoice arrives, validates against your contracts automatically, and gives your CFO a live spend view without anyone running a monthly process. That's what Blackbee AI's Spend Intelligence Agent does, continuously, above your ERP. For more on what continuous spend intelligence looks like in practice, that guide goes deeper. If your team is processing 200 or more invoices a month and a monthly Gemini analysis is starting to feel like it's not keeping pace with the volume, see how Blackbee AI works.