1

From what I've gathered online, onEdit only works when you edit a cell (not when a row is added or when a note/comment is added) and onChange will capture that a change has occurred and trigger when appropriate.

Should I change all my onEdit triggers to onChange so they always work?

closed as primarily opinion-based by ale, Rubén, serenesat, jonsca Aug 15 '18 at 23:30

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What are the criteria to say which one is better? What is the context where they are supposed to be used? "all my onEdit triggers" isn't clear as we don't know how many and how you use the onEdit triggers. – Rubén Aug 15 '18 at 1:13
5

based on a quick search these are the differences between onEdit(e) and onChange(e):

onEdit(e):

brief description:

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet. Most onEdit(e) triggers use the information in the event object to respond appropriately. For example, the onEdit(e) function below sets a comment on the cell that records the last time it was edited. An installable edit trigger runs when a user modifies a value in a spreadsheet.

onEdit(e) can be simple trigger or installable trigger:

Simple Triggers let Apps Script run a function automatically when a certain event, like opening a document, occurs. Simple triggers are a set of reserved functions built into Apps Script, like the function onOpen(e), which executes when a user opens a Google Docs, Sheets, Slides, or Forms file. Installable triggers offer more capabilities than simple triggers but must be activated before use. For both types of triggers, Apps Script passes the triggered function an event object that contains information about the context in which the event occurred.

these are the disadvantages:

Because simple triggers fire automatically, without asking the user for authorization, they are subject to several restrictions:

  • The script must be bound to a Google Sheets, Slides, Docs, or Forms file.
  • They do not run if a file is opened in read-only (view or comment) mode.
  • Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.
  • They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.
  • They can modify the file they are bound to, but cannot access other files because that would require authorization.
  • They may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions.
  • They cannot run for longer than 30 seconds.
  • In certain circumstances, add-ons for Google Sheets, Slides, Docs, and Forms run their onOpen(e) and onEdit(e) simple triggers in a no-authorization mode that presents some additional complications. For more information, see the guide to the add-on authorization lifecycle.



onChange(e):

brief description:

An installable change trigger runs when a user modifies the structure of a spreadsheet itself—for example, by adding a new sheet or removing a column.

onChange(e): is only installable trigger:

Like simple triggers, installable triggers let Apps Script run a function automatically when a certain event, such as opening a document, occurs. Installable triggers, however, offer more flexibility than simple triggers: they can call services that require authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically. For both simple and installable triggers, Apps Script passes the triggered function an event object that contains information about the context in which the event occurred.

Even though installable triggers offer more flexibility than simple triggers, they are still subject to several restrictions:

these are the disadvantages:

  • They do not run if a file is opened in read-only (view or comment) mode.
  • Script executions and API requests do not cause triggers to run. For example, calling FormResponse.submit() to submit a new form response does not cause the form's submit trigger to run.
  • Installable triggers always run under the account of the person who created them. For example, if you create an installable open trigger, it will run when your colleague opens the document (if your colleague has edit access), but it will run as your account. This means that if you create a trigger to send an email when a document is opened, the email will always be sent from your account, not necessarily the account that opened the document. However, you could create an installable trigger for each account, which would result in one email sent from each account. -A given account cannot see triggers installed from a second account, even though the first account can still activate those triggers.

important note:

The type of change for onChange(e):

  • EDIT
  • INSERT_ROW
  • INSERT_COLUMN
  • REMOVE_ROW
  • REMOVE_COLUMN
  • INSERT_GRID
  • REMOVE_GRID
  • FORMAT
  • or OTHER

onChange(e) trigger needs to be installed eg. simply changing "onEdit" to "onChange" is not enough. this is the installation code:

function createSpreadsheetOpenTrigger() {
   var ss = SpreadsheetApp.getActive();
   ScriptApp.newTrigger('myFunction')
    .forSpreadsheet(ss)
    .onOpen()
    .create();
    }
  • @quesyrahsarah also found this post: productforums.google.com/forum/#!topic/docs/… where is mentioned that onChange wont detect recalculations of formulas. – user0 Aug 14 '18 at 22:30
  • 1. onChange isn't a reserved function name, in other words, an on change installable trigger could be linked to functions no matter their names. 2. An on change installable trigger can detect in cell edits. – Rubén Aug 15 '18 at 1:08
  • @user0, i've already added the installation code but when i tried using the onChange(e) trigger, it doesn't seem to work unless i manually run the function. i've read the documentation already, but it made my head spin! the onChange trigger should work in theory, but it just doesnt.... i guess i won't mess with a good thing and just stick with onEdit. onChange sounds a little too complicated for me. i think i'll just give up on this one. thanks for taking the time to write the explanation! – que syrah sarah Aug 15 '18 at 4:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.