CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Asim
Asim

Reputation: 976

Why do both onChange and onEdit triggers fire at the same time?

I have now a spreadsheet where I want to:

Now when I test install-triggers and even onEdit() and onChange() I just don't get it that why both fires whenever I change a value or add new value on blank cell.

You can see my code here:

function onEdit(e){
  Browser.msgBox('EDIT')
}
// trigger --> change
function onmychange(e){
 Browser.msgBox('CHANGE')
}

If I edit a cell with value and change it to something else, both get fired If I write something in a new cell, both fired

My trigger: onmychange --> on spreadsheet --> on change

Why do both get fired?

Upvotes: 2

Views: 6840

Answers (1)

user6655984
user6655984

Reputation:

As documentation says, the "on change" trigger includes EDIT among other kinds of changes:

The type of change (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER)

If you wanted to not execute code when the change is an edit, use a condition such as 

function onmychange(e) {
  if (e.changeType != "EDIT") {
    Browser.msgBox("Change that is not an edit");
  }
}

That said, all the actions you mentioned (CHANGES a value in a cell that already has a value, adds a NEW value in a cell that has no value, DELETES a value in a cell) are Edits, so onEdit is the only trigger you need.

If the goal is to find what kind of an edit was made, use e.value and e.oldValue. The former has some quirks when using simple trigger onEdit(e):

  1. If a user inputs "foo" directly (includes pasting from an external source): e.value is "foo"
  2. If a user pastes "foo" that was copied from elsewhere in the sheet: e.value is {}, empty object.
  3. If a user clears out the cell that had "foo" previously: e.value is the object {"oldValue":"foo"}

(If one uses an installable trigger running on edit, then in cases 2 and 3 there is no property "value" in the event object at all.)

In order to get around the aforementioned quirks, I use the following: 

function onEdit(e) {
  var newValue = (typeof e.value == "object" ? e.range.getValue() : e.value); 
  //
}

which makes sure that newValue has the new value that is now in the cell.

You may want to observe the behavior of triggers by using the following logging trigger: 

function onEdit(e) {
  e.range.offset(0, 1).setValue(JSON.stringify(e));
}

Upvotes: 8

Related Questions