Google Sheets – How to Search Notes Effectively

google sheetsgoogle-apps-scriptsearch

My spreadsheet has many notes added to its cells. I would like to find the cells containing a certain word, phrase or number. How to do this?

Best Answer

It appears that built-in search does not cover notes (or comments) added to spreadsheet. So I wrote a script that searches all notes within the active sheet. (It does not search comments, since scripts are currently unable to access comments at all.)

After entering the script (Tools > Script Editor), you will see a new menu item next time you open the spreadsheet: it will be Custom > Search notes.

There is no user interface; the search term is the content of the currently active cell, and the search results are placed in that cell, replacing the term. The search results are stated as "D5: content of the note" where D5 is the cell to which the note is attached.

One can search for empty text: the result will be the summary of all notes in the sheet, which may be useful.

The search is case-insensitive.

function searchNotes() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var searchTerm = cell.getValue().toString().toLowerCase();
  var dataRange = sheet.getDataRange();
  var notes = dataRange.getNotes();
  var results = [];
  for (var i = 0; i < notes.length; i++) {
    for (var j = 0; j < notes[0].length; j++) {
      var note = notes[i][j].toLowerCase();
      if (note && note.indexOf(searchTerm) !== -1) {
        results.push(dataRange.offset(i, j, 1, 1).getA1Notation() + ': ' + notes[i][j] + '\n');
      }
    }
  }
  cell.setValue(results.join('') || 'Not found');
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Search notes",  functionName: "searchNotes"}]);
}
Related Topic