🚀 Apps Script + Gemini Mastery — Issue #16

AI Spreadsheet Data Analyzer

Use Apps Script + Gemini to automatically analyze spreadsheet data, identify trends, detect anomalies, and generate plain-English insights.

---

⭐ What You Will Build

In this issue, you’ll build an AI Data Analyzer for Google Sheets that can:

📊 Read spreadsheet data
🧠 Analyze it with Gemini
📈 Identify trends and patterns
⚠️ Detect anomalies or unusual values
📝 Generate a written summary of key insights
📄 Output the results into a new sheet or sidebar

---

🧠 Why This Project Matters

Many people can collect data.
Far fewer can quickly interpret it.

This tool helps bridge that gap.

Instead of staring at rows and columns, users can ask for:

• key trends
• performance summaries
• anomalies
• recommendations

It transforms raw data into decision-ready insights.

---

🧩 Architecture

Spreadsheet data
↓
Apps Script reads values
↓
Gemini analyzes patterns
↓
Apps Script writes insights back to Sheets

---

🧱 Step 1 — Add the Menu

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("AI Data Analyzer", "showAnalyzerSidebar")
    .addToUi();
}function showAnalyzerSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar")
      .setTitle("AI Data Analyzer")
  );
}

---

🧱 Step 2 — Sidebar UI

Sidebar.html

<div style="font-family:Arial;padding:14px;">  <h2>AI Data Analyzer</h2>  <label><b>What would you like analyzed?</b></label>  <textarea id="prompt" style="width:100%;height:90px;">
Example: Analyze sales trends and identify anomalies
</textarea>  <button onclick="analyze()">Analyze Data</button>  <pre id="output" style="white-space:pre-wrap;margin-top:12px;"></pre><script>function analyze() {  document.getElementById("output").textContent = "Analyzing data..."  google.script.run
    .withSuccessHandler(res => {
      document.getElementById("output").textContent = res
    })
    .analyzeSheetData(
      document.getElementById("prompt").value
    )}</script></div>

---

🧱 Step 3 — Read Spreadsheet Data

SheetReader.gs

function getSheetData_() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();  return {
    sheetName: sheet.getName(),
    headers: values[0],
    rows: values.slice(1)
  };
}

---

🧱 Step 4 — Send Data to Gemini

DataAnalyzer.gs

function analyzeSheetData(userPrompt) {
  if (!userPrompt) return "Enter an analysis request.";  const data = getSheetData_();  const prompt = `
You are an expert spreadsheet data analyst.Analyze the spreadsheet data below.Sheet name:
${data.sheetName}Headers:
${data.headers.join(", ")}Rows:
${JSON.stringify(data.rows.slice(0, 200))}User request:
${userPrompt}Return JSON in this format:{
  "summary": "short overall summary",
  "insights": [
    "insight 1",
    "insight 2"
  ],
  "anomalies": [
    "anomaly 1",
    "anomaly 2"
  ],
  "recommendations": [
    "recommendation 1",
    "recommendation 2"
  ]
}
`;  let result;  try {
    result = callGemini(prompt, "");
  } catch (e) {
    return "Gemini error: " + e;
  }  result = result.replace(/```json/g, "").replace(/```/g, "").trim();  let json;
  try {
    json = JSON.parse(result);
  } catch (e) {
    Logger.log(result);
    return "Could not parse Gemini output.";
  }  return writeAnalysisToSheet_(json);
}

---

🧱 Step 5 — Write Insights to a New Sheet

AnalysisWriter.gs

function writeAnalysisToSheet_(analysis) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();  let sheet = ss.getSheetByName("AI Analysis");  if (!sheet) {
    sheet = ss.insertSheet("AI Analysis");
  } else {
    sheet.clear();
  }  let row = 1;  sheet.getRange(row++, 1).setValue("Summary");
  sheet.getRange(row++, 1).setValue(analysis.summary || "");  row++;
  sheet.getRange(row++, 1).setValue("Insights");
  (analysis.insights || []).forEach(item => {
    sheet.getRange(row++, 1).setValue(item);
  });  row++;
  sheet.getRange(row++, 1).setValue("Anomalies");
  (analysis.anomalies || []).forEach(item => {
    sheet.getRange(row++, 1).setValue(item);
  });  row++;
  sheet.getRange(row++, 1).setValue("Recommendations");
  (analysis.recommendations || []).forEach(item => {
    sheet.getRange(row++, 1).setValue(item);
  });  return "Analysis written to AI Analysis sheet.";
}

---

🧱 Step 6 — Gemini Helper

Reuse the same helper from earlier issues.

GeminiHelpers.gs

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" + text : "")
      }]
    }]
  };  const res = UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  });  const json = JSON.parse(res.getContentText());
  return json.candidates[0].content.parts[0].text;
}

---

🧪 Example Requests

Users can ask:

Analyze sales trends and identify anomalies

Summarize customer performance by region

Find unusual values and suggest next steps

Explain this spreadsheet in plain English

---

🔥 Advanced Exercises

Readers can extend this by adding:

✅ automatic chart generation from insights
✅ KPI extraction
✅ multi-sheet analysis
✅ anomaly scoring
✅ export to Google Docs report

---

🔜 Next Issue (#17)

AI Spreadsheet Cleaner + Fixer

Describe data cleanup rules → Apps Script cleans and standardizes the spreadsheet automatically.