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

user_14145488 0
Viewed 520

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'.

Expected Result

google-apps-script google-analytics google-sheets
2 Answers

For this case, you can implement a simple trigger (onEdit) to auto populate your sheet every time the material property is changed. You can do it like this:

function onEdit(e) {
  var ss = e.source;
  var sh = ss.getActiveSheet();
  var column = e.range.getColumn();
  var row = e.range.getRow();
  var material = e.value;
  if (column == 2) {
    if (material == "Copper") {
      sh.getRange(row, column+1,1,2).setValue("");
      sh.getRange(row, column+3,1,2).setValue("NA");
    }
    else if (material == "Steel") {
      sh.getRange(row, column+1,1,2).setValue("NA");
      sh.getRange(row, column+3,1,2).setValue("");
    }
  }
}

In this script, I have added .setValue("") to reset the other fields with no "NA".

To test the script, I have created a simple replication:

enter image description here

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

enter image description here

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

enter image description here

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

user_187037120

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", "", ""]]);
    }
  }
}
user_72150910
Related