1

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();
}
jarvis
  • 157
  • 1
  • 13

1 Answers1

3

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
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Any way to have the Trigger pulled only when `changeType == "INSERT_ROW"`? I've got a conditional at the top of my script to `return;` as per your suggestion, but it would be nice to avoid calling the script entirely. – sdo May 03 '20 at 19:16
  • 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:25