Google Sheet Script onChange is triggered when cell value is changed

Question

I add an onChange trigger to my Google Sheet via Tools->Script Editor -> Edit -> Current Project's trigger ->... I thought onChange is only triggered when the structure of data/sheet is changed, e.g., add or delete row and/or columns. However, the testing shows the onChange is triggered when cell value is changed as well.

So the onChange trigger behaves almost identical to the onEdit trigger. I am trying to avoid the onEdit trigger since it's triggered too often, which drags down the speed of Google Sheet.

Ideally, I'd like the onChange only being triggered when new rows and/or columns are added. Any help would be highly appreciated!

Read [this](https://developers.google.com/apps-script/guides/triggers/events#change). It identifies the different change types. — Cooper, Sep 18 '19 at 18:45

score 3 · Answer 1 · answered Sep 19 '19 at 07:22

3

There is an event object which contains information about the context that caused a trigger when it is fired. For the case of Google Sheets, the event object has a changeType parameter which is a string describing the type of the change. As described in the Event Objects Documentation, this can take the string value of:

EDIT
INSERT_ROW
INSERT_COLUMN
REMOVE_ROW
REMOVE_COLUMN
INSERT_GRID
REMOVE_GRID
OTHER

Cooper's answer will work although as there are other change types including the catch-all OTHER, it will also make the function fire on edits such as sorting ranges or inserting images. Try something like:

function onChange(e){
  if(e.changeType == 'INSERT_ROW' || e.changeType == 'INSERT_COLUMN'){
    // your code here
  }
  else {
    return;
  }
}

And then set up your installable trigger as normal in Edit > Current project's triggers > + Add Trigger.

answered Sep 19 '19 at 07:22

Rafa Guillermo

14,474
3
18
54

Thanks for your help - Rafa! Currently I am using changeType == 'INSERT_ROW' as a workaround. Looks like the onChange doesn't behave as GAS documented "triggered when sheet structure changes". Thanks, – Aruba Sep 19 '19 at 19:30
@Aruba Which documentation are you refering to? There isn't an onChange trigger aside from the method of the [SpreadsheetTriggerBuilder](https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder). – Rafa Guillermo Sep 20 '19 at 07:19
Hi, thanks for this answer. Can you please take a look at this similar question? I'm trying to capture which column was inserted in the `onChange` event. Thanks! https://stackoverflow.com/questions/72342596/google-sheets-app-script-onchangeevent-for-insert-column-how-to-get-which-col – Ryan May 23 '22 at 02:35

score 2 · Answer 2 · answered Sep 18 '19 at 18:47

2

Do something like this

function myOnchangeFunction(e) {
  if(e.changeType=='EDIT') {
    return;
  }
}

answered Sep 18 '19 at 18:47

Cooper

59,616
6
23
54

Google Sheet Script onChange is triggered when cell value is changed

2 Answers2