Build a multi-sheet search-and-jump tool

Northwind’s master workbook has grown past twenty tabs — clients, invoices, suppliers, project logs, archived years. The built-in Find and replace dialog will search them all, but it only steps through matches one at a time, and there is no way to see every hit at once or share a link to one.

This tool turns search into a list. Type a term, and it scans every tab, collects each cell that contains it, and writes the results to a Search tab — sheet name, cell reference, the value, and a clickable link that jumps straight to the cell. It is the in-house equivalent of “search the whole spreadsheet” for a workbook that has got too big to navigate by hand.

What you’ll need

• A Google Sheet with several tabs — anything from a handful up.
• Nothing else. The script is bound to the spreadsheet and creates the Search tab itself.

The script

// Name of the tab the results are written to. It is skipped when
// scanning so the tool never finds its own output.
const RESULTS_SHEET = 'Search';

/**
 * Scans every tab for cells containing the query and writes the
 * matches to the results tab, each with a link back to the cell.
 *
 * @param {string} query - The text to search for (case-insensitive).
 */
function searchEverywhere(query) {
  const ss = SpreadsheetApp.getActive();

  // Bail out on an empty search rather than listing every cell.
  if (!query) {
    SpreadsheetApp.getUi().alert('Enter a search term.');
    return;
  }

  const needle = query.toLowerCase();
  const results = [];

  // 1. Walk every tab except the results tab itself.
  for (const sheet of ss.getSheets()) {
    if (sheet.getName() === RESULTS_SHEET) continue;

    // 2. Read the whole tab in one call, then scan cell by cell.
    const data = sheet.getDataRange().getValues();
    for (let r = 0; r < data.length; r++) {
      for (let c = 0; c < data[r].length; c++) {
        const value = data[r][c];
        if (String(value).toLowerCase().includes(needle)) {
          // 3. Build an A1 reference and a HYPERLINK that jumps to it.
          const a1 = sheet.getRange(r + 1, c + 1).getA1Notation();
          const link =
            `=HYPERLINK("#gid=${sheet.getSheetId()}&range=${a1}", "open")`;
          results.push([sheet.getName(), a1, link, value]);
        }
      }
    }
  }

  // 4. Rebuild the results tab from scratch on every search.
  const out = ss.getSheetByName(RESULTS_SHEET) || ss.insertSheet(RESULTS_SHEET);
  out.clear();
  out.getRange(1, 1, 1, 4)
    .setValues([['Sheet', 'Cell', 'Link', 'Value']]);
  if (results.length) {
    out.getRange(2, 1, results.length, 4).setValues(results);
  }

  // 5. Surface the result count and bring the tab forward.
  ss.setActiveSheet(out);
  SpreadsheetApp.getActive().toast(`${results.length} match(es) found.`);
}

/**
 * Prompts for a search term and runs the search.
 */
function searchPrompt() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Search', 'Term:', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === ui.Button.OK) {
    searchEverywhere(response.getResponseText());
  }
}

Add a menu

/**
 * Adds a "Search" menu when the spreadsheet opens.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Search')
    .addItem('Find…', 'searchPrompt')
    .addToUi();
}

How it works

1. searchEverywhere loops over every tab returned by getSheets(), skipping RESULTS_SHEET so the tool never matches its own output from a previous run.
2. Each tab is read once with getDataRange().getValues(), then scanned cell by cell. Reading the whole grid in one call keeps the scan fast even on big tabs.
3. When a cell’s text contains the query, the script converts the row and column to an A1 reference and builds a HYPERLINK formula. The #gid=...&range=... fragment is the URL that selects that exact cell.
4. The Search tab is cleared and rewritten on every run, with a header row and one row per match — sheet name, cell, the jump link, and the value.
5. A toast reports how many matches were found and the script switches to the results tab so you see them straight away.

Example run

Searching for acme across the workbook produces a Search tab like this:

Sheet	Cell	Link	Value
Clients	B14	open	Acme Studios
Invoices	C202	open	INV-Acme-0461
Project log	A7	open	Acme rebrand

Clicking open on the Invoices row jumps the workbook straight to cell C202 on the Invoices tab — no scrolling through twenty tabs to find it.

Run it

The search runs on demand from the menu:

1. Reload the spreadsheet so the onOpen menu appears.
2. Choose Search > Find…, type a term, and click OK.
3. Read the Search tab and click any open link to jump to the cell.

Watch out for

• The search is a plain substring match — cat matches category. There is no whole-word or regex option; tighten the term if you get too many hits.
• It only scans the used range of each tab. Values produced by formulas are searched on their result, not the formula text.
• The jump links rely on the #gid URL fragment. They work in the desktop browser but may not behave the same in the Sheets mobile app.
• On a very large workbook the cell-by-cell scan can take a while and may push toward the six-minute execution limit. If that happens, restrict the loop to the tabs you actually search.
• Results are a snapshot. Edit the workbook after a search and the Search tab is stale until you run Find… again.