8

Is there a way to get cell note value and display the all the note in the cell next to it?

I have a column C where some of the cells contain note. I would like to get these note value and write each cell note in the cell next to it in column D.

For example: if cell C4 has a note "No entry", I want to display "No entry" in D4.

Thanks.

Alex

Rubén
  • 29,320
  • 9
  • 61
  • 145
Alex
  • 253
  • 1
  • 3
  • 10

1 Answers1

15

I needed to do this today and found your question but no answer. This is what I finally came up with. You can do this by setting up a function in the script editor named getNote.

function getNote(cell)
{
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange(cell)
   return range.getNote();
}

then in your spreadsheet enter:

=getNote("C4")

to get the note from cell C4. If you want it to be a reference that changes when you move around the cells, then reference it this way:

=getNote(Address(row(C4), column(C4)))

Word of caution: Notes don't trigger a refresh, so if a note is edited the cell does not pick up the new value right away

Rubén
  • 29,320
  • 9
  • 61
  • 145
JOMan
  • 271
  • 3
  • 9
  • 1
    Word of caution: Notes don't trigger a refresh, so if a note is edited the cell does not pick up the new value right away. – JOMan Sep 20 '13 at 22:10
  • This didn't work for me. Whether I specify the cell address as a string or using `Address()` doesn't matter, the function appears to return nothing. I temporarily altered the function a little (`return "fubar"`) to ensure it is executing. (It does.) Will it make a difference if the spreadsheet from which the function is called has multiple sheets? – Mr. Lance E Sloan Jul 25 '17 at 18:07
  • 1
    for those interested, there is also a `getNotes` if you're working on a range of cells – Jona Jul 05 '19 at 15:40
  • If used on a mouse selected cell, the input syntax is `=getNote(C4)` without the quotes. Is it possible to adapt the script to work on that too? – Sandburg Sep 17 '21 at 09:55
  • This worked for me when i added the quotes around the cell number. =getNote("H35") – TharsDimitris Nov 13 '21 at 07:05