Apps Script and Gemini Powered Google Sheets Tools

by

📘 Lesson 6 — Gemini-Powered Google Sheets Tools (Menus, Selections & AI Output)

Blog Post #6 in the Apps Script + APIs + Gemini Series

Calling Gemini is powerful.
But calling Gemini inside Google Sheets is transformative.

In this lesson, you’ll learn how to turn Gemini into a native Sheets assistant—able to explain data, summarize selections, and write AI output directly into cells.

This is the same pattern used for:

  • AI tutors
  • Data explainers
  • Business analysis helpers
  • Learning tools
  • Internal productivity add-ons

🎯 What You’ll Build

By the end of this lesson, you’ll have:

✅ A custom menu in Google Sheets
✅ The ability to read the user’s selected range
✅ A Gemini prompt built from sheet data
✅ AI output written back into the sheet
✅ A reusable pattern for many AI tools

🧠 Key Concepts Introduced

  • onOpen() custom menus
  • Reading selected ranges
  • Converting spreadsheet data into prompts
  • Writing Gemini output into cells
  • UX patterns for AI in Sheets

📍 Step 1 — Add a Custom Menu

This menu makes your AI tool feel native.

/**
 * Adds a custom AI menu to Google Sheets.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI Tools')
    .addItem('Explain Selection with Gemini', 'explainSelectionWithGemini')
    .addToUi();
}

📌 Reload the Sheet to see AI Tools → Explain Selection with Gemini

📍 Step 2 — Read the User’s Selected Range

/**
 * Gets the active range and converts it to text.
 */
function getSelectedRangeAsText() {
  var range = SpreadsheetApp.getActiveRange();

  if (!range) {
    throw new Error('No range selected.');
  }

  var values = range.getValues();

  return values
    .map(function(row) {
      return row.join(' | ');
    })
    .join('\n');
}

🔍 Why This Matters

Gemini doesn’t understand spreadsheets.
It understands text.

So we convert:
📊 cells → 🧾 structured text

📍 Step 3 — Build the Gemini Prompt

/**
 * Builds a clear prompt for Gemini using sheet data.
 */
function buildSheetExplanationPrompt(sheetText) {
  return (
    'Explain the following spreadsheet data in simple terms:\n\n' +
    sheetText +
    '\n\nProvide a short summary and key insights.'
  );
}

This keeps prompts:

  • Explicit
  • Repeatable
  • Predictable

📍 Step 4 — Call Gemini & Write Output to the Sheet

/**
 * Main entry point from the custom menu.
 */
function explainSelectionWithGemini() {
  try {
    var sheetText = getSelectedRangeAsText();
    var prompt = buildSheetExplanationPrompt(sheetText);

    var aiResponse = callGemini(prompt);

    writeOutputBelowSelection(aiResponse);

  } catch (err) {
    SpreadsheetApp.getUi().alert(err.message);
  }
}

📍 Step 5 — Write AI Output Below the Selection

/**
 * Writes AI output below the selected range.
 */
function writeOutputBelowSelection(text) {
  var range = SpreadsheetApp.getActiveRange();
  var sheet = range.getSheet();

  var startRow = range.getLastRow() + 1;
  var startCol = range.getColumn();

  sheet
    .getRange(startRow, startCol)
    .setValue('Gemini Explanation:');

  sheet
    .getRange(startRow + 1, startCol)
    .setValue(text);
}

🧪 Try It Out

  1. Select a range of cells (data, notes, numbers, anything)
  2. Click AI Tools → Explain Selection with Gemini
  3. Watch Gemini summarize your data below the selection

🎉 You’ve built an AI-powered Sheets tool.

🧪 Exercises (Highly Recommended)

Exercise 1 — Change the Prompt Style

Modify the prompt to:

  • Bullet points
  • Beginner explanation
  • Business executive summary

Exercise 2 — Write to a New Sheet

Instead of writing below the selection:

  • Create a sheet named AI Output
  • Append Gemini responses there

Exercise 3 — Add Confirmation Dialog

Ask users:

“Send selected data to Gemini?”

This builds trust and transparency.

💡 Pro Tips for AI in Sheets

Tip 1 — Always Show Where AI Writes

Never overwrite user data silently.

Tip 2 — Keep Prompts Deterministic

Consistent prompts = predictable results.

Tip 3 — This Pattern Is Reusable

You can now build:

  • “Explain Formula”
  • “Summarize Survey”
  • “Generate Insights”
  • “Suggest Improvements”

All with small prompt changes.

🔮 What’s Next?

In Lesson 7, you’ll bring Gemini into Google Docs:

Lesson 7 — Gemini-Powered Google Docs (Summaries, Rewrites & Outlines)

You’ll learn how to:

  • Read document content
  • Send it to Gemini
  • Insert AI output directly into Docs
  • Build AI writing assistants

➡️ Say “Create Lesson 7” when you’re ready.