Auto clear dependent data validation in Google Sheets

Question

I have a Google sheet where I’m using a dependent drop down for users to select one option and then select a second option depending on their first choice. Im doing this using the Index function on a separate tab where all my data is.

I’m trying to figure out a way to auto clear the second option when either the first choice is deleted or changed.

I feel like a script should be able to take care of this but im a novice when it comes to writing code and I can’t find an answer on the internet.

Any advice would be greatly appreciated!

Answer 1

Issue:

You want to clear the cells in columns I and J whenever column B is edited.

Solution:

You could use a simple onEdit trigger for this. When you have an onEdit trigger, the corresponding function will fire every time a user edits a cell. In this case, you can do the following:

• Use the corresponding event object to check whether the edited cell is in column B from sheet Waiver Claims.
• If that's the case, clear the content from the desired cells using clearContent().

Code snippet:

function onEdit(e) {
  const range = e ? e.range : SpreadsheetApp.getActiveRange();
  const sheet = range.getSheet();
  const colIndex = range.getColumn();
  const rowIndex = range.getRow();
  if (rowIndex > 3 && colIndex === 2 && sheet.getName() === "Waiver Claims") {
    sheet.getRange(rowIndex,9,1,2).clearContent();
  }
}

Reference:

• Simple triggers: onEdit(e)