Build a Spreadsheet Chatbot with Google Apps Script and Gemini

by

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

Artificial intelligence becomes far more useful when it can answer questions using your own data.

One of the most practical ways to do this is to connect AI to a Google Sheet and create a chatbot that can read spreadsheet content, respond to questions, and stay grounded in the data already stored in the sheet.

In this tutorial, we will build a production-ready spreadsheet chatbot using Google Apps Script and Gemini. The chatbot will run directly inside Google Sheets, open in a sidebar, read data from a selected tab, and answer questions using only the spreadsheet data. It will not rely on outside knowledge, and it will return a clear fallback response whenever the answer is not found in the sheet.

This approach is ideal for internal business data, project tracking sheets, CRM-style records, employee lists, inventory sheets, student data, and many other structured spreadsheet use cases.

Why Build a Spreadsheet Chatbot?

Spreadsheets contain a huge amount of valuable information, but finding specific answers often means scrolling, filtering, searching, or writing formulas. A chatbot interface makes spreadsheet data far more accessible.

Instead of manually scanning rows, users can ask questions such as:

  • Which employees are active in Sales?
  • How many projects are overdue?
  • What is Anna’s status?
  • List all records from Toronto.
  • Which items are low in stock?

By combining the data-handling power of Google Sheets with the language capabilities of Gemini, we can create a smart assistant that helps users interact with spreadsheet data in natural language.

What This Project Does

This chatbot project includes several features that make it much more practical than a simple proof of concept.

It adds a custom menu inside Google Sheets so users can launch the chatbot from the spreadsheet. It opens a sidebar interface where users can type questions, view answers, manage settings, and review recent chat history. The code reads spreadsheet data from a selected sheet, filters the most relevant rows based on the question, and sends only those approved rows to Gemini. The model is instructed to answer only from the spreadsheet content and not use any outside knowledge.

The result is a chatbot that feels conversational while still staying grounded in the spreadsheet as the source of truth.

Why This Architecture Matters

A common mistake when building AI spreadsheet tools is sending all spreadsheet data to the model without any control or structure. That can work for small experiments, but it quickly becomes unreliable and inefficient.

A better pattern is to let Apps Script handle the spreadsheet logic and use Gemini mainly as the language layer.

In this project, Apps Script does the following:

  • Reads the sheet data
  • Cleans and filters rows
  • Scores rows for relevance to the user’s question
  • Builds a structured context from the selected rows
  • Sends only that context to Gemini

Gemini then does the final step:

  • Interprets the question
  • Generates a concise natural-language answer
  • Stays limited to the spreadsheet context provided in the prompt

This keeps the spreadsheet in control and reduces hallucinations.

Core Features of the Chatbot

The full project includes:

Sidebar Chat Interface

The chatbot runs inside a Google Sheets sidebar, making it easy for spreadsheet users to ask questions without leaving the sheet.

Sheet Selection

Users can choose which spreadsheet tab the chatbot should use as its data source.

Smart Row Filtering

Instead of sending every row to Gemini, the script ranks rows based on the question and sends the most relevant ones.

Grounded Prompting

The model receives strict instructions to answer only from the approved spreadsheet rows.

Fallback Handling

If the information is not clearly present in the sheet, the chatbot responds with:

That information is not available in the sheet.

Optional Row Citations

The chatbot can include row references such as Row 12 when helpful.

Chat History

Recent questions and answers are stored per user using Apps Script user properties.

Settings Management

The chatbot supports configurable model selection, row limits, temperature, history size, and row citation preferences.

Files in the Project

The project is organized into multiple files so the code stays clean and maintainable.

Code.gs

This file handles the custom menu, opens the sidebar, initializes settings, loads sidebar data, saves settings, resets chat history, and processes user questions.

Config.gs

This file manages configuration values using Apps Script properties. It also retrieves the Gemini API key and stores per-user chat history.

SheetData.gs

This file reads spreadsheet data, filters rows, ranks relevance, formats context text, and builds the grounded prompt.

Gemini.gs

This file sends requests to the Gemini API and returns the generated response.

index.html

This is the main sidebar UI layout.

styles.html

This file contains the CSS styling for the sidebar interface.

scripts.html

This file contains the client-side JavaScript that calls server-side Apps Script functions and updates the UI.

How the Chatbot Works

Let’s walk through the flow step by step.

When the spreadsheet opens, Apps Script adds a custom menu called Sheet Chatbot. The user clicks the menu and opens the sidebar.

Inside the sidebar, the user enters a question such as:

Which employees are active in Sales?

The client-side script sends the question to the Apps Script server function. The server reads the selected sheet, extracts the data, removes empty rows, and scores rows for relevance based on the question.

The script then formats the matching rows into a structured text block, including headers and row numbers. That structured content is inserted into a prompt that tells Gemini to answer only from that spreadsheet data.

Gemini returns an answer, which is then displayed in the sidebar. The question and answer are added to chat history, and metadata such as sheet name and number of rows used is shown in the interface.

Grounding the Model to Spreadsheet Data

One of the most important parts of this project is the prompt design.

The chatbot prompt includes rules like these:

  • Answer only from the approved spreadsheet rows
  • Never use outside knowledge
  • Never infer unsupported facts
  • If the answer is not in the sheet, respond with the fallback message
  • Use row references when helpful

This grounding step makes the chatbot far safer and more useful than a general-purpose AI assistant.

Without grounding, the model might guess or fill in missing details. With grounding, the spreadsheet remains the trusted source.

Row Ranking and Relevance Filtering

A key improvement in this project is the use of Apps Script to filter rows before sending them to Gemini.

The script tokenizes the user’s question, compares those terms against row values and headers, and assigns a simple relevance score. Rows with higher scores are kept, while unrelated rows are removed.

This provides several benefits:

  • Smaller prompts
  • Lower token usage
  • Faster responses
  • Better answer quality
  • More relevant context sent to Gemini

This is especially useful when the spreadsheet contains many rows and only a small subset is needed to answer a question.

The User Interface

The sidebar interface is designed to be simple and practical.

Users can:

  • Type a question
  • Ask the chatbot
  • View the answer
  • See sheet and row metadata
  • Change settings
  • Reset chat history

The answer panel displays the chatbot response clearly, while the settings section gives users control over how the chatbot behaves.

This makes the project suitable for real users rather than just developer testing.

Storing Settings and History

Apps Script property services are used to store configuration and history at different levels.

Script Properties store the Gemini API key.

Document Properties store chatbot settings tied to the spreadsheet, such as selected sheet, model, temperature, maximum rows, and citation preferences.

User Properties store per-user chat history.

This structure keeps the project organized and makes it easier to manage different types of application data.

Example Use Cases

This spreadsheet chatbot can be adapted for many real-world scenarios.

A business team can use it to search sales records, customer data, or employee information. A teacher can use it with student data or assignment tracking sheets. A project manager can use it to check task status, deadlines, and team assignments. A content creator can use it to query article lists, production schedules, or publishing workflows.

Any spreadsheet with clear headers and structured rows can become much easier to work with through a natural-language interface.

Best Spreadsheet Format for This Chatbot

The chatbot works best when the sheet has:

  • A header row in the first row
  • One record per row
  • Consistent columns
  • No merged cells
  • No decorative or blank rows above the data

For example:

EmployeeDepartmentStatusStart DateLocation
AnnaHRActive2024-02-01Toronto
JohnSalesOn Leave2023-10-15Calgary

With a clean structure like this, the chatbot can answer questions much more accurately.

Why Apps Script Is a Great Fit

Google Apps Script is a strong choice for this kind of project because it is already tightly integrated with Google Sheets.

It gives developers access to spreadsheet data, document properties, user properties, custom menus, sidebars, and HTML-based interfaces without needing extra infrastructure. Since the chatbot runs directly inside the spreadsheet, the user experience is smooth and practical.

For teams already working in Google Workspace, this makes deployment much easier.

Why Gemini Works Well Here

Gemini is a strong language layer for this kind of spreadsheet chatbot because it can interpret natural-language questions, summarize structured data, and generate readable responses.

In this project, Gemini is not used as the data source. Instead, it is used to transform spreadsheet content into a helpful conversational answer. That distinction is important.

The sheet contains the facts. Gemini helps present those facts in a useful way.

Production Considerations

Although this project is far more robust than a quick prototype, there are still several ways to make it even stronger.

For highly reliable workflows, you may want Apps Script to handle exact counts, deterministic filters, unique value lists, and column-based comparisons directly. Gemini can then be used only to format the response.

You may also want to add:

  • Hidden sheet logging
  • Tab restrictions
  • Role-based access
  • Multi-sheet routing
  • Exact field matching
  • Custom command handling for count, list, and lookup queries

These additions can make the chatbot even more reliable in production environments.

Final Thoughts

A spreadsheet chatbot is one of the most practical ways to bring AI into everyday workflows.

By combining Google Apps Script and Gemini, you can create an assistant that feels modern and conversational while still staying tied to the actual spreadsheet data. This project shows how to build that in a structured, production-ready way.

The most important lesson is this: let the spreadsheet remain the source of truth, and let AI act as the interpretation layer.

That design pattern leads to better answers, safer behavior, and a much more useful chatbot experience.

What this project includes

  • Sidebar chatbot inside Google Sheets
  • Uses only selected sheet/tab data
  • Optional row citations in answers
  • Token-safe row limiting
  • Smart keyword filtering before Gemini
  • Settings panel
  • Chat history per user
  • Clear fallback when answer is not in the sheet
  • Safe prompt grounding
  • Ready to expand later into multi-sheet support

Project structure

Create these files in Apps Script:

  • Code.gs
  • Gemini.gs
  • SheetData.gs
  • Config.gs
  • index.html
  • styles.html
  • scripts.html

1) Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Sheet Chatbot')
    .addItem('Open Chatbot', 'showChatbotSidebar')
    .addSeparator()
    .addItem('Initialize Settings', 'initializeSettings')
    .addItem('Reset Chat History', 'resetChatHistory')
    .addToUi();
}function showChatbotSidebar() {
  const html = HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle('Sheet Chatbot');
  SpreadsheetApp.getUi().showSidebar(html);
}function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}function initializeSettings() {
  const props = PropertiesService.getDocumentProperties();
  const existing = getAppConfig_();  if (!existing.sheetName) {
    const firstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    props.setProperty('CHATBOT_SHEET_NAME', firstSheet.getName());
  }  if (!existing.maxRows) props.setProperty('CHATBOT_MAX_ROWS', '150');
  if (!existing.temperature) props.setProperty('CHATBOT_TEMPERATURE', '0.1');
  if (!existing.model) props.setProperty('CHATBOT_MODEL', 'gemini-3-flash-preview');
  if (!existing.historySize) props.setProperty('CHATBOT_HISTORY_SIZE', '12');
  if (!existing.allowRowCitations) props.setProperty('CHATBOT_ALLOW_ROW_CITATIONS', 'true');  SpreadsheetApp.getUi().alert('Chatbot settings initialized.');
}function getSidebarData() {
  return {
    config: getAppConfig_(),
    sheets: SpreadsheetApp.getActiveSpreadsheet().getSheets().map(s => s.getName()),
    history: getChatHistory_()
  };
}function saveSettings(settings) {
  const props = PropertiesService.getDocumentProperties();  props.setProperty('CHATBOT_SHEET_NAME', String(settings.sheetName || ''));
  props.setProperty('CHATBOT_MAX_ROWS', String(settings.maxRows || 150));
  props.setProperty('CHATBOT_TEMPERATURE', String(settings.temperature || 0.1));
  props.setProperty('CHATBOT_MODEL', String(settings.model || 'gemini-3-flash-preview'));
  props.setProperty('CHATBOT_HISTORY_SIZE', String(settings.historySize || 12));
  props.setProperty('CHATBOT_ALLOW_ROW_CITATIONS', String(!!settings.allowRowCitations));  return getSidebarData();
}function resetChatHistory() {
  PropertiesService.getUserProperties().deleteProperty('CHATBOT_HISTORY');
  return { success: true };
}function askSpreadsheetChatbot(question) {
  if (!question || !question.trim()) {
    return { ok: false, answer: 'Please enter a question.' };
  }  const config = getAppConfig_();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(config.sheetName);  if (!sheet) {
    return { ok: false, answer: `Configured sheet "${config.sheetName}" was not found.` };
  }  const sheetContext = buildRelevantContext_(sheet, question, config);
  if (!sheetContext.rows.length) {
    return {
      ok: true,
      answer: 'No usable data was found in the selected sheet.',
      meta: sheetContext.meta
    };
  }  const prompt = buildGroundedPrompt_(question, sheetContext, config);
  const modelResponse = generateGeminiText_(prompt, config);  appendChatHistory_({
    role: 'user',
    text: question,
    ts: new Date().toISOString()
  });  appendChatHistory_({
    role: 'assistant',
    text: modelResponse,
    ts: new Date().toISOString()
  });  return {
    ok: true,
    answer: modelResponse,
    meta: sheetContext.meta,
    history: getChatHistory_()
  };
}

2) Config.gs

function getAppConfig_() {
  const docProps = PropertiesService.getDocumentProperties();  return {
    sheetName: docProps.getProperty('CHATBOT_SHEET_NAME') || '',
    maxRows: Number(docProps.getProperty('CHATBOT_MAX_ROWS') || 150),
    temperature: Number(docProps.getProperty('CHATBOT_TEMPERATURE') || 0.1),
    model: docProps.getProperty('CHATBOT_MODEL') || 'gemini-3-flash-preview',
    historySize: Number(docProps.getProperty('CHATBOT_HISTORY_SIZE') || 12),
    allowRowCitations: String(docProps.getProperty('CHATBOT_ALLOW_ROW_CITATIONS') || 'true') === 'true'
  };
}function getGeminiApiKey_() {
  const key = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  if (!key) {
    throw new Error('Missing GEMINI_API_KEY in Script Properties.');
  }
  return key;
}function getChatHistory_() {
  const raw = PropertiesService.getUserProperties().getProperty('CHATBOT_HISTORY');
  if (!raw) return [];
  try {
    return JSON.parse(raw);
  } catch (err) {
    return [];
  }
}function appendChatHistory_(entry) {
  const config = getAppConfig_();
  const history = getChatHistory_();
  history.push(entry);  const maxEntries = Math.max(2, config.historySize * 2);
  const trimmed = history.slice(-maxEntries);  PropertiesService.getUserProperties().setProperty('CHATBOT_HISTORY', JSON.stringify(trimmed));
}

3) SheetData.gs

function buildRelevantContext_(sheet, question, config) {
  const values = sheet.getDataRange().getDisplayValues();
  const meta = {
    sheetName: sheet.getName(),
    totalRowsInSheet: Math.max(0, values.length - 1),
    rowsUsedForAnswer: 0
  };  if (!values || values.length < 2) {
    return {
      headers: [],
      rows: [],
      contextText: '',
      meta
    };
  }  const headers = values[0].map(h => String(h).trim());
  const dataRows = values.slice(1)
    .map((row, idx) => ({
      rowNumber: idx + 2,
      values: row,
      text: row.join(' | ')
    }))
    .filter(r => r.values.some(cell => String(cell).trim() !== ''));  const relevantRows = rankRowsByQuestion_(headers, dataRows, question)
    .slice(0, config.maxRows);  meta.rowsUsedForAnswer = relevantRows.length;  return {
    headers,
    rows: relevantRows,
    contextText: formatRowsAsContext_(headers, relevantRows),
    meta
  };
}function rankRowsByQuestion_(headers, dataRows, question) {
  const q = normalizeText_(question);
  const qTokens = tokenize_(q);  const headerBoostTokens = tokenize_(headers.join(' '));  const scored = dataRows.map(row => {
    const rowText = normalizeText_(headers.join(' ') + ' ' + row.values.join(' '));
    const rowTokens = tokenize_(rowText);    let score = 0;    qTokens.forEach(token => {
      if (rowText.includes(token)) score += 3;
      if (rowTokens.includes(token)) score += 2;
      if (headerBoostTokens.includes(token)) score += 1;
    });    if (score === 0 && qTokens.length <= 2) {
      qTokens.forEach(token => {
        row.values.forEach(cell => {
          if (normalizeText_(String(cell)).includes(token)) score += 1;
        });
      });
    }    return Object.assign({}, row, { score });
  });  const matched = scored.filter(r => r.score > 0)
    .sort((a, b) => b.score - a.score || a.rowNumber - b.rowNumber);  if (matched.length) return matched;  return scored.slice(0, Math.min(50, scored.length));
}function formatRowsAsContext_(headers, rows) {
  const lines = [];
  lines.push(`Columns: ${headers.join(' | ')}`);
  lines.push('Approved spreadsheet rows:');  rows.forEach(row => {
    const pairs = headers.map((header, i) => `${header}: ${row.values[i] || ''}`);
    lines.push(`Row ${row.rowNumber}: ${pairs.join(' ; ')}`);
  });  return lines.join('\n');
}function normalizeText_(text) {
  return String(text || '')
    .toLowerCase()
    .replace(/[^\w\s.-]/g, ' ')
    .replace(/\s+/g, ' ')
    .trim();
}function tokenize_(text) {
  return normalizeText_(text)
    .split(' ')
    .filter(Boolean)
    .filter(token => token.length > 1);
}function buildGroundedPrompt_(question, sheetContext, config) {
  const citationRule = config.allowRowCitations
    ? 'When possible, include spreadsheet row references like "Row 12".'
    : 'Do not include row references unless absolutely necessary.';  return `
You are a spreadsheet chatbot.Your job:
- Answer ONLY from the approved spreadsheet rows below.
- Never use outside knowledge.
- Never infer facts not directly supported by the sheet.
- If the answer is not in the sheet, reply exactly:
That information is not available in the sheet.
- Be concise, accurate, and businesslike.
- If the user asks for a count, count only from the approved rows.
- If the user asks for a list, list only values found in the sheet.
- If column names matter, use the provided headers exactly.
- ${citationRule}User question:
${question}Spreadsheet context:
${sheetContext.contextText}
  `.trim();
}

4) Gemini.gs

function generateGeminiText_(prompt, config) {
  const apiKey = getGeminiApiKey_();  const url = `https://generativelanguage.googleapis.com/v1beta/models/${encodeURIComponent(config.model)}:generateContent`;  const payload = {
    contents: [
      {
        parts: [
          { text: prompt }
        ]
      }
    ],
    generationConfig: {
      temperature: config.temperature,
      maxOutputTokens: 700
    }
  };  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'x-goog-api-key': apiKey
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });  const status = response.getResponseCode();
  const body = response.getContentText();  if (status < 200 || status >= 300) {
    throw new Error(`Gemini API error (${status}): ${body}`);
  }  const json = JSON.parse(body);  const text =
    json?.candidates?.[0]?.content?.parts
      ?.map(part => part.text || '')
      .join('')
      .trim();  if (!text) {
    return 'That information is not available in the sheet.';
  }  return text;
}

5) index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('styles'); ?>
  </head>
  <body>
    <div class="app">
      <div class="header">
        <h2>Sheet Chatbot</h2>
        <div class="sub">Grounded answers from one spreadsheet tab only</div>
      </div>      <div class="panel">
        <div class="panel-title">Ask a question</div>
        <textarea id="question" placeholder="Example: Which employees are active in Sales?"></textarea>
        <div class="actions">
          <button class="primary" onclick="askBot()">Ask</button>
          <button onclick="clearQuestion()">Clear</button>
        </div>
        <div id="status" class="status"></div>
      </div>      <div class="panel">
        <div class="panel-title">Answer</div>
        <div id="answer" class="answer">No answer yet.</div>
        <div id="meta" class="meta"></div>
      </div>      <div class="panel">
        <div class="panel-title">Settings</div>
        <label>Sheet</label>
        <select id="sheetName"></select>        <label>Model</label>
        <input id="model" type="text" placeholder="gemini-3-flash-preview">        <label>Max rows to send</label>
        <input id="maxRows" type="number" min="10" max="500">        <label>Temperature</label>
        <input id="temperature" type="number" min="0" max="1" step="0.1">        <label>Chat history size</label>
        <input id="historySize" type="number" min="2" max="50">        <label class="checkbox-row">
          <input id="allowRowCitations" type="checkbox">
          <span>Allow row citations</span>
        </label>        <div class="actions">
          <button onclick="saveSettings()">Save Settings</button>
          <button onclick="resetHistory()">Reset History</button>
        </div>
      </div>      <div class="panel">
        <div class="panel-title">Chat history</div>
        <div id="history" class="history"></div>
      </div>
    </div>    <?!= include('scripts'); ?>
  </body>
</html>

6) styles.html

<style>
  body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 12px;
    background: #f6f8fb;
    color: #1f2937;
  }  .app {
    display: flex;
    flex-direction: column;
    gap: 12px;
  }  .header h2 {
    margin: 0 0 4px 0;
    font-size: 20px;
  }  .sub {
    color: #6b7280;
    font-size: 12px;
  }  .panel {
    background: #fff;
    border: 1px solid #dbe3ef;
    border-radius: 10px;
    padding: 12px;
  }  .panel-title {
    font-weight: 700;
    margin-bottom: 10px;
  }  textarea,
  input,
  select {
    width: 100%;
    box-sizing: border-box;
    padding: 10px;
    margin: 6px 0 12px 0;
    border: 1px solid #cbd5e1;
    border-radius: 8px;
    font-size: 13px;
    background: #fff;
  }  textarea {
    min-height: 110px;
    resize: vertical;
  }  .actions {
    display: flex;
    gap: 8px;
    flex-wrap: wrap;
  }  button {
    border: none;
    border-radius: 8px;
    padding: 10px 14px;
    cursor: pointer;
    background: #e5e7eb;
    font-weight: 600;
  }  button.primary {
    background: #2563eb;
    color: #fff;
  }  .status {
    margin-top: 10px;
    font-size: 12px;
    color: #6b7280;
  }  .answer {
    white-space: pre-wrap;
    line-height: 1.45;
    font-size: 14px;
  }  .meta {
    margin-top: 10px;
    font-size: 12px;
    color: #6b7280;
  }  .history {
    display: flex;
    flex-direction: column;
    gap: 8px;
  }  .history-item {
    border: 1px solid #e5e7eb;
    border-radius: 8px;
    padding: 8px 10px;
    background: #fafafa;
  }  .history-role {
    font-size: 11px;
    font-weight: 700;
    color: #475569;
    text-transform: uppercase;
    margin-bottom: 4px;
  }  .history-text {
    white-space: pre-wrap;
    font-size: 13px;
    line-height: 1.4;
  }  .checkbox-row {
    display: flex;
    align-items: center;
    gap: 8px;
    margin-bottom: 12px;
  }  .checkbox-row input {
    width: auto;
    margin: 0;
  }
</style>

7) scripts.html

<script>
  function setStatus(msg) {
    document.getElementById('status').textContent = msg || '';
  }  function escapeHtml(text) {
    return String(text || '')
      .replace(/&/g, '&amp;')
      .replace(/</g, '&lt;')
      .replace(/>/g, '&gt;');
  }  function renderHistory(history) {
    const el = document.getElementById('history');
    if (!history || !history.length) {
      el.innerHTML = '<div class="history-item"><div class="history-text">No chat history yet.</div></div>';
      return;
    }    el.innerHTML = history.map(item => `
      <div class="history-item">
        <div class="history-role">${escapeHtml(item.role)}</div>
        <div class="history-text">${escapeHtml(item.text)}</div>
      </div>
    `).join('');
  }  function fillSettings(data) {
    const sheetSelect = document.getElementById('sheetName');
    sheetSelect.innerHTML = '';    data.sheets.forEach(name => {
      const option = document.createElement('option');
      option.value = name;
      option.textContent = name;
      if (name === data.config.sheetName) option.selected = true;
      sheetSelect.appendChild(option);
    });    document.getElementById('model').value = data.config.model || 'gemini-3-flash-preview';
    document.getElementById('maxRows').value = data.config.maxRows || 150;
    document.getElementById('temperature').value = data.config.temperature || 0.1;
    document.getElementById('historySize').value = data.config.historySize || 12;
    document.getElementById('allowRowCitations').checked = !!data.config.allowRowCitations;    renderHistory(data.history || []);
  }  function loadSidebar() {
    setStatus('Loading...');
    google.script.run
      .withSuccessHandler(data => {
        fillSettings(data);
        setStatus('Ready.');
      })
      .withFailureHandler(err => {
        setStatus('Error loading sidebar: ' + err.message);
      })
      .getSidebarData();
  }  function askBot() {
    const question = document.getElementById('question').value.trim();
    if (!question) {
      setStatus('Please enter a question.');
      return;
    }    document.getElementById('answer').textContent = 'Thinking...';
    document.getElementById('meta').textContent = '';
    setStatus('Sending request...');    google.script.run
      .withSuccessHandler(result => {
        document.getElementById('answer').textContent = result.answer || '';
        if (result.meta) {
          document.getElementById('meta').textContent =
            `Sheet: ${result.meta.sheetName} | Rows used: ${result.meta.rowsUsedForAnswer} | Total rows: ${result.meta.totalRowsInSheet}`;
        }
        renderHistory(result.history || []);
        setStatus('Done.');
      })
      .withFailureHandler(err => {
        document.getElementById('answer').textContent = 'Error: ' + err.message;
        setStatus('Request failed.');
      })
      .askSpreadsheetChatbot(question);
  }  function clearQuestion() {
    document.getElementById('question').value = '';
    setStatus('');
  }  function saveSettings() {
    const payload = {
      sheetName: document.getElementById('sheetName').value,
      model: document.getElementById('model').value.trim(),
      maxRows: Number(document.getElementById('maxRows').value || 150),
      temperature: Number(document.getElementById('temperature').value || 0.1),
      historySize: Number(document.getElementById('historySize').value || 12),
      allowRowCitations: document.getElementById('allowRowCitations').checked
    };    setStatus('Saving settings...');
    google.script.run
      .withSuccessHandler(data => {
        fillSettings(data);
        setStatus('Settings saved.');
      })
      .withFailureHandler(err => {
        setStatus('Error saving settings: ' + err.message);
      })
      .saveSettings(payload);
  }  function resetHistory() {
    setStatus('Resetting history...');
    google.script.run
      .withSuccessHandler(() => {
        renderHistory([]);
        setStatus('History reset.');
      })
      .withFailureHandler(err => {
        setStatus('Error resetting history: ' + err.message);
      })
      .resetChatHistory();
  }  document.addEventListener('DOMContentLoaded', loadSidebar);
</script>

Setup steps

1) Create the Apps Script project

Open your Google Sheet, then go to Extensions → Apps Script. Apps Script supports container-bound Sheets projects and custom sidebars through HTML Service.

2) Add all files above

Create the .gs and .html files exactly as listed.

3) Add your Gemini API key

In Apps Script:

  • open Project Settings
  • under Script Properties
  • add GEMINI_API_KEY

Google’s Gemini Apps Script quickstart explicitly shows storing GEMINI_API_KEY in Script Properties.

4) Initialize settings

Run initializeSettings() once from the Apps Script editor.

5) Reload the sheet

You’ll see a new menu:
Sheet Chatbot → Open Chatbot

How it works

The project does not send the entire spreadsheet blindly every time. It:

  1. reads the selected tab from Sheets,
  2. scores rows for relevance to the user’s question,
  3. sends only those approved rows to Gemini,
  4. instructs Gemini to answer only from that context,
  5. returns “That information is not available in the sheet.” when unsupported.

This is the right pattern because Apps Script works naturally with sheet data as 2D arrays, and Gemini generateContent is best used as the language layer, not as the source of truth.

Recommended spreadsheet format

Best results happen when your sheet has:

  • row 1 as headers
  • one record per row
  • clean values
  • no merged cells
  • no decorative rows above the headers

Example:

EmployeeDepartmentStatusStart DateLocation
AnnaHRActive2024-02-01Toronto
JohnSalesOn Leave2023-10-15Calgary

Good questions:

  • “Who is active in HR?”
  • “How many employees are in Sales?”
  • “What is John’s status?”
  • “List everyone in Toronto.”

Production notes

1) Model name

I set the default model to gemini-3-flash-preview because current Gemini docs show Gemini 3 family examples and recent model guidance. If your key/project uses a different available model, update the setting in the sidebar.

2) API endpoint

The code uses the Gemini REST generateContent endpoint with the API key sent in the x-goog-api-key header, which matches Google’s current official docs.

3) Storage

  • Script Properties hold the API key
  • Document Properties hold per-sheet chatbot settings
  • User Properties hold chat history

That separation matches Apps Script property scopes.

Best upgrades to add next

Here are the most useful improvements after this version:

Exact structured querying

Instead of letting Gemini do counting from row text, add deterministic Apps Script logic for:

  • count
  • lookup by exact field
  • list unique values
  • filter by column value

That makes answers even more reliable.

Multi-sheet routing

Allow Gemini to first choose which tab is relevant, then Apps Script fetches only that tab.

Column-aware filters

Map phrases like:

  • “active employees”
  • “department is sales”
  • “start date after January”

into exact column comparisons in Apps Script.

Audit log

Write each question and response to a hidden log sheet.

Access control

Restrict certain tabs from being queried.

Optional exact-count helper

Add this to SheetData.gs if you want a deterministic count utility:

function countRowsWhere_(sheetName, columnName, matchValue) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) throw new Error(`Sheet not found: ${sheetName}`);  const values = sheet.getDataRange().getDisplayValues();
  if (values.length < 2) return 0;  const headers = values[0];
  const colIndex = headers.indexOf(columnName);
  if (colIndex === -1) throw new Error(`Column not found: ${columnName}`);  return values.slice(1).filter(row =>
    String(row[colIndex]).trim().toLowerCase() === String(matchValue).trim().toLowerCase()
  ).length;
}

Optional hidden log sheet

Add this to Code.gs if you want every chat recorded:

function logChat_(question, answer, meta) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('_ChatbotLog');  if (!sheet) {
    sheet = ss.insertSheet('_ChatbotLog');
    sheet.appendRow(['Timestamp', 'User', 'Question', 'Answer', 'Sheet', 'Rows Used']);
  }  const email = Session.getActiveUser().getEmail() || 'Unknown';
  sheet.appendRow([
    new Date(),
    email,
    question,
    answer,
    meta?.sheetName || '',
    meta?.rowsUsedForAnswer || 0
  ]);
}

Then inside askSpreadsheetChatbot(question), after getting modelResponse, add:

logChat_(question, modelResponse, sheetContext.meta);