Build a Google Drive “Knowledge Base” Chatbot with Apps Script + Gemini

drive-rag-chatbot/
├── Code.gs // Apps Script backend
├── Index.html // Web app UI
└── README.md // Basic project documentation

Github https://github.com/lsvekis/Build-a-Google-Drive-Knowledge-Base-Chatbot

In this lesson, you’ll learn how to turn a Google Drive folder into a simple “knowledge base” chatbot, powered by:

• Google Apps Script
• Gemini (Google’s generative AI)
• Embeddings + cosine similarity
• A spreadsheet index to speed things up

You’ll be able to ask questions about the documents in a Drive folder (Docs, text files, and PDFs), and the bot will answer using only those files as context.

---

What We’re Building (High-Level)

Here’s the basic flow:

1. You choose a Drive folder that holds your content (Docs, PDFs, TXT).
2. Apps Script:
   • Reads the files
   • Splits them into smaller chunks (easier for AI to handle)
   • Uses Gemini’s embedding model to turn each chunk into a list of numbers
   • Stores those chunks + embeddings in a Google Sheet (this is our index/cache).
3. When you ask a question:
   • Your question is also turned into an embedding.
   • The script compares your question with all chunk embeddings using cosine similarity (how similar they are in meaning).
   • It picks the top matching chunks and sends them, along with your question, to Gemini.
   • Gemini generates an answer based on those chunks.

The result: a simple RAG (Retrieval-Augmented Generation) chatbot on top of a Drive folder.

---

Prerequisites

You’ll need:

1. A Google account.
2. A Gemini API key (from Google AI Studio).
3. A Google Drive folder with some content (Docs, PDFs, or .txt files).

---

Step 1 – Create the Apps Script Project

1. Go to script.google.com and create a new project.
2. Rename it to something like Drive RAG Chatbot.
3. In the default Code.gs file, we’ll add our server-side logic.

---

Step 2 – Add Your Gemini API Key as a Script Property

1. In the Apps Script editor, click the gear icon → Script properties (or Project Settings → Script properties).
2. Add a new property:
   • Name: GEMINI_API_KEY
   • Value: your Gemini API key
3. Save.

This lets the script read your API key securely with PropertiesService.

---

Step 3 – Enable the Advanced Drive Service (for PDFs)

To extract text from PDFs, we temporarily convert them to Google Docs using the Drive API.

1. In Apps Script, click Services in the left sidebar.
2. Click the + button.
3. Add Drive API (not just DriveApp; this is the Advanced Drive Service).

---

Step 4 – Add the Backend Code

Below is a complete version of the server-side script.

• It:
  • Reads a single Drive folder (you configure the ID).
  • Handles Docs + TXT + PDF.
  • Has a buildIndex function to precompute embeddings.
  • Uses a Sheet as a cache.
  • Exposes chatWithFolder(question) for the UI.

Replace the contents of Code.gs with this:

/***************************************
 * CONFIG
 ***************************************/
// TODO: Replace with your folder ID:
const DEFAULT_FOLDER_ID = 'PASTE_YOUR_FOLDER_ID_HERE';

const GENERATION_MODEL = 'gemini-2.5-flash';
const EMBEDDING_MODEL = 'gemini-embedding-001';

const CHUNK_SIZE = 800;        // characters per chunk
const TOP_K = 5;               // how many chunks to retrieve
const MAX_OUTPUT_TOKENS = 1024;

const DRIVE_FOLDER_URL_PREFIX = 'https://drive.google.com/drive/folders/';
const PROP_INDEX_SHEET_ID = 'RAG_INDEX_SHEET_ID';
const INDEX_SHEET_NAME = 'Index';

/***************************************
 * UI ENTRY: SERVE THE HTML
 ***************************************/
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index')
    .setTitle('Drive Folder RAG Chatbot');
}

/***************************************
 * FOLDER INFO (USED BY UI HEADER)
 ***************************************/

/**
 * Returns info about the configured knowledge base folder.
 * Used by the UI to show folder name + link.
 */
function getActiveFolderInfo() {
  const folderId = DEFAULT_FOLDER_ID;
  if (!folderId) return null;

  try {
    const folder = DriveApp.getFolderById(folderId);
    return {
      id: folderId,
      name: folder.getName(),
      url: DRIVE_FOLDER_URL_PREFIX + folderId
    };
  } catch (e) {
    return null;
  }
}

/***************************************
 * BUILD INDEX (RUN MANUALLY)
 ***************************************/

/**
 * Precompute embeddings for all chunks in the folder
 * and store them in a dedicated Spreadsheet.
 *
 * Run this from the editor or hook it to a menu/button.
 */
function buildIndex() {
  const folderId = DEFAULT_FOLDER_ID;
  if (!folderId) {
    throw new Error('DEFAULT_FOLDER_ID is not set.');
  }

  const chunks = getFolderChunks_(folderId);
  if (!chunks.length) {
    throw new Error('No readable text documents found in the folder.');
  }

  const sheet = getIndexSheet_();
  sheet.clear();

  // Header row
  const rows = [[
    'folderId',
    'fileId',
    'fileName',
    'chunkId',
    'chunkIndex',
    'chunkText',
    'embeddingJson'
  ]];

  // Build rows with embeddings
  chunks.forEach(chunk => {
    const embedding = embedText_(chunk.text, 'RETRIEVAL_DOCUMENT');
    const [fileId, chunkIndex] = chunk.id.split(':');

    rows.push([
      folderId,
      fileId,
      chunk.fileName,
      chunk.id,
      Number(chunkIndex),
      chunk.text,
      JSON.stringify(embedding)
    ]);
  });

  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);

  Logger.log(`Index built: ${chunks.length} chunks indexed.`);
  return {
    folderId,
    chunksIndexed: chunks.length,
    sheetUrl: sheet.getParent().getUrl()
  };
}

/***************************************
 * CHAT ENTRY (USED BY UI)
 ***************************************/

/**
 * Called from client: chatWithFolder(question)
 * Returns { answer: string, usedChunks: [...] }
 */
function chatWithFolder(question) {
  const q = (question || '').trim();
  if (!q) {
    throw new Error('Question is empty.');
  }

  // Try to use precomputed index first
  let docEmbeddings = getIndexedEmbeddings_();

  // Fallback: no index yet → compute on the fly
  if (!docEmbeddings.length) {
    const chunks = getFolderChunks_(DEFAULT_FOLDER_ID);
    if (!chunks.length) {
      return {
        answer: 'I could not find any readable text documents in the folder.',
        usedChunks: []
      };
    }
    docEmbeddings = chunks.map(chunk => ({
      chunk,
      embedding: embedText_(chunk.text, 'RETRIEVAL_DOCUMENT')
    }));
  }

  // Embed query
  const queryEmbedding = embedText_(q, 'RETRIEVAL_QUERY');

  // Rank chunks by cosine similarity
  const ranked = docEmbeddings
    .map(d => ({
      chunk: d.chunk,
      score: cosineSimilarity_(queryEmbedding, d.embedding)
    }))
    .sort((a, b) => b.score - a.score);

  const topChunks = ranked.slice(0, TOP_K);

  const contextText = topChunks.map((item, i) =>
    `[#${i + 1} FILE: ${item.chunk.fileName} | score=${item.score.toFixed(3)}]\n` +
    item.chunk.text
  ).join('\n\n');

  const fullPrompt =
    'You are an AI assistant. Use ONLY the information in the retrieved ' +
    'chunks below to answer the question.\n\n' +
    '--- RETRIEVED CHUNKS ---\n' +
    contextText +
    '\n--- END CHUNKS ---\n\n' +
    'Question: ' + q + '\n' +
    'If the answer is not contained in the chunks, say you do not know.';

  const answerText = callGeminiText_(fullPrompt);

  return {
    answer: answerText,
    usedChunks: topChunks.map((t, i) => ({
      index: i + 1,
      fileName: t.chunk.fileName,
      score: t.score
    }))
  };
}

/***************************************
 * STEP 1 — Read & Chunk Folder Docs
 ***************************************/
function getFolderChunks_(folderId) {
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFiles();

  const chunks = [];

  while (files.hasNext()) {
    const file = files.next();
    const text = (getFileText_(file) || '').trim();
    if (!text) continue;

    let start = 0;
    let idx = 0;
    const len = text.length;

    while (start < len) {
      const end = Math.min(start + CHUNK_SIZE, len);
      const chunkText = text.substring(start, end);

      chunks.push({
        id: file.getId() + ':' + idx,
        fileName: file.getName(),
        text: chunkText
      });

      start = end;
      idx++;
    }
  }

  return chunks;
}

/***************************************
 * STEP 2 & 3 — Embeddings via Gemini
 ***************************************/
function embedText_(text, taskType) {
  const apiKey = getApiKey_();
  const url =
    'https://generativelanguage.googleapis.com/v1beta/models/' +
    EMBEDDING_MODEL +
    ':embedContent?key=' +
    apiKey;

  const payload = {
    model: 'models/' + EMBEDDING_MODEL,
    content: { parts: [{ text }] },
    task_type: taskType || 'SEMANTIC_SIMILARITY'
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    muteHttpExceptions: true,
    payload: JSON.stringify(payload)
  };

  const res = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(res.getContentText());

  const embedding =
    data?.embedding?.values ||
    data?.embeddings?.[0]?.values;

  if (!embedding) {
    throw new Error('No embedding returned: ' + res.getContentText());
  }

  return embedding;
}

/***************************************
 * STEP 4 — Cosine similarity
 ***************************************/
function cosineSimilarity_(a, b) {
  if (!a || !b || a.length !== b.length) return 0;

  let dot = 0;
  let magA = 0;
  let magB = 0;

  for (let i = 0; i < a.length; i++) {
    const x = a[i];
    const y = b[i];
    dot += x * y;
    magA += x * x;
    magB += y * y;
  }

  if (!magA || !magB) return 0;
  return dot / (Math.sqrt(magA) * Math.sqrt(magB));
}

/***************************************
 * STEP 7 — Call Gemini text model
 ***************************************/
function callGeminiText_(prompt) {
  const apiKey = getApiKey_();
  const url =
    'https://generativelanguage.googleapis.com/v1beta/models/' +
    GENERATION_MODEL +
    ':generateContent?key=' +
    apiKey;

  const payload = {
    contents: [{ role: 'user', parts: [{ text: prompt }] }],
    generationConfig: {
      temperature: 0.3,
      maxOutputTokens: MAX_OUTPUT_TOKENS
    }
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    muteHttpExceptions: true,
    payload: JSON.stringify(payload)
  };

  const res = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(res.getContentText());

  const text = data?.candidates?.[0]?.content?.parts?.[0]?.text;
  return text || ('No response from Gemini. Raw: ' + res.getContentText());
}

/***************************************
 * UTIL — API KEY
 ***************************************/
function getApiKey_() {
  const key = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  if (!key) {
    throw new Error('Missing GEMINI_API_KEY in Script Properties.');
  }
  return key;
}

/***************************************
 * INDEX SHEET HELPERS
 ***************************************/
function getIndexSheet_() {
  const props = PropertiesService.getScriptProperties();
  let ssId = props.getProperty(PROP_INDEX_SHEET_ID);
  let ss;

  if (ssId) {
    ss = SpreadsheetApp.openById(ssId);
  } else {
    ss = SpreadsheetApp.create('Drive RAG Index');
    props.setProperty(PROP_INDEX_SHEET_ID, ss.getId());
  }

  let sheet = ss.getSheetByName(INDEX_SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(INDEX_SHEET_NAME);
  }

  return sheet;
}

/**
 * Read precomputed embeddings from the index sheet.
 * Returns array of { chunk: {id, fileName, text}, embedding: number[] }
 */
function getIndexedEmbeddings_() {
  const sheet = getIndexSheet_();
  const values = sheet.getDataRange().getValues();

  if (values.length <= 1) return []; // header only or empty

  const rows = values.slice(1);
  const docEmbeddings = [];

  rows.forEach(row => {
    const folderId = row[0];
    if (folderId !== DEFAULT_FOLDER_ID) return;

    const fileName = row[2];
    const chunkId = row[3];
    const chunkText = row[5];
    const embeddingJson = row[6];

    if (!chunkText || !embeddingJson) return;

    const embedding = JSON.parse(embeddingJson);

    docEmbeddings.push({
      chunk: {
        id: chunkId,
        fileName,
        text: chunkText
      },
      embedding
    });
  });

  return docEmbeddings;
}

/***************************************
 * FILE TEXT EXTRACTION
 * Supports: Google Docs, plain text, PDF
 ***************************************/
function getFileText_(file) {
  const mime = file.getMimeType();

  if (mime === MimeType.GOOGLE_DOCS) {
    return DocumentApp.openById(file.getId()).getBody().getText();
  }

  if (mime === MimeType.PLAIN_TEXT) {
    return file.getBlob().getDataAsString();
  }

  if (mime === MimeType.PDF) {
    return extractTextFromPdf_(file);
  }

  return '';
}

/**
 * Convert a PDF to a temporary Google Doc, read the text,
 * then move the temp doc to trash.
 *
 * Requires Advanced Drive Service:
 *  - Services → Add → Drive API
 */
function extractTextFromPdf_(file) {
  try {
    const resource = {
      title: file.getName(),
      mimeType: MimeType.GOOGLE_DOCS
    };

    const docFile = Drive.Files.copy(resource, file.getId());
    const doc = DocumentApp.openById(docFile.id);
    const text = doc.getBody().getText() || '';

    DriveApp.getFileById(docFile.id).setTrashed(true);
    return text;
  } catch (e) {
    Logger.log('PDF conversion failed for ' + file.getName() + ': ' + e);
    return '';
  }
}

🔧 Important:
Replace 'PASTE_YOUR_FOLDER_ID_HERE' with your actual Drive folder ID.

You can get the folder ID from the URL:

• Folder URL: https://drive.google.com/drive/folders/ABC123...
• Folder ID: ABC123...

---

Step 5 – Add the Web App Chat UI (Index.html)

Create a new file in Apps Script:

• File → New → HTML file
• Name it: Index
• Replace its contents with this:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
    <meta charset="UTF-8" />
    <style>
      body {
        font-family: system-ui, -apple-system, BlinkMacSystemFont, 'Segoe UI',
          sans-serif;
        margin: 0;
        padding: 16px;
        background: #020617;
        color: #e5e7eb;
      }
      .app {
        max-width: 900px;
        margin: 0 auto;
        background: #020617;
        border-radius: 16px;
        padding: 16px;
        box-shadow: 0 10px 40px rgba(0, 0, 0, 0.6);
        border: 1px solid #1f2937;
        display: flex;
        flex-direction: column;
        height: calc(100vh - 32px);
        box-sizing: border-box;
      }
      h1 {
        font-size: 1.5rem;
        margin-top: 0;
        margin-bottom: 0.4rem;
      }
      .subtitle {
        font-size: 0.9rem;
        color: #9ca3af;
        margin-bottom: 0.75rem;
      }
      .header-row {
        display: flex;
        align-items: center;
        justify-content: space-between;
        gap: 8px;
        margin-bottom: 10px;
        flex-wrap: wrap;
      }
      .kb-info {
        display: flex;
        align-items: center;
        gap: 8px;
        font-size: 0.8rem;
        color: #9ca3af;
      }
      .kb-info a {
        color: #60a5fa;
        text-decoration: none;
      }
      .badge {
        display: inline-flex;
        align-items: center;
        gap: 6px;
        padding: 3px 8px;
        border-radius: 999px;
        border: 1px solid #1f2937;
        font-size: 0.7rem;
        color: #9ca3af;
      }
      .badge-dot {
        width: 7px;
        height: 7px;
        border-radius: 999px;
        background: #22c55e;
      }
      .toolbar {
        display: flex;
        align-items: center;
        gap: 8px;
        font-size: 0.8rem;
      }
      .chat-shell {
        flex: 1;
        display: flex;
        flex-direction: column;
        border-radius: 12px;
        border: 1px solid #1f2937;
        background: #020617;
        padding: 10px;
        min-height: 0;
      }
      .messages {
        flex: 1;
        overflow-y: auto;
        padding: 8px;
        border-radius: 8px;
        background: #020617;
        font-size: 0.9rem;
      }
      .message {
        max-width: 80%;
        margin-bottom: 8px;
        padding: 8px 10px;
        border-radius: 12px;
        line-height: 1.4;
        white-space: pre-wrap;
      }
      .message.user {
        margin-left: auto;
        background: #0b1120;
        border: 1px solid #1d4ed8;
        text-align: right;
      }
      .message.bot {
        margin-right: auto;
        background: #020617;
        border: 1px solid #334155;
      }
      .message.system {
        margin: 4px auto 10px auto;
        background: #020617;
        border: 1px dashed #374151;
        font-size: 0.8rem;
        color: #9ca3af;
        text-align: center;
      }
      .bubble-label {
        display: block;
        font-size: 0.7rem;
        color: #9ca3af;
        margin-bottom: 2px;
      }
      .input-area {
        margin-top: 8px;
        border-top: 1px solid #1f2937;
        padding-top: 8px;
      }
      .input-row {
        display: flex;
        gap: 8px;
      }
      textarea {
        flex: 1;
        resize: none;
        min-height: 46px;
        max-height: 110px;
        padding: 8px 10px;
        border-radius: 8px;
        border: 1px solid #374151;
        background: #020617;
        color: #e5e7eb;
        font-family: inherit;
        font-size: 0.9rem;
      }
      button {
        padding: 8px 12px;
        border-radius: 8px;
        border: none;
        background: #3b82f6;
        color: white;
        font-weight: 600;
        cursor: pointer;
        font-size: 0.9rem;
        white-space: nowrap;
      }
      button:disabled {
        opacity: 0.6;
        cursor: default;
      }
      .status {
        font-size: 0.8rem;
        color: #9ca3af;
        margin-top: 4px;
        min-height: 1.2em;
      }
      .status.error {
        color: #f97373;
      }
      .build-status {
        font-size: 0.75rem;
        color: #9ca3af;
      }
      .build-status strong {
        color: #e5e7eb;
      }
      .typing {
        display: inline-flex;
        align-items: center;
        gap: 4px;
      }
      .typing-dot {
        width: 4px;
        height: 4px;
        border-radius: 999px;
        background: #9ca3af;
        opacity: 0.7;
        animation: pulse 1s infinite ease-in-out;
      }
      .typing-dot:nth-child(2) {
        animation-delay: 0.15s;
      }
      .typing-dot:nth-child(3) {
        animation-delay: 0.3s;
      }
      @keyframes pulse {
        0%, 100% { transform: translateY(0); opacity: 0.3; }
        50% { transform: translateY(-2px); opacity: 1; }
      }
    </style>
  </head>
  <body>
    <div class="app">
      <header>
        <h1>Drive Knowledge Chatbot</h1>
        <div class="subtitle">
          Ask questions about the documents in your Google Drive folder.
          The bot will search the folder index and answer using those files.
        </div>

        <div class="header-row">
          <div class="kb-info">
            <div class="badge">
              <span class="badge-dot"></span>
              <span id="kbLabel">Loading knowledge base…</span>
            </div>
            <div id="kbLink"></div>
          </div>

          <div class="toolbar">
            <button id="buildIndexBtn" onclick="onBuildIndex()">
              Build Index
            </button>
            <span id="buildStatus" class="build-status"></span>
          </div>
        </div>
      </header>

      <main class="chat-shell">
        <div id="messages" class="messages"></div>

        <div class="input-area">
          <div class="input-row">
            <textarea
              id="userInput"
              placeholder="Ask anything about the folder documents… (Enter to send, Shift+Enter for a new line)"
            ></textarea>
            <button id="sendBtn" onclick="onSend()">Send</button>
          </div>
          <div id="status" class="status"></div>
        </div>
      </main>
    </div>

    <script>
      const messagesEl = document.getElementById('messages');
      const userInputEl = document.getElementById('userInput');
      const sendBtn = document.getElementById('sendBtn');
      const statusEl = document.getElementById('status');
      const kbLabelEl = document.getElementById('kbLabel');
      const kbLinkEl = document.getElementById('kbLink');
      const buildIndexBtn = document.getElementById('buildIndexBtn');
      const buildStatusEl = document.getElementById('buildStatus');

      let typingMessageEl = null;

      function addMessage(text, sender) {
        const wrapper = document.createElement('div');
        wrapper.className = 'message ' + sender;

        if (sender === 'user' || sender === 'bot') {
          const label = document.createElement('span');
          label.className = 'bubble-label';
          label.textContent = sender === 'user' ? 'You' : 'Assistant';
          wrapper.appendChild(label);
        }

        const content = document.createElement('div');
        content.textContent = text;
        wrapper.appendChild(content);

        messagesEl.appendChild(wrapper);
        messagesEl.scrollTop = messagesEl.scrollHeight;
        return wrapper;
      }

      function addSystemMessage(text) {
        const wrapper = document.createElement('div');
        wrapper.className = 'message system';
        wrapper.textContent = text;
        messagesEl.appendChild(wrapper);
        messagesEl.scrollTop = messagesEl.scrollHeight;
        return wrapper;
      }

      function showTyping() {
        if (typingMessageEl) return;
        typingMessageEl = document.createElement('div');
        typingMessageEl.className = 'message bot';
        const label = document.createElement('span');
        label.className = 'bubble-label';
        label.textContent = 'Assistant';
        typingMessageEl.appendChild(label);

        const typing = document.createElement('div');
        typing.className = 'typing';
        typing.innerHTML =
          '<span class="typing-dot"></span>' +
          '<span class="typing-dot"></span>' +
          '<span class="typing-dot"></span>';
        typingMessageEl.appendChild(typing);

        messagesEl.appendChild(typingMessageEl);
        messagesEl.scrollTop = messagesEl.scrollHeight;
      }

      function hideTyping() {
        if (typingMessageEl && typingMessageEl.parentNode) {
          typingMessageEl.parentNode.removeChild(typingMessageEl);
        }
        typingMessageEl = null;
      }

      function setLoading(isLoading) {
        sendBtn.disabled = isLoading;
        sendBtn.textContent = isLoading ? 'Thinking…' : 'Send';
      }

      function showStatus(msg, isError) {
        statusEl.textContent = msg || '';
        statusEl.classList.toggle('error', !!isError);
      }

      function onSend() {
        const text = userInputEl.value.trim();
        if (!text) {
          showStatus('Please enter a question.', true);
          return;
        }
        showStatus('', false);

        addMessage(text, 'user');
        userInputEl.value = '';
        autoGrowTextarea();

        setLoading(true);
        showTyping();

        google.script.run
          .withSuccessHandler(function (res) {
            setLoading(false);
            hideTyping();
            if (res && res.answer) {
              addMessage(res.answer, 'bot');
            } else {
              addMessage(
                'I could not generate an answer from the folder documents.',
                'bot'
              );
            }
          })
          .withFailureHandler(function (err) {
            setLoading(false);
            hideTyping();
            showStatus(err.message || String(err), true);
            addMessage('Sorry, something went wrong on the server.', 'bot');
          })
          .chatWithFolder(text);
      }

      function onBuildIndex() {
        buildStatusEl.textContent =
          'Building index… this may take some time for large folders.';
        buildIndexBtn.disabled = true;
        buildIndexBtn.textContent = 'Building…';

        google.script.run
          .withSuccessHandler(function (res) {
            buildIndexBtn.disabled = false;
            buildIndexBtn.textContent = 'Rebuild Index';
            if (res && res.chunksIndexed != null) {
              buildStatusEl.innerHTML =
                'Index built for <strong>' +
                res.chunksIndexed +
                '</strong> chunks. ' +
                'You can now ask questions faster.';
            } else {
              buildStatusEl.textContent =
                'Index built. You can now ask questions.';
            }
          })
          .withFailureHandler(function (err) {
            buildIndexBtn.disabled = false;
            buildIndexBtn.textContent = 'Build Index';
            buildStatusEl.textContent =
              'Index build failed: ' + (err.message || String(err));
          })
          .buildIndex();
      }

      function initKbInfo() {
        google.script.run
          .withSuccessHandler(function (info) {
            if (info && info.name && info.url) {
              kbLabelEl.textContent = 'Knowledge base: ' + info.name;
              kbLinkEl.innerHTML =
                '<a href="' +
                info.url +
                '" target="_blank" rel="noopener noreferrer">Open folder</a>';
            } else {
              kbLabelEl.textContent =
                'Knowledge base: Default Drive folder (configured in script).';
            }
          })
          .withFailureHandler(function () {
            kbLabelEl.textContent =
              'Knowledge base: Default Drive folder (configured in script).';
          })
          .getActiveFolderInfo();
      }

      function autoGrowTextarea() {
        userInputEl.style.height = 'auto';
        userInputEl.style.height = userInputEl.scrollHeight + 'px';
      }

      document.addEventListener('DOMContentLoaded', function () {
        addSystemMessage(
          'You are chatting with an AI assistant that uses a Google Drive folder ' +
            'as its knowledge base. Build the index first for best performance, ' +
            'then start asking questions about those documents.'
        );

        initKbInfo();

        userInputEl.addEventListener('input', autoGrowTextarea);

        userInputEl.addEventListener('keydown', function (e) {
          if (e.key === 'Enter' && !e.shiftKey) {
            e.preventDefault();
            onSend();
          }
        });

        autoGrowTextarea();
      });
    </script>
  </body>
</html>

---

Step 6 – Deploy as a Web App

To make this accessible as a web page:

1. In the Apps Script editor, click Deploy → New deployment.
2. Choose “Web app”.
3. Set:
   • Execute as: Me
   • Who has access: Anyone with the link (or your choice).
4. Click Deploy, then copy the Web App URL.

Share that URL or bookmark it — that’s your Drive-powered RAG chatbot.

---

Step 7 – Build the Index

Before your chatbot can use the sheet cache, run:

1. Open the Apps Script editor.
2. Select the function buildIndex.
3. Click Run.
4. Authorize the script (Drive, Sheets, etc.).

This will:

• Read documents from your Drive folder.
• Split them into chunks.
• Ask Gemini to embed each chunk.
• Store all of that in a spreadsheet named “Drive RAG Index”.

After that, chatWithFolder() will use the precomputed index instead of re-calculating embeddings every time, which is much faster.

---

How to Explain This to Non-Developers

If you’re sharing this with non-programmers, you can describe it like this:

We created an index by storing pieces of our documents, plus their AI “meaning fingerprints,” in a Google Sheet.
When you ask a question, the system finds which pieces of text are most similar in meaning to your question, and only then asks Gemini to answer using those pieces.
Because we store the fingerprints in a sheet, the system doesn’t have to re-scan everything each time, so it’s much faster.