AI Report Generator for Google Docs Apps Script and Gemini 9

by

https://github.com/lsvekis/Apps-Script-Code-Snippets/tree/main/apps_script_gemini_ai_report_generator_docs

🚀 Apps Script + Gemini Mastery — Issue #9

AI Report Generator for Google Docs

Turn Google Sheets data into polished executive reports automatically using Apps Script + Gemini.

⭐ What You Will Build in This Issue

In this lesson, readers will build an AI Report Generator that:

📊 Reads structured data from Google Sheets
🧠 Uses Gemini to analyze trends and insights
📝 Generates executive-ready narratives
📄 Writes directly into a Google Doc
🧩 Applies headings, sections, and summaries
🔁 Can be re-run to regenerate reports on demand

This tool bridges data → insight → documentation, all inside Google Workspace.

🧠 Learning Objectives

By the end of this issue, readers will know how to:

✔ Read and summarize tabular data from Sheets
✔ Prompt Gemini for analytical writing, not just text generation
✔ Return structured JSON or markdown safely
✔ Insert and format content in Google Docs via Apps Script
✔ Design reusable AI reporting workflows
✔ Build “human-in-the-loop” AI document generators

🧩 Exercise — Build the AI Report Generator

You’ll create:

  1. A Sheets menu to launch report generation
  2. A sidebar UI to select report style and audience
  3. A Gemini analyzer for insights
  4. A Docs writer that formats sections cleanly

1️⃣ Menu Entry (Sheets)

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("Generate AI Report", "showReportUI")
    .addToUi();
}

function showReportUI() {
  const html = HtmlService.createHtmlOutputFromFile("Sidebar")
    .setTitle("AI Report Generator");
  SpreadsheetApp.getUi().showSidebar(html);
}

2️⃣ Sidebar UI

Sidebar.html

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

  <label><b>Audience</b></label>
  <input id="audience" style="width:100%; margin-bottom:8px;"
         placeholder="e.g., Executives, Managers, Clients" />

  <label><b>Report Tone</b></label>
  <select id="tone" style="width:100%; margin-bottom:8px;">
    <option>Executive Summary</option>
    <option>Technical Analysis</option>
    <option>Plain Language</option>
  </select>

  <button onclick="generate()">Generate Report</button>

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

  <script>
    function generate() {
      document.getElementById("output").textContent = "Analyzing data...";
      google.script.run
        .withSuccessHandler(msg => document.getElementById("output").textContent = msg)
        .generateReport(
          document.getElementById("audience").value,
          document.getElementById("tone").value
        );
    }
  </script>
</div>

3️⃣ Read Data + Summarize

DataReader.gs

function getSheetSummary_() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();

  if (values.length < 2) throw new Error("Sheet must include headers + data.");

  return {
    headers: values[0],
    rows: values.slice(1)
  };
}

4️⃣ Gemini Analysis Prompt

ReportGenerator.gs

function generateReport(audience, tone) {
  const data = getSheetSummary_();

  const prompt = `
You are an expert data analyst and business writer.

Analyze the following dataset and write a report.

Audience: ${audience || "General"}
Tone: ${tone || "Executive Summary"}

Dataset:
${JSON.stringify(data)}

Return JSON ONLY using this structure:
{
  "title": "Report title",
  "sections": [
    {
      "heading": "Section heading",
      "content": "Paragraph text"
    }
  ],
  "summary": "One-paragraph executive summary"
}
`;

  const jsonText = callGemini(prompt, "");
  const report = JSON.parse(jsonText);

  const docUrl = writeReportToDoc_(report);
  return "✅ Report created:\n" + docUrl;
}

5️⃣ Write to Google Docs

DocWriter.gs

function writeReportToDoc_(report) {
  const doc = DocumentApp.create(report.title || "AI Generated Report");
  const body = doc.getBody();

  body.appendParagraph(report.title)
      .setHeading(DocumentApp.ParagraphHeading.TITLE);

  body.appendParagraph(report.summary)
      .setHeading(DocumentApp.ParagraphHeading.HEADING1);

  (report.sections || []).forEach(sec => {
    body.appendParagraph(sec.heading)
        .setHeading(DocumentApp.ParagraphHeading.HEADING2);
    body.appendParagraph(sec.content);
  });

  return doc.getUrl();
}

🧪 Testing Strategy

Readers should test in stages:

1️⃣ testGeminiConnection()
2️⃣ Log Gemini JSON output only
3️⃣ Validate report structure
4️⃣ Write to Docs only after verification

🎯 Why This Issue Matters

This pattern enables:

📈 Automated business reporting
📊 KPI summaries
🧠 AI-assisted decision documents
📄 Executive briefings
🔁 Repeatable insights from live data

It’s one of the highest-ROI uses of AI inside Google Workspace.