Auto-populate other column cells based on a column in google sheet-CodePudding

I am trying to auto-populate some values in my google sheet

If column B has the value as 'Steel', it should auto populate 'NA' in column C and D. On the other hand if the value in Column B is filled as 'Copper', Column E and F should become 'NA'.

I cannot use a formula because then I cannot input other values in those columns. For example, I need to select values from dropdown in column E and F when Column B is 'Steel'.

When I set the Material property to "Steel", I get:

On the other hand, when I change the Material property to "Copper", I get:

You can refer to the Simple Triggers Guide and Event Objects Guide for more details.

CodePudding user response：

Try it this way:

function onEdit(e) {
  var sh = e.range.getSheet();
  if (sh.getName() == "Sheet Name" && e.range.columnStart == 2) {
    if (e.value == "Copper") {
      sh.getRange(e.range.rowStart, e.range.columnStart   1, 1, 4).setValues([["", "", "NA", "NA"]]);
    }
    else if (e.value == "Steel") {
      sh.getRange(e.range.rowStart, e.range.columnStart   1, 1, 4).setValues([["NA", "NA", "", ""]]);
    }
  }
}