Automated Sales Analysis with Gemini AI in Google Sheets

Welcome back to MageSheet! We are officially kicking off our third B2B pillar: Data Analytics & Artificial Intelligence.

In our previous post, we built a Real-Time BI Dashboard inside Google Sheets. Dashboards with colorful charts and pivot tables are excellent for spotting trends. But charts still require a human being to log in, look at the data, interpret what it means, and write an email to the marketing team saying, "Hey, Widget A sales dropped 15% this week, we need a promo."

What if your data could speak for itself?

Today, we are going to connect Google's Gemini AI directly to our Google Sheets via Apps Script. We will teach the AI how to read our Weekly Sales Data, write a professional executive summary, and highlight actionable insights—completely on autopilot.

The Goal

We want an Apps Script function that triggers every Monday morning. It will:

1. Read the previous 7 days of sales data from our Magento integration.
2. Package that raw data into a text prompt.
3. Call the Gemini Pro API.
4. Print the AI-generated natural language report directly into our "Executive Summary" tab.

Step 1: Get the Gemini API Key

To use Gemini programmatically, you need an API key from Google AI Studio.

1. Go to aistudio.google.com.
2. Click Get API Key and generate one.
3. Securely store this key. (Never paste it openly in public code; use Apps Script PropertiesService for production!).

Step 2: Writing the Apps Script

Open your Google Sheet and navigate to Extensions > Apps Script.

First, let's write a helper function to call the Gemini API. We will use the gemini-pro model because of its massive context window—it can analyze thousands of rows of CSV data in seconds.

// Code.gs

const GEMINI_API_KEY = "YOUR_AI_STUDIO_KEY";

function callGeminiAPI(promptText) {
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${GEMINI_API_KEY}`;
  
  const payload = {
    "contents": [{
      "parts": [{
        "text": promptText
      }]
    }]
  };
  
  const options = {
    "method": "POST",
    "headers": { "Content-Type": "application/json" },
    "payload": JSON.stringify(payload)
  };
  
  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = JSON.parse(response.getContentText());
    
    // Extract the text from the Gemini response object
    if (json.candidates && json.candidates.length > 0) {
      return json.candidates[0].content.parts[0].text;
    }
  } catch (e) {
    Logger.log("Error calling Gemini: " + e.message);
    return "AI Analysis failed.";
  }
}

Step 3: Feeding the Sales Data to the AI

Large Language Models (LLMs) are text-prediction engines. We can't just send them a spreadsheet object; we need to convert our data into a structured string (like CSV or JSON) along with a very specific set of instructions.

function generateWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Sales Data");
  
  // 1. Get the raw data
  // Let's assume Row 1 is Headers, and we get the last 7 days of sales
  const data = dataSheet.getRange("A1:D50").getDisplayValues(); 
  
  // Convert 2D Array into a readable CSV string
  let csvString = "";
  data.forEach(row => {
    csvString += row.join(", ") + "\n";
  });
  
  // 2. Craft the Prompt
  const prompt = `
    You are an expert E-Commerce Data Analyst. 
    Review the following weekly sales data from our Magento B2B store.
    
    Data Format: [Date, Product Category, Units Sold, Revenue]
    
    Data:
    ${csvString}
    
    Please provide:
    1. A 2-sentence executive summary of the week's performance.
    2. The top-performing category and why you think it succeeded.
    3. One actionable recommendation for the marketing team to improve slow-moving inventory next week.
    
    Format your response cleanly in Markdown.
  `;
  
  // 3. Get the AI's response!
  const aiReport = callGeminiAPI(prompt);
  
  // 4. Print it back into the Google Sheet
  const reportSheet = ss.getSheetByName("Executive Summary");
  // Put the timestamp in A1, and the AI Report in B1
  reportSheet.insertRowBefore(2); 
  reportSheet.getRange("A2").setValue(new Date());
  reportSheet.getRange("B2").setValue(aiReport);
}

The Result: Automated Intelligence

When you run generateWeeklyReport(), Gemini will instantly process the numbers and spit out something like this into your sheet:

Executive Summary: Revenue for the week totaled $45,200, driven largely by end-of-month B2B wholesale orders. Sales volume remained stable, though 'Office Electronics' saw a sharp 15% dip compared to earlier in the month.

Top Performer: Network Cables. With 850 units sold, this category drove 40% of total revenue. This aligns with standard IT infrastructure upgrades typical for Q3.

Recommendation: We recommend the marketing team launch an email campaign offering a 10% volume discount on 'Office Electronics' specifically targeted at buyers who recently purchased Network Cables.

Endless Possibilities

This basic architecture opens the door to incredible automation.

• You can pipe Gemini's text output directly into GmailApp to email the CEO every Monday.
• You can use Gemini to analyze Customer Support tickets from Magento to detect negative sentiment.
• You can feed competitor pricing data into Gemini and have it suggest your new MSRPs.

At MageSheet, we build enterprise-grade AI integrations using Google Workspace, Gemini, and Adobe Commerce to turn raw data into automated business actions. Ready to stop formatting spreadsheets and start listening to them? Reach out to us today.