Is there a way to search backward in Google Spreadsheets?

Question

When I click CTRL+H then I can open big search box in Google Spreadsheets. It searches forward for specific text all sheets from the position of my cursor. But is there a way to search backward?

I want to find last occurrence of specific text in previous sheets. Maybe is there some kind of formula I could use, since it seems default search box doesn't have function to search backwards.

Answer 1

The Edit > Find and replace dialog box (Control+Shift+H) only searches forward, but the quick find dialog box (Control+F) lets you search in both directions. Press Control+G to go forward and Control+Shift+G to go backward.

The Control+G shortcut is not listed in the keyboard shortcuts help page, but it is fairly common in all kinds of office software.

This method of searching backward only works in the current sheet. To search across all sheets, dedicate a cell such as A1 on every sheet as a search box. You can make sure that the search box cell is always visible by using View > Freeze > 1 row and View > Freeze > 1 column.

Then add a script that runs on a simple trigger, searches for a text string or regular expression, and activates cells that match that string across the spreadsheet.

Here's one way to do that:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.');
  }
  quickFind_(e);
}

/**
* Activates cells that match the regular expression entered in a magic cell.
*
* @param {Object} e The onEdit() event object.
*/
function quickFind_(e) {
  // version 1.1, written by --Hyde, 13 March 2023
  //  - activate cells in multiple sheets
  //  - see https://webapps.stackexchange.com/a/169771/269219
  // version 1.0, written by --Hyde, 16 May 2021
  //  - see https://stackoverflow.com/a/67555531/13045193

  // specify the sheets where the function will be active
  // use /./i to make the function work in all sheets
  // use /^(Sheet1|Sheet2|Sheet3)$/i to make the function work only those three sheets
  const sheetsToWatch = /./i;
  // specify the location of the magic cell where you enter search strings or regexes
  const magicCellA1 = 'A1';

  let activeSheet;
  let activeSheetName;
  if (!e.value
    || e.range.getA1Notation() !== magicCellA1
    || !(activeSheetName = (activeSheet = e.range.getSheet()).getName()).match(sheetsToWatch)) {
    return;
  }
  e.source.toast(`Searching the spreadsheet for '${e.value}'...`);
  let searchFor;
  try {
    searchFor = new RegExp(e.value, 'i');
  } catch (error) {
    searchFor = e.value.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  }
  const magicCellR1C1 = `R${e.range.rowStart}C${e.range.columnStart}`;
  let numMatches = 0;
  let activeSheetMatches = [];
  e.source.getSheets().forEach(sheet => {
    let sheetName;
    if (!(sheetName = sheet.getName()).match(sheetsToWatch)) {
      return;
    }
    const data = sheet.getDataRange().getDisplayValues();
    const matches = [];
    data
      .forEach((row, rowIndex) => row
        .forEach((value, columnIndex) => {
          if (value.match(searchFor)) {
            const cellR1C1 = `R${rowIndex + 1}C${columnIndex + 1}`;
            if (cellR1C1 !== magicCellR1C1) {
              matches.push(cellR1C1);
              if (sheetName === activeSheetName) {
                activeSheetMatches.push(cellR1C1);
              }
            }
          }
        }));
    if (matches.length) {
      sheet.getRangeList(matches).activate();
      numMatches += matches.length;
    }
    SpreadsheetApp.flush();
  });
  if (activeSheetMatches.length) {
    activeSheet.getRangeList(activeSheetMatches).activate();
    SpreadsheetApp.flush();
  }
  e.source.toast(`Done. Found ${numMatches} matches for '${e.value}'.`, 'Search complete', 10);
}

Type the search string or regular expression in cell A1. The script will activate cells that match that string. To jump from one matching cell to another, press Tab to advance and Shift+Tab to back up. Switch to another tab to see the matches there.