How to search notes in Google Sheets?

by if....   Last Updated January 08, 2018 08:03 AM

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?



Answers 2


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"}]);
}
if....
if....
November 21, 2016 18:03 PM

I have created a payment workflow using google sheets and forms. The google sheet auto-populates the approval status, however, the timestamp (time of approval) gets recorded as a note.

Is it possible to copy all the notes from various cells (within the same column) and paste it onto a different column? This will help in validating the time taken for approval processing, etc.

Thanks in advance!

Regards, Sreejith

Sreejith
Sreejith
January 08, 2018 07:39 AM

Related Questions


how to search for just phones, not accessories

Updated April 03, 2017 01:03 AM

Multiple conversions in one cell

Updated April 27, 2018 09:03 AM

Can the Hiba property sold

Updated January 12, 2018 22:03 PM