Is there a way to dynamically reference cells in scripts for google sheets via script editor to create a note on another cell?

Question

I want to be able to quickly and easily add notes (Insert menu / Note) on multiple Google Sheets cells using cell values on other columns on that sheet. For example, F3 would grab the cell values in X3, F4 from X4. But also, if I grab G3, it would grab the values in Y3 (so it consistently grabs the values on the same row, but always the same number of columns to the right (in this case, always 18 columns to the right). I want to grab those values and stick them into a note on the left (so, columns F, G, etc.). I would like to be able to highlight a range (F3-F10), run the Google script, and it will grab all values on X3-X10 and stick them on their relative rows. Is this possible?

I've been hunting for this option, searching for every range of terms I can think of, and I've got pretty close, but I keep getting stuck at numerous points, the most important being able to grab the data from an equal number of columns to the right, relative to the range I highlight. I found this code that gets me close, but I don't know how to edit it to do what I need. It allows me to create the menu option, which works on pulling the value of A1 on the first tab of my spreadsheet (I'm working on the second tab, and it's only grabbing the same data for all of my notes). Here is the code I've been working on, but also a second function that I think has features that I need, such as grabbing the active sheet.

function noteSetter() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0]; //I need to make this grab the right sheet

 var cell = sheet.getRange(2, 2); //this and the line below it seem to be critical here...
 cell.setNote(sheet.getRange(1,1).getValue()); 
}

//below gives me the menu option to run the script, but I'm open to changing this
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
  name : "Set cell note",
    functionName : "noteSetter"
  }];
  sheet.addMenu("Scripts", entries);
};

Here is another one that is used on that post that has features that I think I need, but still doesn't get me the relative reference of the cells.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == "Project"){ //Change to your sheet name
    var sourceRange = e.source.getActiveRange();
    var sourceRow = sourceRange.getRow();
    var sourceColumn = sourceRange.getColumn();
    var sourceValue = sourceRange.getValue();

    if(sourceColumn == 1){ //Change to the column you want to get the value from
      var noteCell = sheet.getRange(sourceRow, 2); //The cell where the Note should be, B-column on the current row
      noteCell.setNote(sourceValue);
    }
  }
}

This is where I am now, but I may have messed some things up, as it no longer works at all.

function noteSetter() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

 var cell = sheet.getActiveRange();
 cell.setNote(sheet.getRange(sourceRow, sourceColumn).getValue());
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
  name : "Set cell note",
    functionName : "noteSetter"
  }];
  sheet.addMenu("Scripts", entries);
};

Any suggestions on how to make this work?

Before your noteSetter function, you state "this is where I am now". Do you still have the onEdit function, or just noteSetter now? I ask since, for one, sourceRow and sourceColumn are out of scope and undefined in noteSetter as written. — Joel Reid
Yes, sorry I didn't include that. I've just revised the post to reflect everything that I have in there now. And actually, I don't have the onEdit, I have onOpen, which creates a menu option. And I wasn't sure how to use the sourceRow and sourceColumn. I just plugged them in because I felt like it was getting closer to what I needed. They clearly don't work. When I had getRange listed as above, just (1,1), the menu option was collecting information from the first tab, in cell A1. But it was applying it to all cells in the range that I had highlighted before selecting the menu option. — Jentz
In your new "where I am now" your sourceRow and sourceColumn variables are being sent to sheet's getRange method as parameters, but they only existed (were defined) in the old onEdit function. [ Give a man a fish?: ] copy those lines that defined them to inside your noteSetter function. [ Teach a man to fish?: ] Super-helpfully, Apps Script is basically javascript. Try a couple tutorials on the javascript topics of "variable scope" and "function parameters". — Joel Reid
Ok, I'll make another few passes at it. Knowing to look up "variable scope" I think will be helpful. Thank you, Joel. — Jentz

Jentz Jentz · Accepted Answer · 2019-05-17T02:42:51

Just to close the loop on this a little, I'm 90% of the way there with the below. It works perfectly when I highlight one cell. But if I highlight more (e.g. a range or array), it sticks the only the top and left-most value as the note in all cells I've highlighted.

function noteSetter() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var column = sheet.getActiveRange().offset(0, 18);
  var cells = sheet.getActiveRange();

  cells.setNote((row, column).getValue());
}

I think the next step is using the Selection class, but it's good enough for my purposes.

Is there a way to dynamically reference cells in scripts for google sheets via script editor to create a note on another cell?

1 Answers