google apps script - Get notes from a cell and insert into another cell

Question

I have a range of cells in Google Sheets, some of them have notes attached.

If there's a note attached to a cell, I need to put the note in a separate cell, and put the location of that note in another cell.

I found this script elsewhere:

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

but when I try to use it I get an error "Exception: Range not found (line 12)."

But this script only gets me halfway there as it only gets the note and puts it in a cell. I also need to know what cell the note came from.

Any help is greatly appreciated.

Answer 1

In the script above the function getNote() expects the paramter cell

If you just run the script without calling getNote() from another function / an environment where it gets a values for cell assigned, the script will fail wiht the error you obtained.

Indeed, this script doe snot not meet your needs. What you probably want is to screen all your cells for the one that have notes.

What you need to decide is into which cells you want to put the note and the cell notation.

Below is a sample that you need to adapt for your needs:

function getNotes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //if you have only one sheet in the spreadsheet, otherwise use ss.getSheetByName(name);
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();  
  var results = range.getNotes();  
  for (var i = 0; i < results.length; i++) {
    for (var j = 0; j < results[0].length; j++) {
      //if a not empty note was found:
      if(results[i][j]){
        var note = results[i][j];
        var cell = range.getCell(i+1, j+1);
        var notation = cell.getA1Notation();
        //adjust the offset as function of the column / row where you want to output the results
        cell.offset(0, 1).setValue(note);
        cell.offset(0, 2).setValue(notation);
      }
    }
  }
}

Important references:

• getNotes()
• getA1Notation()
• getDataRange()
• getCell(row, column)
• offset(rowOffset, columnOffset)