How to automatically set a value in drop-down list based on a value from another drop-down list

Question

I have a googlesheet with Columns A - P. Column B (GROUP) is a dropdown list and Column N (EXECUTION STATUS) is a drop-down list. I am trying to automatically set a particular value in a cell for GROUP based on the value that I selected in the EXECUTION STATUS dropdown list.

For Example: GROUP has the following values in the drop-down list:

DEVELOPER QA LEVEL 1 SUPPORT LEVEL 2 SUPPORT

EXECUTION STATUS has the following values in the drop-down list:

PASSED FAILED NOT EXECUTED BLOCKED

Here is what I want to happen: If I select FAILED as the EXECUTION STATUS, I want the GROUP to automatically change to DEVELOPER.

Here is my function:

function changeGroup(event)
{
  var ColN = 14;  // Column Number of "N"
  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == ColN) 
  {
    // An edit has occurred in Column N
    var state = changedRange.getValue();
    var Group = event.source.getActiveSheet().getRange(changedRange.getRow(),ColN-12);
    switch (state) 
    {
      case "FAILED":
        // Select DEVELOPER from dropdown list
        Group.setValue("Developer");
        break
      
    }
  }
}

I think my problem is the Group.setValue("Developer") line. SetValue is setting a text value. I am trying to set a value from the drop-down list. I'm not sure. Any suggestions?

Tanaike Tanaike · Accepted Answer · 2020-03-21T23:09:05

The data validation rules distinguishes the uppercase and lowercase letters. I thought that this might be the reason of your issue. So if your values of data validation rules are DEVELOPER of the uppercase letter, please use it as follows.

Group.setValue("DEVELOPER");

Note:

In your script, I think that the OnEdit event trigger of the simple trigger can be used. But from your script, it seems that you are using the installable event trigger. So please confirm whether the function of changeGroup is installed as the installable OnEdit event trigger, again.
By the way, if you want to run the script for the specific sheet, please modify if (changedRange.getColumn() == ColN) as follows.
```
if (changedRange.getColumn() == ColN && changedRange.getSheet().getSheetName() == "Sheet1")
```

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Added:

In your case, the simple trigger can be also used. So how about testing the following script? When you use this script, please copy and paste the following script. Then, please change the dropdown list at the column "N".

function onEdit(event)  // <--- Modified
{
  var ColN = 14;  // Column Number of "N"
  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == ColN) 
  {
    // An edit has occurred in Column N
    var state = changedRange.getValue();
    var Group = event.source.getActiveSheet().getRange(changedRange.getRow(),ColN-12);
    switch (state) 
    {
      case "FAILED":
        // Select DEVELOPER from dropdown list
        Group.setValue("DEVELOPER");
        break

    }
  }
}

Reference:

Simple Triggers