Google Sheets how to copy note from cell in separate sheet based on entered value-CodePudding

please excuse the accuracy of my language I'm new to this.

I would like to create a google sheet where I am able to enter a numeric value and that cell would import a note from a specified range on a separate sheet.

My application is to have a sheet of learning stages on one sheet. For example:

Listening stage 1 / 2 / 3 (etc)
Interacting stage 1 / 2 / 3 / 4 (etc)

Then I would like to have a second sheet where I am able to enter student name and then enter a numeric value and have that cell automatically show a note from the the previous sheet describing the learning stage in more detail. For example

           Listening                       /    Interacting  
Student 1/    2     >note from sheet1 cell B2-I2  /    1  >note from sheet1 cell B3-H3

I'm not sure if this is answered elsewhere as I have limited knowledge of how to search and interpret answers here. I would very much appreciate someone showing me how to set up a script to complete this in google sheets or pointing me in the right direction (let me know what functions to learn or where this question may already be answered).

Thank you.

CodePudding user response：

Try this

function onEdit(event){
  var sh = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if (sh.getName()=='Student progression map' && r.getRow()>2 && r.getColumn()>1){
    const ss=SpreadsheetApp.getActiveSpreadsheet()
    const stages = ss.getSheetByName('Learning stages')
    let list = stages.getRange('B:B').getValues().flat().filter(r => r!='')
    let row = (list.indexOf(sh.getRange(2,r.getColumn()).getValue()) 2)
    if (row>1){
      r.setNote(stages.getRange(row,r.getValue() 2).getNote())
      SpreadsheetApp.getActive().toast('Note has been added')
    }
    else {
      SpreadsheetApp.getActive().toast(`"${sh.getRange(2,r.getColumn()).getValue()}" can't be found!`)
    }
  }
}

Note that B1 of Learning stages has no value.