0

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();
}
1

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()]];
}
  • I used =copyNote(cell("address",Ref!B59)). It returns to the cell the note attached successfully, but without referencing what was in the cell. What I was looking for was a way to reference a cell range with data and notes still attached. Like having a master list that I can reference, but I want the notes to still transfer over retroactively. Hope that makes sense. I feel like setNote() is needed, I keep trying to add in it but I couldn't seem to make it work. – Frank Aug 29 '16 at 14:18
  • @Frank: Edited my answer. – Rubén Aug 29 '16 at 17:53
  • That didnt work – Frank Sep 1 '16 at 19:08
  • Did you get an error message? – Rubén Sep 2 '16 at 0:42
  • Not an error. Just that I meant it's not working the way I was intending to. With yours above, in one cell it displays "Value: " and in the adjacent cell to the right it displays "Note: " with the contents of each. It doesn't create a note. It seems like it just copy's a cell and splits the note and the value. – Frank Sep 3 '16 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.