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?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityWhen 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?
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
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
}
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.
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:
Checking for the range first, will eliminate the call for retrieving the sheet name each time you edit a cell throughout the spreadsheet.
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
}
}
}