Get Sheet cell note value with Google Apps Script

Question

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

I have a column C where some of the cells contain notes. I would like to get these note values 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.

Eno

Answer 1

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

Coalition

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. – Coalition

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? – Hutto

for those interested, there is also a getNotes if you're working on a range of cells – Mold

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? – Tadzhik

This worked for me when i added the quotes around the cell number. =getNote("H35") – Stereopticon

Answer 2

You can adjust the script to fetch a range instead of a singular cell. In the script, substitutecell with range. Then in the sheet you can specify the range with formula =getnotes("J:J"). This will return the entire range of cells that contain notes.

Binturong