How to change value of a column when selecting the value of other column Spreadsheet by Appscript

I believe your current situation and your goal as follows.

• From your script, I understood that the dropdown list of left side is "E5". And, "E5" has already had the data validation rule with the values of 1, 2, 3.
• The data for the dropdown list can be used from the cells "A3:C11".
• When the dropdown list of "E5" is changed, you want to change the dropdown list of "F5" using the values of column "B".
• When the dropdown list of "F5" is changed, you want to put the value to "G5" using the values of column "C".

Modification points:

• In your script, only row number of the dropdown list at "E5" is retrieved. And, the event object of e of onEdit(e) is not used.

• In order to achieve your goal, I would like to propose the following flow.

  1. Check the sheet name and dropdown list.
  2. Create an object for putting the values of 2nd dropdown list and last output cell.
  3. Update 2nd dropdown list and the last output cell.

When above points are reflected to the script, it becomes as follows.

Sample script:

Please set the value of sheetName. I used the values of dropdownList and output from your question.

function onEdit(e) {
  const dropdownList = ["E5", "F5"]; // The cell coordinates of dropdown list.
  const output = "G5"; // Output value from the 2nd dropdown list.
  const sheetName = "Sheet1"; // Sheet name of sheet that the dropdown list is put.

  // 1. Check the sheet name and dropdown list.
  const range = e.range;
  const sheet = range.getSheet();
  const a1Notation = range.getA1Notation();
  if (sheet.getSheetName() != sheetName || !dropdownList.includes(a1Notation)) return;

  // 2. Create an object for putting the values of 2nd dropdown list and last output cell.
  const { obj } = sheet.getRange("A3:C11").getValues().reduce((o, [a, b, c]) => {
    if (a) {
      o.temp = a;
      o.obj[o.temp] = { [b]: c };
    } else {
      o.obj[o.temp] = Object.assign(o.obj[o.temp], { [b]: c });
    }
    return o;
  }, { obj: {}, temp: "" });

  // 3. Update 2nd dropdown list and the last output cell.
  const v = e.value;
  if (a1Notation == dropdownList[0]) {
    const dropdown2 = SpreadsheetApp.newDataValidation().requireValueInList(Object.keys(obj[v])).build();
    sheet.getRange(output).clearContent();
    sheet.getRange(dropdownList[1]).clearContent().setDataValidation(dropdown2);
  } else if (a1Notation == dropdownList[1]) {
    const dropdown1 = range.offset(0, -1).getValue();
    sheet.getRange(output).setValue(obj[dropdown1][v]);
  }
}

• When you want to run this script, please change the 1st dropdown list of "E5". By this, 2nd dropdown list of "F5" is updated. And, when you change the 2nd dropdown list, the last output cell of "G5" is updated.

Note:

• Above script is run by the simple trigger. So when you directly run the function of onEdit, an error like Cannot read property 'range' of undefined occurs. Please be careful this.

• In this sample script, it supposes that the 1st dropdown list of cell "E5" has already had the data validation rule with the values of 1, 2, 3. Please be careful this.

References:

• Simple Triggers
• Event Objects