2

I have now a spreadsheet where I want to:

  • Track when someone CHANGES a value in a cell that already has a value
  • Track when someone adds a NEW value in a cell that has no value
  • Track when someone DELETES a value in a cell

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?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Asim
  • 936
  • 2
  • 10
  • 29
  • 1
    There are 2 trigger type, (Simple)[https://developers.google.com/apps-script/guides/triggers/] and (Installable)[https://developers.google.com/apps-script/guides/triggers/installable]. Since there is not a Simple trigger for a Change event, you must have defined `onmychange()` as an Installable trigger. Did you set both as an Edit trigger? You want it to be a Change trigger. onEdit() is a Simple trigger so you do not need to define that one in the Installable triggers location as the name of the function is enough to trigger it on edits. – Karl_S Jul 31 '17 at 18:41
  • See https://stackoverflow.com/questions/22389875/google-application-script-onchange-or-onedit for an explanation of trigger types – terrywb Jul 31 '17 at 18:43
  • onmychange() is on change in triggers and onEdit i have nothing. still both fires and not just one. When i change value in a col BOTH fires
    When i add value in blank col BOTH fires Ive tried to trigger on change and edit in triggers, but i dont know why this happends, im aware of trigger types terrywb     – Asim Jul 31 '17 at 18:51

1 Answers1

8

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