google-apps-scriptgoogle-sheetstriggers

Get the actual change when onChange event fires

the doc is not very helpful. Unlike onEdit, onChange event doesn't have a propety that includes the change made. How do I get it?

function onChange(e){
  console.log(e);
}

enter image description here

There is no useful information in this event object.

The reason I can't use onEdit is I am using some outside script to POST to this sheet and it won't trigger the on edit event.

Solution

  • Use the source property of the change event object.

    This property returns the active spreadsheet, then you could use getActiveSheet(), getActiveRange() etc.

    function respondToChange(e){
  
  if(e.changeType === 'EDIT'){
    const spreadsheet = e.source;
    const range = spreadsheet.getActiveRange();
    console.log(range.getA1Notation());
  }

}

    Related