Google sheet + App script: select item from a drop down menu but replace the selected value with another value

Question

Hi I'm trying to get my drop down menu to display a list of item from column "D" and once the item from column "D" is selected I would need the value to be change to the matching ID from column "E" value like shown in this example

I've tried with formulas without success I think it might have to be done with a script with an onedit function but not sure how. Here's a link to the google sheet https://docs.google.com/spreadsheets/d/1dfI-jvfsyQ4BxmHnEtnhrA-eiNBV5IyGWkVrmcjX83M/edit?usp=sharing

Answer 1

You can refer to this sample code:

function onEdit(e) {
  var ss = e.source;
  var cell = e.range;

  //Get your display value and id 
  var ids = ss.getRange('Sheet2!A2:B5').getDisplayValues();


  //Check if modified cell is within the drop-down list range example Sheet1!B2:B
  //Check if modified cell is in a specific sheet and the value is not null
  if(ss.getActiveSheet().getName() =='Sheet1' && cell.getRow() > 1 && cell.getColumn() == 2 && e.value != null){

    //Search for the the id based on the modified cell's value
    var matchedId = ids.find(id=>{
      return id[0] == cell.getDisplayValue();
    })

    //Update the cell value
    cell.setValue(matchedId[1]);
  }
  
}

What it does?

1. Create an onEdit() simple trigger, get the spreadsheet and range object using e.source and e.range respectively.
2. Get the drop-down display value and its corresponding id using Spreadsheet.getRange(a1Notation). In this example, I created "Sheet2" that contains the values and ids in range Sheet2!A2:B5
3. Check if the modified cell is in Sheet1, row index should be greater than 1, column index should be 2(in column B) and modified cell value should not be empty.
4. Find the matching value-id based on the modified cell's value using array.find()
5. Update the modified cell's value based on its id.

Sample Sheet2:

Output: