4

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?

5

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"}]);
}
0

Inspired from the accepted answer, here is the version if you want to find in notes in all sheets:

function searchNotes() {

  var allSheets = SpreadsheetApp.getActive().getSheets();

  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var searchTerm = cell.getValue().toString().toLowerCase();
  var results = [];

  for(var s in allSheets){
    var dataRange = allSheets[s].getDataRange();
    var notes = dataRange.getNotes();
    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(allSheets[s].getName() + ': ' + dataRange.offset(i, j, 1, 1).getA1Notation() + ': ' + notes[i][j] + '\n\n');
        }
      }
    }
  }
  cell.setValue(results.join('') || 'Not found');
}

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy