How to Use Gemini to Create Procurement Reports from Google Drive Data

If you own procurement at a mid-market company, you know the Monday morning question.

Someone, the CFO, the COO, a budget owner, asks for a procurement report. Spend by category. Top suppliers. How much you've saved. Where the money's going.

And you know the answer is somewhere. It's in a spend export in one Drive folder. A supplier list in another. Last quarter's report buried three folders deep. A contract summary someone made in a Google Doc. The data exists, it's just scattered across Google Drive in a dozen files that don't talk to each other.

Pulling it together into a clean report has, until recently, meant hours of manual work. Opening files, copying numbers, building tables, writing summaries.

Gemini changes that. Because it's built into Google Workspace, Gemini can read across the files in your Google Drive, pull the data together, analyse it, and draft a procurement report, without you opening a single spreadsheet manually.

This is Post 1 of the Gemini for Procurement Teams series, practical guides for procurement and finance 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 your Drive up so Gemini can read it cleanly, the exact prompts to generate each type of procurement report, how to handle data spread across multiple files, and, for the technically inclined, a light Google Apps Script section to automate the whole thing on a schedule.

What Gemini Can Do With Your Google Drive Data

Before the prompts, a clear picture of what Gemini can and can't do across Google Drive, because it's different from working in a single spreadsheet.

What Gemini can do:

• Read and summarise content across multiple files in your Drive, Sheets, Docs, and PDFs
• Pull data from a specified spreadsheet and analyse it
• Synthesise information from several documents into a single summary
• Generate structured report content, tables, summaries, narrative analysis
• Reference specific files you point it to using the @ mention feature
• Draft reports directly in a new Google Doc

What Gemini can't do natively

• Automatically find and combine data across files unless you tell it which files to use
• Run complex statistical modelling the way a dedicated BI tool does
• Generate live charts from a prompt, you'll build those in Sheets using native tools
• Access files outside your Drive or that you don't have permission to view

The practical implication

Gemini is excellent at pulling together and interpreting procurement data that's scattered across your Drive, and drafting the report from it. It's not a replacement for a BI platform if you need live, interactive dashboards. But for the weekly or monthly procurement report most mid-market teams actually need, it does the job, and it does it inside the tools you already have.

Step 1: Organise Your Drive So Gemini Can Read It

This is the unglamorous step that determines everything. Gemini can only produce a good report if it can find and read clean data. Spend ten minutes here before anything else.

Create a dedicated folder. Make a folder in Google Drive called something clear like Procurement Reporting. Put the source files Gemini needs into it, or shortcuts to them. A focused folder is easier for you to reference and reduces the chance Gemini reads the wrong file.

Standardise your core spend file. Your main spend data should live in one Google Sheet with consistent columns. At minimum: PO number, supplier name, supplier category, spend amount, currency, date, department or cost centre, contract reference, and approval status. Consistent column names matter, Gemini reads them as field labels.

Standardise supplier names. This is the single biggest cause of bad procurement reports. "Acme Ltd," "Acme Limited," and "ACME" will be treated as three different suppliers, splitting your spend and breaking every supplier ranking. Clean these before you analyse.

Use this Gemini prompt to audit your data first:

Look at the spend data in [@mention your spend Sheet].

Tell me:

1. How many rows of spend data are there?
2. What date range does the data cover?
3. How many unique suppliers appear in the data?
4. Are there any supplier names that look like duplicates or variations of each other (e.g. "Acme Ltd" and "Acme Limited")?
5. Are there any rows with blank or zero spend amounts?
6. Are there any rows missing a category or department?
7. What is the total spend across all rows?

List anything that needs cleaning up before I run reporting on this data.

Fix what it flags

The reports are only as good as the data underneath them.

Step 2: The Spend Overview Report

Start every reporting cycle here. This is the top-line procurement picture, the one the CFO wants first.

Using the spend data in [@mention your spend Sheet], create a procurement spend overview report.

Include the following sections:

SECTION 1: TOTAL SPEND SNAPSHOT
- Total spend for the period
- Total number of purchase orders
- Average PO value
- Number of active suppliers
- Period covered

SECTION 2: SPEND BY CATEGORY
- Total spend per category
- Each category as a % of total spend
- Rank categories highest to lowest

SECTION 3: SPEND BY DEPARTMENT
- Total spend per department or cost centre
- Each as a % of total
- Flag any department significantly above its typical share

SECTION 4: MONTH-OVER-MONTH TREND
- Total spend by month if the data spans multiple months
- Note any month with unusual spikes or drops

Write a 150-word plain-English summary at the top for a CFO audience, then present the supporting tables below.

Create this as a new Google Doc called "Procurement Spend Overview".

Why "write to a new Google Doc" matters

The instruction to write to a new Google Doc is one of Gemini's most useful capabilities here. Instead of reading output in a side panel, you get a formatted report document you can immediately share, edit, or present.

Step 3: The Supplier Analysis Report

This is where procurement reporting earns its keep. Supplier concentration, spend distribution, and vendor proliferation are the insights that actually change decisions.

Using the spend data in [@mention your spend Sheet], create a supplier analysis report.

Include:

TOP SUPPLIERS
- Top 15 suppliers by total spend
- For each: supplier name, total spend, number of POs, % of total spend, category

SUPPLIER CONCENTRATION
- What % of total spend goes to the top 5 suppliers?
- Top 10? Top 20?
- Flag any single supplier representing more than 15% of total spend as CONCENTRATION RISK

SUPPLIER COUNT BY CATEGORY
- How many suppliers do we use per category?
- Flag any category with more than 5 suppliers as a CONSOLIDATION OPPORTUNITY, we may be fragmenting spend that could be negotiated as one contract

LONG-TAIL SUPPLIERS
- How many suppliers account for the bottom 20% of spend?
- This is our tail spend, flag it as a management opportunity

Write a plain-English summary of the key findings and recommendations at the top.

Create this as a new Google Doc called "Supplier Analysis Report".

Why this report earns its keep

The consolidation opportunity and tail spend sections are the ones that get a procurement leader noticed. They turn a backward-looking spend report into a forward-looking savings argument.

Step 4: The Savings and Contract Compliance Report

If you've negotiated contracts, you need to show whether the organisation is actually buying through them. This report surfaces off-contract spend, the leakage that quietly erodes negotiated savings.

This prompt works best when you can point Gemini at both your spend data and a contract summary. If you have contract terms in a separate Sheet or Doc, reference both.

Using the spend data in [@mention your spend Sheet] and the contract summary in [@mention your contract Doc or Sheet], create a contract compliance and savings report.

Analyse the following:

ON-CONTRACT VS OFF-CONTRACT SPEND
- For each supplier with a contract reference in the data, total their spend (on-contract)
- For spend with no contract reference, total it (off-contract)
- Show off-contract spend as a % of total, this is our maverick spend exposure

SUPPLIERS WITH CONTRACTS NOT BEING USED
- List any supplier in the contract summary that has little or no spend in the period, we negotiated terms we aren't using

OFF-CONTRACT SPEND BY CATEGORY
- Which categories have the highest off-contract spend?
- These are the categories where we're most likely leaking negotiated savings

POTENTIAL CONSOLIDATION
- Are there categories where off-contract spend is high AND we already have a contracted supplier we could route it to?

Write a summary highlighting the total off-contract exposure and the top 3 actions to reduce it.

Create this as a new Google Doc called "Contract Compliance Report".

Why this metric connects to real money

Off-contract spend is the metric that connects procurement reporting to real money. Industry research consistently puts maverick spend at 10–20% of savings lost, this report makes your organisation's number visible instead of invisible.

Step 5: The Executive Summary

Once you've generated the detailed reports, pull them into a single executive summary. This is what goes to the leadership team, short, sharp, and decision-focused.

Based on the three reports I've created in this folder, the Spend Overview, the Supplier Analysis, and the Contract Compliance Report, write a one-page procurement executive summary.

Structure it as:

HEADLINE METRICS (3 to 4 bullets):
- Total spend for the period
- Number of active suppliers
- Off-contract spend %
- Top spend category

KEY FINDINGS (4 to 5 bullets):
- Most significant supplier concentration risk
- Biggest consolidation opportunity
- Off-contract leakage hotspot
- Any notable spend trend

RECOMMENDED ACTIONS (numbered, max 4):
- Specific, actionable steps for the next quarter

WATCH LIST (max 3):
- Items to monitor that don't need action yet

Keep the whole summary under 250 words. Plain English. No jargon. Write for a CFO and executive team audience.

Create this as a new Google Doc called "Procurement Executive Summary".

Working Across Multiple Files. The @ Mention Trick

The single most useful Gemini skill for procurement reporting is the @ mention. When you type @ in a Gemini prompt within Google Workspace, you can reference specific files by name, telling Gemini exactly which spreadsheet, document, or PDF to pull from.

This matters because procurement data is almost never in one place. Your spend export is one file. Your supplier master is another. Your contract summaries might be a third. Rather than copying everything into one sheet, you can point Gemini at each file in a single prompt:

Compare the supplier list in [@Supplier Master Sheet] against the spend data in [@Q1 Spend Export] and the contract terms in [@Contract Summary Doc].

Identify:

1. Suppliers we're spending with that aren't in the supplier master
2. Suppliers in the master with no spend this quarter
3. Suppliers with spend but no contract on file

Create a reconciliation report flagging each gap.

Why this is powerful

This is genuinely powerful, it lets Gemini act as the connective tissue across files that were never designed to work together. Keep file names clean and descriptive so the @ mention finds the right ones.

Advanced Section. Automating Report Generation with Google Apps Script

This section is for procurement ops leads or IT administrators who want reports generated automatically on a schedule. If you're happy running the prompts manually each cycle, skip this.

Google Apps Script can automate the data-gathering and report-scaffolding parts of this workflow, pulling spend data from your Drive, calculating the core metrics, and writing them into a report document automatically. You then run the Gemini prompts on the prepared data for the narrative analysis.

Before you start: Have your spend data in a Google Sheet with consistent columns (PO number, supplier, category, amount, date, department, contract reference). Note the Sheet's ID from its URL.

The Apps Script: In Google Sheets, go to Extensions → Apps Script. Paste this:

function generateProcurementReport() {
  const SPEND_SHEET_NAME = 'Spend Data';
  const REPORT_FOLDER_ID = 'YOUR_DRIVE_FOLDER_ID';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SPEND_SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  // Map columns, adjust to match your sheet
  const COL = {
    supplier: headers.indexOf('Supplier'),
    category: headers.indexOf('Category'),
    amount: headers.indexOf('Amount'),
    department: headers.indexOf('Department'),
    contract: headers.indexOf('Contract Reference')
  };

  // Calculate core metrics
  let totalSpend = 0;
  const supplierSpend = {};
  const categorySpend = {};
  const deptSpend = {};
  let offContractSpend = 0;

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const amount = parseFloat(row[COL.amount]) || 0;
    const supplier = row[COL.supplier] || 'Unknown';
    const category = row[COL.category] || 'Uncategorised';
    const dept = row[COL.department] || 'Unassigned';
    const contract = row[COL.contract];

    totalSpend += amount;
    supplierSpend[supplier] = (supplierSpend[supplier] || 0) + amount;
    categorySpend[category] = (categorySpend[category] || 0) + amount;
    deptSpend[dept] = (deptSpend[dept] || 0) + amount;

    // Track off-contract spend
    if (!contract || contract === '') {
      offContractSpend += amount;
    }
  }

  // Sort suppliers by spend
  const topSuppliers = Object.entries(supplierSpend)
    .sort((a, b) => b[1] - a[1])
    .slice(0, 15);

  // Calculate top 5 concentration
  const top5Total = topSuppliers
    .slice(0, 5)
    .reduce((sum, s) => sum + s[1], 0);
  const top5Pct = ((top5Total / totalSpend) * 100).toFixed(1);
  const offContractPct = ((offContractSpend / totalSpend) * 100).toFixed(1);

  // Create the report document
  const today = new Date();
  const doc = DocumentApp.create(
    `Procurement Report, ${today.toDateString()}`
  );
  const body = doc.getBody();
  body.appendParagraph('Procurement Report')
    .setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph(`Generated: ${today.toDateString()}`);
  body.appendParagraph('');

  // Headline metrics
  body.appendParagraph('Headline Metrics')
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendParagraph(`Total Spend: ${totalSpend.toLocaleString()}`);
  body.appendParagraph(`Active Suppliers: ${Object.keys(supplierSpend).length}`);
  body.appendParagraph(`Top 5 Supplier Concentration: ${top5Pct}%`);
  body.appendParagraph(`Off-Contract Spend: ${offContractPct}%`);
  body.appendParagraph('');

  // Top suppliers table
  body.appendParagraph('Top 15 Suppliers by Spend')
    .setHeading(DocumentApp.ParagraphHeading.HEADING2);
  const tableData = [['Supplier', 'Spend', '% of Total']];
  topSuppliers.forEach(([name, spend]) => {
    const pct = ((spend / totalSpend) * 100).toFixed(1);
    tableData.push([name, spend.toLocaleString(), `${pct}%`]);
  });
  body.appendTable(tableData);

  // Move report to the reporting folder
  const file = DriveApp.getFileById(doc.getId());
  const folder = DriveApp.getFolderById(REPORT_FOLDER_ID);
  folder.addFile(file);
  DriveApp.getRootFolder().removeFile(file);

  Logger.log('Report created: ' + doc.getUrl());
  return doc.getUrl();
}

// Run monthly on the 1st
function createMonthlyTrigger() {
  ScriptApp.newTrigger('generateProcurementReport')
    .timeBased()
    .onMonthDay(1)
    .atHour(7)
    .create();
}

To activate

Replace YOUR_DRIVE_FOLDER_ID with your reporting folder's ID (from its Drive URL), adjust the column names to match your sheet, and run createMonthlyTrigger once. On the first of each month at 7am, the script calculates your core procurement metrics, builds a report document with the headline numbers and top supplier table, and drops it in your reporting folder.

What this gives you

The structured, numerical backbone of your report is generated automatically. You then open it, run the Gemini narrative prompts from Steps 2–5 to add the plain-English analysis and recommendations, and you have a finished report in minutes instead of hours.

A note on full automation

Connecting the Gemini API to write the narrative analysis directly into the document, full hands-off report generation, is possible with a Google Cloud project and the Gemini API. That's beyond this post, but the Apps Script above is the foundation. The documentation at ai.google.dev covers the API capabilities you'd build on.

The Complete Google Workspace Procurement Reporting Workflow

Here's the full picture:

• Spend data lands in your Google Sheet
• Apps Script runs monthly, calculates core metrics, scaffolds the report document
• You run the Gemini data audit prompt to confirm the data is clean
• Gemini generates the Spend Overview
• Gemini generates the Supplier Analysis with concentration and consolidation flags
• Gemini generates the Contract Compliance Report showing off-contract leakage
• Gemini pulls it all into a one-page Executive Summary
• Finished, shareable procurement reports in Google Docs, built from scattered Drive data, in minutes

Where this workflow stops and Blackbee AI picks up

The Gemini workflow in this post turns scattered Drive data into clean procurement reports, a real upgrade for any team doing this manually today. What it can't do is operate in real time. It reports on spend after it's happened, from data you assemble and prompts you run. It can't flag an off-contract purchase the moment it's proposed, validate a commitment against contracts before it's made, or give procurement leadership a live view of spend as it moves. That's where an agentic Intake-to-Pay platform takes over. Blackbee AI governs procurement spend continuously, capturing every request at the point of intent, validating it against contracts and policy before commitment, and giving the CFO and procurement leader a live picture of spend rather than a monthly reconstruction of it. If your team is processing 200+ procurement cycles a month and a monthly Gemini report is starting to lag behind the pace of your spend, see how Blackbee AI works.