How to execute a trigger only when one of the sheets is changed

Question

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?

Answer 1

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

• Insert current date to cell based on content in other cell
• Make function only work on first sheet, not in whole Google Sheets file
• Google App Script - changeType "REMOVE_GRID"

Answer 2

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
}

Answer 3

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.

Answer 4

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