2

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
shenkwen
  • 3,536
  • 5
  • 45
  • 85

2 Answers2

1

Let's suppose you have this source code:

const showEvent = e => {
  Logger.log(JSON.stringify(e, null, 2));
}

const setTrigger = () => {
  const sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('showEvent')
    .forSpreadsheet(sheet)
    .onChange()
    .create();
}

You set your trigger with setTrigger() and every time a change event is dispatched, showEvent() will print out something like this:

{
  "authMode": "FULL",
  "changeType": "REMOVE_ROW",
  "source": {},
  "triggerUid": "8302470070286889650",
  "user": {
    "email": "user@gmail.com",
    "nickname": "user"
  }
}

The trick is making sure you specify and event argument in your triggered function, in my case it's e as in showEvent(e)

Dmitry Kostyuk
  • 1,354
  • 1
  • 5
  • 21
  • This is exactly what I get from `console.log(e)` when there are changes made to the sheet. But I wanna see from the event object what changes are being made, what values are being edited. See my question update. – shenkwen Jun 11 '21 at 12:03
1

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

Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
Rubén
  • 34,714
  • 9
  • 70
  • 166