Google-sheets – How to Clear Cell(s) based on the Value of another Cell

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I am looking for a script that will clear cell(s) when the value of another cell changes or is equal to ("X").
capture1

In the attachment I would like when Column "G" gets checked marked (TRUE), then Columns "D" & "E" become cleared (or no value) according to the respective row that was checked (TRUE).

For Example:

When "G3" is checked (TRUE), then D3 & E3 are cleared. As you can see that it is important that both columns get cleared out as it affects Columns "E", "F", and "H" which all include very important information that needs to be automatically updated according to Column "G" to remove as much human error as possible.
Capture2

I have done some research on my own searching through this site, other sites and forums, and google support, etc. There was one that was close to this that I wanted, but it would "hide" the row, which I don't want and supposedly it would clear a cell but I couldn't get that to work. i attempted to make my own edits but I do not have experience enough to troubleshoot.

Here is something that I found and couldn't troubleshoot:

function onEdit(e) {
    var sh = e.source.getActiveSheet();
    if (sh.getName() !== 'Sheet2' || e.range.columnStart !== 3 || e.range.rowStart < 2 || e.value !== 'No') return;
    e.range.offset(0, 3).clearContent()
    sh.hideRows(e.range.rowStart)
}

Then change the highlighted text to the name of the sheet/tab you want the script to work on.
Finally close the script editor (!) and navigate to that sheet. Try entering 'No' in col C and see what happens.

I edited it to be:

function onEdit(e) {
    var sh = e.source.getActiveSheet();
    if (sh.getName() !== 'Main' || e.range.columnStart !== 7 || e.range.rowStart < 2 || e.value !== 'FALSE') return;
    e.range.offset(0, 3).clearContent()
    sh.hideRows(e.range.rowStart)
}

I can get it to hide the row (which I don't want), but I can't get it to clear a cell, let alone two of them.

Here is what I have from Raymond Tran's answer (good workaround for now):

Before

After

Best Answer

The code line that hides the row is 

sh.hideRows(e.range.rowStart)

To avoid that the row be hidden, just remove that line.

The code line that clears a a cell content is 

e.range.offset(0, 3).clearContent()

It's clearing a cell in the same row, three columns to the right, i.e. if G3 is edited then the content of J3 is cleared. To clear the content of C3 and D3 change the above code line by

e.range.offset(0, -4, 1, 2).clearContent()

Reference

Related Topic