Google Apps Script: onEdit

First of all, I’m a begginner. 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 collumn 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());
}

35 thoughts on “Google Apps Script: onEdit”

  1. Solved. It’s possible to converte "range"’s value to A1 notation by using the following method:

    getA1Notation()
    

    That way I could finally compare the value of the edited cells with the trigger cell.

    Reply

Leave a Comment