Create a Custom Search Interface Within Your Google Sheet Using Apps Script

Have you ever wished you could add a built-in search bar to your Google Sheet that filters results like a pro? With Google Apps Script, you can build a custom search interface directly inside your spreadsheet—perfect for beginners who want to improve how they interact with their data without learning complex formulas or filters.

This is especially helpful when dealing with large datasets, customer lists, product catalogs, or student records. Instead of scrolling endlessly or manually applying filters, users can type in a keyword and instantly see matching results.

In this tutorial, you’ll learn how to use a simple custom menu and dialog box built with HTML to create a powerful search feature—all using Google Apps Script.

Real-Life Example: Search for a Student by Name

Let’s say you’re a teacher managing class records in Google Sheets. You want to allow other teachers or assistants to easily look up a student by name and view their details—without messing up the main data.

Sample Data Table

Student ID	Name	Class	Grade
1001	Alice Smith	10-A	A
1002	Bob Johnson	10-B	B+
1003	Carla Lee	10-A	A-

How to Build a Search Interface in Google Sheets

Step 1: Set Up Your Google Sheet

Name your sheet: Student Records
Enter data as shown above

Step 2: Open the Script Editor

Go to Extensions → Apps Script
Delete any placeholder code

Step 3: Add This Code to Your Script Editor


// Create custom menu
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("🔍 Custom Search")
    .addItem("Search Student", "showSearchBox")
    .addToUi();
}

// Show HTML form as dialog
function showSearchBox() {
  const html = HtmlService.createHtmlOutputFromFile('SearchForm')
    .setWidth(300)
    .setHeight(150);
  SpreadsheetApp.getUi().showModalDialog(html, 'Search Student');
}

// Perform search and return results
function searchStudent(name) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Student Records");
  const data = sheet.getDataRange().getValues();
  let results = [];

  for (let i = 1; i < data.length; i++) {
    if (data[i][1].toLowerCase().includes(name.toLowerCase())) {
      results.push(data[i]);
    }
  }

  return results;
}

Step 4: Add a New HTML File

Click the + icon → choose HTML → name it SearchForm
Paste the following HTML:


<!DOCTYPE html>
<html>
  <body>
    <label>Enter student name:</label>
    <input type="text" id="name" /><br><br>
    <button onclick="search()">Search</button>
    <div id="results" style="margin-top:10px;"></div>

    <script>
      function search() {
        const name = document.getElementById("name").value;
        google.script.run.withSuccessHandler(showResults).searchStudent(name);
      }

      function showResults(data) {
        let html = '';
        if (data.length === 0) {
          html = "No results found.";
        } else {
          html = "<table border='1' cellpadding='5'><tr><th>ID</th><th>Name</th><th>Class</th><th>Grade</th></tr>";
          data.forEach(row => {
            html += "<tr>" + row.map(cell => `<td>${cell}</td>`).join("") + "</tr>";
          });
          html += "</table>";
        }
        document.getElementById("results").innerHTML = html;
      }
    </script>
  </body>
</html>

Step 5: Save and Reload

Click the disk icon to save
Close and reopen the Sheet (or run onOpen() manually)
Click 🔍 Custom Search → Search Student

Key Benefits

User-Friendly: Create a search box instead of asking users to filter or use formulas
Real-Time Search: Instantly return results from your spreadsheet
Works on Any Sheet: You can adjust the code for employee records, product databases, etc.

Pro Tips

Add dropdown filters (e.g., by Class or Grade) to make it more advanced
Include a “Clear Results” button in the dialog for better UX
Use .toLowerCase() to make the search case-insensitive

Quick-Reference Cheat Sheet

Function / Term	Definition
`HtmlService`	Lets you create custom HTML dialogs or sidebars in Google Sheets
`Modal Dialog`	A popup box that stays on screen until closed
`onOpen()`	Runs automatically when the sheet is opened
`google.script.run`	Allows HTML to talk to your Apps Script code

With just a bit of Apps Script and HTML, you can turn any Google Sheet into an interactive tool that your users will love. Whether you’re managing students, clients, inventory, or contacts, a custom search box helps you find what you need—fast and stress-free.

Try it on your next spreadsheet project and impress your coworkers with a slick search feature right inside your sheet!