AI Data Analyst for Google Sheets

by

🚀 Apps Script + Gemini Mastery — Issue #3

AI Data Analyst for Google Sheets

Ask AI questions about your data. Get summaries, trends, anomalies, and insights — automatically analyzed inside Sheets.

⭐ What You Will Build in Issue #3

A full AI Data Analyst sidebar for Google Sheets that can:

🔍 Analyze selected ranges
📊 Summarize trends
📉 Detect anomalies or outliers
🔥 Generate recommendations
🧠 Explain data “in simple English”
📈 Suggest charts and pivot tables

This transforms Sheets from a manual analysis tool into an AI-assisted insights engine.

🧠 Learning Objectives

By completing this issue, you will learn:

✔ How to read selected ranges from Sheets
✔ How to transform Sheets data into AI-friendly JSON
✔ How to send structured data to Gemini
✔ How to build a Sheets sidebar UI
✔ How to insert AI analysis back into the spreadsheet
✔ How to build modular AI functions (reusable across projects)

🧩 EXERCISE — Build the AI Data Analyst for Sheets

We’ll create:

  1. A sidebar UI
  2. A function to read the selected range
  3. A prompt builder for Gemini
  4. A reusable analysis engine
  5. A result writer that inserts analysis directly into Sheets

1️⃣ Create the Sheets Menu

Create a new Apps Script project bound to a Sheet and add this:

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("Open AI Data Analyst", "showDataAnalyst")
    .addToUi();
}

function showDataAnalyst() {
  const html = HtmlService.createHtmlOutputFromFile("DataSidebar")
    .setTitle("AI Data Analyst");
  SpreadsheetApp.getUi().showSidebar(html);
}

2️⃣ Create the Sidebar UI

DataSidebar.html

<div style="font-family: Arial; padding: 14px;">
  <h2>AI Data Analyst</h2>

  <p>Select a range in your Sheet, then enter your question:</p>

  <label><b>Your Question</b></label><br>
  <input id="question" style="width: 100%; margin-bottom: 10px;" 
         placeholder="e.g., What trends do you see?" />

  <button onclick="analyze()" style="margin-top: 8px;">Analyze Selection</button>

  <pre id="output" style="white-space: pre-wrap; margin-top: 20px;"></pre>

  <script>
    function analyze() {
      const question = document.getElementById("question").value;

      google.script.run.withSuccessHandler(function(response) {
        document.getElementById("output").textContent = response.message;
      }).analyzeSelection(question);
    }
  </script>
</div>

3️⃣ Create the AI Data Analysis Function

Analyzer.gs

function analyzeSelection(question) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();

  if (!range) {
    return { message: "Please select a range first." };
  }

  const values = range.getValues();

  // Convert sheet data to JSON for Gemini
  const dataObject = {
    headers: values[0],
    rows: values.slice(1)
  };

  const prompt = `
You are an expert data analyst.

Analyze the dataset below and answer the user's question.

User Question: ${question}

Dataset:
${JSON.stringify(dataObject, null, 2)}

Your analysis should include:
- Summary of patterns or trends
- Outliers or anomalies
- Explanations in simple English
- Recommendations
- Suggested charts (describe them)
- Business or operational insights
`;

  const response = callGemini(prompt, "");

  // Write the results to a new sheet
  const outputSheet = getOrCreateSheet("AI Analysis");
  const lines = response.split("\n");
  lines.forEach((line, i) => outputSheet.getRange(i + 1, 1).setValue(line));

  return { message: "Analysis complete. See the 'AI Analysis' sheet." };
}

4️⃣ Helper to get or create a sheet

SheetHelpers.gs

function getOrCreateSheet(name) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(name);

  if (!sheet) {
    sheet = ss.insertSheet(name);
  }

  sheet.clear();
  return sheet;
}

5️⃣ Reuse Your Existing Gemini Helper

This issue uses the same callGemini function from Issue #1 and Issue #2.

No changes needed — just ensure you included:

GeminiHelpers.gs (shared)

const GEMINI_API_KEY = "YOUR_API_KEY_HERE";
const GEMINI_MODEL = "gemini-2.5-flash";

function callGemini(prompt, text) {
  const url = `https://generativelanguage.googleapis.com/v1/models/${GEMINI_MODEL}:generateContent?key=${GEMINI_API_KEY}`;

  const payload = {
    contents: [{
      parts: [{ text: `${prompt}${text ? "\n\n---\n\n" + text : ""}` }]
    }]
  };

  const res = UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const json = JSON.parse(res.getContentText());
  return json.candidates?.[0]?.content?.parts?.[0]?.text || "No response";
}

🔬 Testing Instructions

  1. Open a Google Sheet
  2. Add any table of data (sales, attendance, grades, metrics—anything)
  3. Select the range
  4. Go to AI Tools → Open AI Data Analyst
  5. Ask a question:
    • “What trends do you see?”
    • “Any anomalies in this dataset?”
    • “Explain this data to a non-technical manager.”
  6. The results appear in a new sheet called AI Analysis

🎉 What You Built in Issue #3

You now have:

  • A fully functional AI Data Analyst for Sheets
  • Automatic trend detection
  • Anomaly detection
  • Insight generation
  • Chart suggestions
  • A real analyst workflow powered by Gemini
  • A sidebar-driven Sheets extension

This foundation sets up the next step:

🔥 Coming Next — Issue #4: RAG for Google Drive

Learn how to:

  • Read files from Drive
  • Chunk + embed content
  • Store embeddings
  • Retrieve relevant content
  • Build a real Retrieval-Augmented Generation system inside Apps Script

This will be one of the most powerful lessons in the entire series.