CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttriggers
Aruba
Aruba

Reputation: 41

Google Sheet Script onChange is triggered when cell value is changed

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!

Upvotes: 4

Views: 7524

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

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.

Upvotes: 3

Cooper
Cooper

Reputation: 64140

Do something like this

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

Upvotes: 3

Related Questions