AI Knowledge Base Builder for Google Drive Apps Script Gemini Issue 10

by

🚀 Apps Script + Gemini Mastery — Issue #10

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

AI Knowledge Base Builder for Google Drive

Turn a Drive folder of Docs into a searchable “AI knowledge base” using Apps Script + Gemini.

⭐ What You Will Build

You’ll build a Google Workspace tool that:

📁 Scans a Drive folder of Google Docs
🧠 Uses Gemini to extract + summarize key knowledge
🧾 Stores structured knowledge in a Google Sheet (your “index”)
🔎 Lets users ask questions from a sidebar
✨ Returns answers with citations (Doc name + link)

This is the foundation for internal helpdesks, course knowledge bases, SOP assistants, or client documentation bots.

🧠 Learning Objectives

Readers will learn how to:

✔ Process Drive folders with DriveApp
✔ Extract text from Google Docs programmatically
✔ Build an “AI index” stored in Sheets
✔ Design a question-answer pipeline (search → context → answer)
✔ Implement citation-style output (links to original sources)
✔ Add a sidebar UI in Google Sheets

🧩 Architecture (Simple RAG Pattern)

Step 1 — Indexing (Build once / refresh):
Docs → extract text → Gemini summary → store in Sheet

Step 2 — Q&A (Run anytime):
User question → pick best summaries → Gemini answer → show citations

No external database needed.

✅ Issue #10 Lesson Build

1️⃣ Create the Sheet “Index” Structure

In a Google Sheet, create a tab called: KB_Index

Headers (Row 1):

ABCDE
docIdtitleurlsummarykeywords

This sheet is your lightweight knowledge base.

2️⃣ Menu + Sidebar

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("KB Builder (Index Folder)", "showKbBuilder")
    .addItem("KB Q&A (Ask)", "showKbAsk")
    .addToUi();
}

function showKbBuilder() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar_Index")
      .setTitle("KB Builder")
  );
}

function showKbAsk() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar_Ask")
      .setTitle("KB Q&A")
  );
}

3️⃣ Sidebar UI — Index Builder

Sidebar_Index.html

<div style="font-family:Arial;padding:14px;">
  <h2>KB Builder</h2>
  <p>Index a Google Drive folder of Docs into a Sheet.</p>

  <label><b>Drive Folder ID</b></label><br>
  <input id="folderId" style="width:100%;margin-bottom:8px;"
         placeholder="Paste folder ID here" />

  <button onclick="runIndex()">Index Folder</button>

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

  <script>
    function runIndex() {
      document.getElementById("out").textContent = "Indexing...";
      google.script.run
        .withSuccessHandler(msg => document.getElementById("out").textContent = msg)
        .indexFolder(document.getElementById("folderId").value);
    }
  </script>
</div>

4️⃣ Sidebar UI — Ask Questions

Sidebar_Ask.html

<div style="font-family:Arial;padding:14px;">
  <h2>KB Q&A</h2>
  <p>Ask questions using your indexed Docs.</p>

  <label><b>Question</b></label><br>
  <textarea id="q" style="width:100%;height:80px;"></textarea>

  <button onclick="ask()">Ask</button>

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

  <script>
    function ask() {
      document.getElementById("out").textContent = "Searching...";
      google.script.run
        .withSuccessHandler(msg => document.getElementById("out").textContent = msg)
        .askKb(document.getElementById("q").value);
    }
  </script>
</div>

5️⃣ Index Folder → Store Summaries

KbIndexer.gs

function indexFolder(folderId) {
  if (!folderId) return "Please provide a folder ID.";

  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(MimeType.GOOGLE_DOCS);

  const sheet = getKbSheet_();
  let count = 0;

  while (files.hasNext()) {
    const file = files.next();
    const docId = file.getId();
    const title = file.getName();
    const url = file.getUrl();

    const text = extractDocText_(docId);
    const summary = summarizeForKb_(title, text);

    const keywords = extractKeywords_(title, text);

    sheet.appendRow([docId, title, url, summary, keywords.join(", ")]);
    count++;
  }

  return "✅ Indexed " + count + " docs into KB_Index.";
}

6️⃣ Doc Text Extraction

DocUtils.gs

function extractDocText_(docId) {
  const doc = DocumentApp.openById(docId);
  return doc.getBody().getText();
}

function getKbSheet_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("KB_Index");
  if (!sheet) throw new Error("Create a sheet tab named KB_Index.");
  return sheet;
}

7️⃣ Gemini Summarizer + Keyword Extractor

KbGemini.gs

function summarizeForKb_(title, text) {
  const prompt = `
Summarize this doc for a searchable knowledge base.
Return 4-7 bullet-style sentences in plain text.
Doc title: ${title}
Doc text:
${text.substring(0, 12000)}
`;
  return callGemini(prompt, "");
}

function extractKeywords_(title, text) {
  const prompt = `
Return ONLY JSON array of 8-15 keywords for search.
Doc title: ${title}
Doc text:
${text.substring(0, 12000)}
`;
  const out = callGemini(prompt, "");
  try { return JSON.parse(out); } catch(e) { return []; }
}

8️⃣ Ask → Find Best Matches → Answer with Citations

KbAsk.gs

function askKb(question) {
  if (!question) return "Enter a question.";

  const sheet = getKbSheet_();
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return "KB_Index is empty. Run indexing first.";

  const rows = values.slice(1);

  // Simple scoring: keyword/title contains
  const ranked = rows.map(r => {
    const title = String(r[1] || "");
    const summary = String(r[3] || "");
    const keywords = String(r[4] || "");
    const hay = (title + " " + summary + " " + keywords).toLowerCase();
    const score = (hay.includes(question.toLowerCase())) ? 10 : 0;
    return { row:r, score };
  }).sort((a,b)=>b.score-a.score);

  const top = ranked.slice(0, 5).map(x => ({
    title: x.row[1],
    url: x.row[2],
    summary: x.row[3]
  }));

  const prompt = `
Answer the question using ONLY the provided sources.
If the sources don't contain the answer, say what is missing.

Question: ${question}

Sources:
${JSON.stringify(top)}

Return:
- Answer (short)
- Citations list (title + url)
`;

  return callGemini(prompt, "");
}

🔥 Exercise Upgrades (For Advanced Users)

✅ Add “re-index” mode (update rows instead of append)
✅ Add embeddings (optional) using text-embedding models
✅ Add chunking for large Docs
✅ Better ranking (keyword overlap scoring)
✅ Add a “Source preview” in sidebar

✅ Testing Checklist

  1. Create KB_Index tab with headers
  2. Run testGeminiConnection()
  3. Use a folder with 3–5 docs to start
  4. Run Indexing
  5. Ask questions + verify citations

🔜 Coming Next (Issue #11)

AI Email Draft Assistant for Gmail (Context-Aware)
Use Gmail threads + Gemini to draft replies with tone + action items.