How to Search Text Across All Sheets in a Google Spreadsheet Using Google Apps Script

You can search for text in a spreadsheet using shortcut keys like Ctrl + F or Ctrl + H, but when dealing with multiple sheets or large amounts of data, manual searching can be time-consuming.

If you want to quickly identify which sheet and cell contain the relevant data, using Google Apps Script can significantly improve efficiency.

In this article, we’ll introduce a method to search text across multiple sheets at once using Google Apps Script.

With this script, you can streamline your workflow and quickly access the information you need.

Example Output

Display a list of sheet names and cell locations containing the search keyword.

Here’s an example of a text search using the keyword “Tokyo”.

The script searches for cells containing the keyword “Tokyo” across multiple sheets within a file and compiles the results into a list.

• Sheet Name
• Cell Location
• Cell Content

The search results are displayed on a newly created sheet, generated by Google Apps Script.

This script allows you to efficiently search and display keywords scattered across multiple sheets.

Additionally, by clicking on the cell location link, you can jump directly to the corresponding cell.

Steps

STEP

Open Your Google sheets

Start by opening the Google Sheets file you want to search.

To search across all sheets in the file, the position of the displayed sheet tabs doesn’t matter.

STEP

Open Apps Script Editor

Open a Google Spreadsheet and click on “Extensions” in the menu, then “Apps Script” to open the Apps Script editor.

STEP

Create the Script

Since the default function myFunction() {} is present, delete it and replace it with the script below.

function searchAcrossSheets() {
  // Prompt the user to input the keyword to search for
  var keyword = Browser.inputBox("Enter the keyword to search for");
  
  // Get the active spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Create a new sheet to store the search results
  var resultSheet = spreadsheet.getSheetByName("Search Results");
  if (resultSheet) {
    spreadsheet.deleteSheet(resultSheet);
  }
  resultSheet = spreadsheet.insertSheet("Search Results");
  resultSheet.appendRow(["Sheet Name", "Cell", "Value"]);
  
  // Style the header row
  var headerRange = resultSheet.getRange(1, 1, 1, 3);
  headerRange.setHorizontalAlignment("center").setBackground("#b6d7a8");
  
  // Center-align the entire B column
  resultSheet.getRange(1, 2, resultSheet.getMaxRows()).setHorizontalAlignment("center");
  
  // Adjust column widths
  resultSheet.setColumnWidth(1, 150); // Sheet Name
  resultSheet.setColumnWidth(2, 100); // Cell
  resultSheet.setColumnWidth(3, 300); // Value
  
  // Get all sheets in the spreadsheet
  var sheets = spreadsheet.getSheets();
  
  // Loop through each sheet to search for the keyword
  sheets.forEach(function(sheet) {
    if (sheet.getName() === "Search Results") return; // Skip the results sheet
    
    var range = sheet.getDataRange();
    var values = range.getValues();
    
    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[i].length; j++) {
        if (values[i][j].toString().indexOf(keyword) !== -1) {
          // If the keyword is found, add the result to the results sheet
          var cell = range.getCell(i + 1, j + 1);
          var cellLink = '=HYPERLINK("' + spreadsheet.getUrl() + '#gid=' + sheet.getSheetId() + '&range=' + cell.getA1Notation() + '", "' + cell.getA1Notation() + '")';
          resultSheet.appendRow([sheet.getName(), cellLink, values[i][j]]);
        }
      }
    }
  });
  
  Browser.msgBox("Search completed. Results are displayed in the 'Search Results' sheet.");
}

Script Description

Set the Keyword to Search

  // Prompt the user to input the keyword to search for
  var keyword = Browser.inputBox("Enter the keyword to search for");

• The script uses Browser.inputBox to prompt the user to enter the keyword they want to search for.

Creating the Search Results Sheet

  // Get the active spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Create a new sheet to store the search results
  var resultSheet = spreadsheet.getSheetByName("Search Results");
  if (resultSheet) {
    spreadsheet.deleteSheet(resultSheet);
  }
  resultSheet = spreadsheet.insertSheet("Search Results");

• If a sheet named “Search Results” already exists, it will be deleted, and a new “Search Results” sheet will be created.

Styling the Header

  resultSheet.appendRow(["Sheet Name", "Cell", "Value"]);
  
  // Style the header row
  var headerRange = resultSheet.getRange(1, 1, 1, 3);
  headerRange.setHorizontalAlignment("center").setBackground("#b6d7a8");

• The first row is populated with the headers: “Sheet Name,” “Cell,” and “Value.”
• The headers are center-aligned, and the background color is set to light green (#b6d7a8).

Centering and Adjusting Column Widths

  // Center-align the entire B column
  resultSheet.getRange(1, 2, resultSheet.getMaxRows()).setHorizontalAlignment("center");
  
  // Adjust column widths
  resultSheet.setColumnWidth(1, 150); // Sheet Name
  resultSheet.setColumnWidth(2, 100); // Cell
  resultSheet.setColumnWidth(3, 300); // Value

• The entire column B is center-aligned, and the widths of each column are adjusted for better readability.

Retrieve and Search All Sheets

  // Get all sheets in the spreadsheet
  var sheets = spreadsheet.getSheets();
  
  // Loop through each sheet to search for the keyword
  sheets.forEach(function(sheet) {
    if (sheet.getName() === "Search Results") return; // Skip the results sheet

• The script retrieves all sheets in the current spreadsheet and loops through each sheet, excluding the “Search Results” sheet, to perform the search.

Checking for the Search Keyword

    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[i].length; j++) {
        if (values[i][j].toString().indexOf(keyword) !== -1) {
          // If the keyword is found, add the result to the results sheet
          var cell = range.getCell(i + 1, j + 1);
          var cellLink = '=HYPERLINK("' + spreadsheet.getUrl() + '#gid=' + sheet.getSheetId() + '&range=' + cell.getA1Notation() + '", "' + cell.getA1Notation() + '")';
          resultSheet.appendRow([sheet.getName(), cellLink, values[i][j]]);
        }
      }
    }
  });

• Each cell value is compared against the keyword. If a matching value is found, the cell’s information is added to the “Search Results” sheet.
• The search results include a hyperlink to the cell, allowing users to directly access the corresponding cell in the original sheet by clicking the link.

Search Completion Notification

  Browser.msgBox("Search completed. Results are displayed in the 'Search Results' sheet.");
}

• Finally, a completion message is displayed using Browser.msgBox to notify the user that the search has finished.

STEP

Save the Script

Once the script is written, save it with an appropriate name.

(Example: “Text Search”)

STEP

Run the Script

Execute the script to perform the text search.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”

STEP

Enter a Keyword in the Input Box

When the script is executed, an input box will appear.

Enter the keyword you want to search for in the box.

Example: Tokyo

STEP

Search Results Are Output to a Sheet

Once the script finishes running, the search results will be displayed.

• Column A: Sheet Name
• Column B: Matching Cell
• Column C: Cell Content

Clicking a cell in Column B will take you directly to the corresponding cell in the original sheet.

Using Shortcut Keys for Searching

Google Sheets provides a “Find and Replace” feature accessible with the shortcut key Ctrl + H.

With this feature, you can search for specific text across all sheets in a file and optionally replace it as needed.

While Ctrl + F limits the search to the current sheet, Ctrl + H allows you to search and replace text across all sheets, making it a more comprehensive tool for file-wide operations.

For simple searches or replacements, using these shortcut keys can be a quick and convenient option.

Conclusion

Using Google Apps Script for searching in Google Sheets offers significant advantages, especially when managing multiple sheets.

While standard text search tools allow you to search across sheets, Google Apps Script takes it a step further by compiling results into a clear and organized list, making it much easier to locate what you need.

Additionally, with some script enhancements, you can even perform text replacements based on the search results, further extending its functionality.

Our company offers support for improving work efficiency through the use of Google Apps Script.
If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us.
We are fully committed to supporting your business improvements.

Contact us here