Google Apps Script: onEdit

Question

First of all, I'm a beginner. I was trying to do some automation in Google Sheets and I faced an interesting, but maybe silly problem: how can I delete the content of an unique cell or group of cells if I change the value in another specific cell? I want to delete whatever is written in cell B1 only if I change the value of cell A1. No matter what happens on cells C1, C2, B2 or A2, the only trigger that matters is editing cell A1.

I tried solving that by using the event onEdit(e), but the returned argument "e" does not contain any information about column or row that I could use for comparison, i.e. to discover the edition was made to cell A1. That way, I didn't find any specific condition I could use to solve my problem.

My code was:

function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = e.range;
  if (range == 'A1'){
    spreadsheet.getRange('B1').activate();
    spreadsheet.getActiveRangeList().setValue("");
    spreadsheet.getRange('A1').activate();
  }
}

As I've noticed, "range" will never be equal to 'A1' because the e.range method returns "Range", no matter the cell I edit: when the code returned e's value after I wrote "potato" in the cell, that's what showed up on the screen:

{ value=potato, authMode=LIMITED, range=Range, user=, source=Spreadsheet }

Is there any other way I can use the onEdit function to accomplish this task of mine? Or should I maybe use another event? Any suggestions?

This is the code that inspired me to use onEdit function (it works just fine):

function onEdit(e) {
   // Set a comment on the edited cell to indicate when it was changed.
   var range = e.range;
   range.setNote('Last modified: ' + new Date());
}

These three spreadsheet.getRange('B1').activate(); spreadsheet.getActiveRangeList().setValue(""); spreadsheet.getRange('A1').activate(); boil down to e.range.getSheet().getRange(1,2).setValue(''); — Cooper
webapps.stackexchange.com/a/87361/27487 was very helpful for me. — Ryan

Cooper Cooper · Accepted Answer · 2021-02-15T23:08:33

Same thing with fewer functions called most information comes from event object

function onEdit(e) {
  if(e.range.columnStart==1 && e.range.rowStart==1) {//same as A1
    e.range.offset(0,1).setValue('');//this is B1
  }
}

Google Apps Script: onEdit

2 Answers