2

When I have multiple sheets in a spreadsheet, I want to execute a trigger only when One of the Sheets is changed, not the others. OnChange() or OnEdit() is executing the trigger for any data change.

Any suggestions?

0

We can't limit a trigger to be executed only on certain sheets but we could limit if some parts of the code are executed if a condition is met. For this, we could use JavaScript statements like if, switch, etc.

Simple edit trigger example:

function onEdit(event){
  var sheet = event.range.getSheet();
  if(sheet.getName() === 'Sheet1') {
    //do something
  }
}

Notes:

Some operations doesn't return the active range/sheet, when using a change trigger. Ref. getActiveRange() incorrect in onChange trigger for some column/row operations

Related Q&A

| improve this answer | |
0

you can specify on which sheet will be script executed (as Rubén's example):

function onEdit(event){
  var sheet = event.range.getSheet();
  if(sheet.getName() === 'Sheet1') {
    //your script
  }
}

or for example only on the active sheet like:

function onEdit(event){ 
  var sheet = event.source.getActiveSheet();
  //your script
}
| improve this answer | |
0
function onEdit(){
  var sheet = SpreadsheetApp.getActiveSheet().getSheetName()
  if(sheet === 'your sheet name') {

///your code
}

This only executes the code ONLY IF 'your sheet name' was edited.

| improve this answer | |
0

You can use the onEdit simple trigger to accomplish this. In order to minimize the API calls (contacting the Google server), it is advisable to limit its range in which it will execute. When triggered, it comes with an event object that contains build in parameters, amongst one is the range:

  • columnStart
  • rowStart
  • rowEnd
  • columnEnd

Checking for the range first, will eliminate the call for retrieving the sheet name each time you edit a cell throughout the spreadsheet.

Code

Rhino (ES5)

function onEdit(e) {
  if(e.range.rowStart > 1 && e.range.columnStart === 1) {
    var sh = e.source.getActiveSheet();
    if(sh.getName() === 'Sheet1') {
      // your script
    }
  }
}

V8 (ES6)

const onEdit = (e) => {
  if(e.range.rowStart > 1 && e.range.columnStart === 1) {
    const sh = e.source.getActiveSheet()
    if(sh.getName() === 'Sheet1') {
      // your script
    }
  }
}

Reference

  1. https://tanaikech.github.io/2018/04/14/benchmark-event-objects-for-google-apps-script/
  2. https://developers.google.com/apps-script/guides/v8-runtime
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.