How to bound the onChange() google apps script function to specific events?

0
votes

I have tried the following block of code where by I need to trigger a script when changes are made to the other spreadsheet. The script triggers & executes successfully. However I would like to trigger it for specific change events like "INSERT_ROW", "REMOVE_ROW", INSERT_COLUMN, & REMOVE_COLUMN only.

Reference Available here. But I cannot figure out the implementation https://developers.google.com/apps-script/guides/triggers/events#change

function uploadsDataOnChange(e){
  var ss = SpreadsheetApp.openById("xxxxxxxxxxx")
  ScriptApp.newTrigger('uploadToBigQuery')
          .forSpreadsheet(ss)
          .onChange()
          .create();
}
1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

You need to add a exit criteria in the triggered function:

function uploadToBigQuery(e){
  if(e.changeType !== 'INSERT_ROW') return; //Run subsequent code only on INSERT_ROW
  //or:
  if(['INSERT_ROW','INSERT_COLUMN', 'REMOVE_COLUMN','REMOVE_ROW']
    .indexOf(e.changeType) === -1) return;
  //upload to bq here
}