Google-sheets – Insert cell’s note from a relevance cell of another sheet

google sheets

I am making a few of matrix of educational programs.

I need to insert cell's note (for example in sheet D1) from a cell of another sheet (for example sheet "dict_LO") by key cells of both sheets with some word (this word is in the cell where to insert note).

On a post is a solution.
Punchlinern proposed a script:

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

  var cell = sheet.getRange(2, 2);
  cell.setNote(sheet.getRange(1,1).getValue());
}

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

This script must be modified under my conditions, but I do not know how 🙁

How does it?

Voila, my spreadsheet.

Best Answer

The following code looks for the selected value on the first column of simple table of notes. If the value is found the add a note with the corresponding description, otherwise add a note with an error message.

function noteSetter() {
  // Customize this values according to your needs.
  var notesSheetName = 'Notes';
  var notesColumn = 2;

  // Intialize variables with relevant Spreasheet objects
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var notesSheet = ss.getSheetByName(notesSheetName);
  var notesMatrix = notesSheet
        .getRange(1,1,notesSheet.getLastRow(),1)
        .getValues();
  // Convert a 1xn matrix (2D array) to a list of values (1D array)      
  var notesList = notesMatrix.join(',').split(',');
  // Get the key to look for on the notes sheet
  var range = ss.getActiveRange();
  var key = range.getValue();
  // Find the row number of the required note
  var notesRow = notesList.indexOf(key)+1;

  // Set note
  // If the key is found, then set the corresponding note,
  // otherwise then set an error message.
  var note = '';
  if(notesRow >=1) {
    note = notesSheet.getRange(notesRow,notesColumn).getValue();
  } else {
    note = 'Key not found';
  }
  range.setNote(note);
}
Related Topic