Google-sheets – How to reference a cell with notes still attached in a Google Spreadsheet

google sheetsgoogle-apps-script

I'm simply looking for how to link one cell's data with the notes still attached into another cell and/or sheet.

With my code I cant't seem to figure out how to reference a single cell from another sheet with the notes still attached. This is the best code I have about it. I get error that range is not found. I'm using =copyNote(Ref!B59)

function copyNote(cell) {
  return SpreadsheetApp.getActiveSheet().getRange(cell).getNote();
}

Best Answer

Short answer

It's not possible to use a custom function to make that a cell shows the referenced cell value and note. Instead use function to copy the cell.

Explanation

Custom functions can only return values, they can't modify cell attributes like cell notes.

References in custom functions

Instead of Ref!B59 use "Ref!59" or cell("address",Ref!B59). The final formula will be =copyNote("Ref!B59") or =copyNote(cell("address",Ref!B59))

The above will return the value of the note from Ref!B59.

A more elaborated approach is to use getFormula() and regular expressions to extract the reference from the formula.

Workarounds

Using a script to copy a cells

The following function will copy a fixed cell to another fixed cell including. This will include notes.

function copyCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var origin = ss.getRange('A1');
  var destination = ss.getRange('F1');
  origin.copyTo(destination);
}

Using custom functions

In case that a custom function should be used, to get the value and the note from a cell reference, some changes should be done to the function. The following code returns a 1X2 array, the first resulting cell will contain the referred cell value and the second resulting cell the referred cell note:

function copyValueNote(cell) {
  var rng = SpreadsheetApp.getActiveSheet().getRange(cell);  
  return [['Value: ' + rng.getValue(),'Note: ' + rng.getNote()]];
}

Code

function autoAddRows() {
  var sh = SpreadsheetApp.getActiveSheet();
  var cRow = sh.getActiveRange().getRowIndex(), mRow = sh.getMaxRows();

  if((mRow-cRow) < 1) {
    var lCol = sh.getLastColumn(), key = sh.getRange(mRow,lCol).getValue();
    sh.insertRowAfter(mRow), sh.getRange(mRow+1,lCol).setValue(key+1);
  }
}

Remarks

var ss isn't necessary, since you don't use it in the script.
you need the row of the active cell, compared to the last row (getMaxRows)
add all the goodies into the IF statement that need to be executed, when mRow-cRow=0

Example

I've prepared a file for you to experiment with: Inserting Rows + Reference

Google-sheets – How to link two sheets so that entering a numerical value in one cell of Sheet 1 produces an ‘x’ in the same cell of Sheet 2

From inside the Google spreadsheet, go to Tools -> Script editor then try this:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 1) { //checks the column
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var XSheet = ss.getSheetByName("Sheet2");
     ss.setActiveSheet(XSheet);
     SpreadsheetApp.getActiveSheet().getRange(r.getRow(), r.getColumn()).setValue("X");
     var MainSheet = ss.getSheetByName("Sheet1");
     ss.setActiveSheet(MainSheet);   };
 };
}

This assumes your sheets are named Sheet1 and Sheet2, as is the default, and it's set to only watch column E (5) on Sheet1 for edits and only add an "X" to the corresponding cell in Sheet2.