0

Is there a function where if you modify a certain cell, the contents of another cell will be cleared?

E.g. If A1 is modified the content of B1 will be cleared.

1

To automatically clear a cell (deleting it's value or formula, background color settings, etc.) when another cell changes, we should use a Google Apps Script triggered by a simple or installable on edit trigger taking advantage of the automatically assigned event object.

Let say that we want that on Sheet 1, B1 be cleared every time that A1 is edited. The following script will do the job:

function onEdit(e){
  if(e.range.getA1Notation() == 'A1' && 
      e.range.getSheet().getName() == 'Sheet 1'
    ) 
    e.source.getRange('B1').clear()
}

Note: A simple trigger doesn't require authorization to run, so there are some limitations about what they could do. In this case, a simple trigger it's good enough to achieve the desired result.

  • 1
    @SleepingGod: There are "custom functions" but not "custom scripts" as there aren't "built-in scripts" (: Custom functions are scripts but not all scripts are custom functions. Custom functions can return values but the can't clear cells. – Rubén May 14 '17 at 0:52
  • sorry about that (this is not my area of expertise), but could you rewrite/expand on "triggered by a simple or installable on edit trigger taking advantage of the automatically assigned event object" slightly for clarity. I'm a bit baffled as to it's meaning though I understand overall what your trying to say i.e what is a simple? – SleepingGod May 14 '17 at 0:55
  • 1
    @SleepingGod: I added a couple of links and a note. Thanks for your feedback. – Rubén May 14 '17 at 1:01
  • I tried using it but I ended up with this error: TypeError: Cannot read property "range" from undefined. (line 3, file "Code") Here is the spreadsheet if you need access. Thanks! docs.google.com/spreadsheets/d/… – Daryl Sandoval May 15 '17 at 17:34
  • @DarylSandoval: It's very likely that you tried to run the script by using the run command from the script editor. The problem with that is that the event object it's not automatically updated in such case. The solution is to create a an auxiliary function to populate the required parameter. – Rubén May 16 '17 at 1:58
0

Slightly different code, with the same outcome

Code

// only when cell A1 is changed, clear cell B1
function onEdit(e) {
  if(e.range.rowStart === 1 && e.range.columnStart === 1) { 
    e.range.offset(0,1).clearContent();
  }
}

// when a cell in column A (A:A) is changed, clear the corresponding cell in column B
function onEdit(e) {
  if(e.range.columnStart === 1) { 
    e.range.offset(0,1).clearContent();
  }
}

Explained

This code will be triggered throughout all sheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.