CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Hugo Mendes
Hugo Mendes

Reputation: 33

Google Apps Script: onEdit

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());
}

Upvotes: 2

Views: 2299

Answers (2)

Cooper
Cooper

Reputation: 64110

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
  }
}

Upvotes: 1

Hugo Mendes
Hugo Mendes

Reputation: 33

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.

Upvotes: 1

Related Questions