Google Sheets Search from Database and insert into specific cell on edit

Question

i need some help from someone who is more experience than me. I've the following formula

=WENNFEHLER(SVERWEIS($B$3;$B6:C;{2};0))

and the following script

function copyIntoCell() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C3').activate();
  spreadsheet.getRange('A3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

How is it possible to add the formula into the script and also make it "onEdit" when a name entered into B3 it should auto insert the number into C3 from B6:C when B3 match with the database.

Also is it possible to autocomplete when I enter a word into B3 it suggest me the names from the database with the word I tipped in? This one is not important but would be nice.

Here is the example

Thanks for any help and idea I can get to complete what I looking for.

Answer 1

Combine script and formula as following:

• Check if the edit was performed in column B
• Retrieve the active row
• setFormula() to assign your formula to the active row in column C
• If the entered name is not found in the database and the formula returns the error "#NAME?" - delete the formula again

function onEdit(e) {
  //check if edit takes place in the second column (B)
  if(e.range.getA1Notation() =="B3"){
    //proceed
    var spreadsheet = SpreadsheetApp.getActive();
    var row = e.range.getRow();
    var formula = "=IFERROR(VLOOKUP($B$3,$B6:C,{2},0))";
    //set formula to active row in column C
    var cell = spreadsheet.getActiveSheet().getRange(row, 3);
    cell.setFormula(formula);
  }
};