How to use multiple OnEdit functions across multiple Google Sheets

Question

I am currently working on a Project where I am trying to fill in the 'Last Updated' date based on changes made in a google sheet by a user.

I have three google sheets in the same workbook.

I followed a tutorial which helped me get what I wanted however, when I use the same code across sheets it uses the last one which I read OnEdit does.

As you can see below. If a user updates Column (Col) 1-17 in Sheet 1 then add the last updated to Col 18 in Row.

Note: The code below is all in 1 .gs file.

Expected Behaviour:

When I am in Sheet 1 update Col 18 (Last Update) based on changes and the ranges specified in Sheet1. If in Sheet 2 do the corresponding for the respective ranges there. Same for Sheet 3.

See Code Below:

function onEdit(x) {

  addTimeStampGlossary(x);
  
}

function onEdit(y) {
  
 addTimeStampTables(y);
  
}

function onEdit(z) {
  
 addTimeStampFields(z);
  
}


function addTimeStampGlossary(x){
  // variables
  var startRow = 2;
  var ws = "Sheet1";

  //get modified row and column
  var row = x.range.getRow();
  var col = x.range.getColumn();

  if(col === 1 || col === 2 || col === 3 || col === 4 || col === 5 || col === 6 || col === 7 || col === 8 || col === 9 || col === 10 || col === 11 || col === 12 || col === 13 || col === 14 || col === 15 || col === 16 || col === 17 && row >= startRow && x.source.getActiveSheet().getName() === ws ){
    x.source.getActiveSheet().getRange(row,18).setValue(new Date());
  }
 
 }
 
 function addTimeStampTables(y){
  // variables
  var startRow = 2;
  var ws = "Sheet2";

  //get modified row and column
  var row = y.range.getRow();
  var col = y.range.getColumn();

  if(col === 1 || col === 2 || col === 3 || col === 4 || col === 5 || col === 6 || col === 7 || col === 8 || col === 9 || col === 10 || col === 11 || col === 12 || col === 13 || col === 14 || col === 15 || col === 16 || col === 17 || col === 18 || col === 19 && row >= startRow && y.source.getActiveSheet().getName() === ws ){
    y.source.getActiveSheet().getRange(row,20).setValue(new Date());
  }
 
 }
 
  function addTimeStampFields(z){
  // variables
  var startRow = 2;
  var ws = "Sheet3";

  //get modified row and column
  var row = z.range.getRow();
  var col = z.range.getColumn();

  if(col === 1 || col === 2 || col === 3 || col === 4 || col === 5 || col === 6 || col === 7 || col === 8 || col === 9 || col === 10 || col === 11 || col === 12 || col === 13 || col === 14 || col === 15 || col === 16 && row >= startRow && z.source.getActiveSheet().getName() === ws ){
    z.source.getActiveSheet().getRange(row,17).setValue(new Date());
  }
 
 }

Every function in a google apps script project must have a unique name — Cooper, Dec 29 '20 at 20:21
@Cooper of course it is not recommended at all to have multiple functions with the same name, however you could do that :P The compiler won't complain. — Marios, Dec 29 '20 at 20:29
The script editor may not complain but I think only one of them will run. — Cooper, Dec 29 '20 at 20:41
@Marios thank you both - this really helped and for the link to the similar question - I resolved my query. — Ankhit Sharma, Dec 29 '20 at 23:53

score 2 · Answer 1 · answered Dec 29 '20 at 20:35

You could improve your script performance by using more data from the event block and thus minimize the use of unnecessary functions as show here:

function addTimeStampGlossary(x){
  const sh=x.range.getSheet();
  if(x.range.columnStart >=1 && x.range.columnStart<=17 && x.range.rowStart>=2 && sh.getName()=='Sheet1'){
    sh.getRange(x.range.rowStart,18).setValue(new Date());
  }
}

I'm not trying to take points away from Marios. I just wanted to let you know that performance is important in the functions because simple triggers must complete in 30 seconds. And I know people tend to over use them so making them run quickly is important.

That was a valid point. Don't need to feel like you're taking points :) good addition — Marios, Dec 29 '20 at 20:42
@Cooper thank you and yes a valid point - I was thinking all those ORs were getting a bit much :) — Ankhit Sharma, Dec 29 '20 at 23:51

score 1 · Accepted Answer · answered Dec 29 '20 at 20:02

You can't have multiple onEdit functions.

Keep only one onEdit function:

function onEdit(e) {
  addTimeStampGlossary(e);
  addTimeStampTables(e);
  addTimeStampFields(e);
}

and the other functions as well:

function addTimeStampGlossary(x){
  // variables
  var startRow = 2;
  var ws = "Sheet1";

  //get modified row and column
  var row = x.range.getRow();
  var col = x.range.getColumn();

  if(col === 1 || col === 2 || col === 3 || col === 4 || col === 5 || col === 6 || col === 7 || col === 8 || col === 9 || col === 10 || col === 11 || col === 12 || col === 13 || col === 14 || col === 15 || col === 16 || col === 17 && row >= startRow && x.source.getActiveSheet().getName() === ws ){
    x.source.getActiveSheet().getRange(row,18).setValue(new Date());
  }
 
 }
 
 function addTimeStampTables(y){
  // variables
  var startRow = 2;
  var ws = "Sheet2";

  //get modified row and column
  var row = y.range.getRow();
  var col = y.range.getColumn();

  if(col === 1 || col === 2 || col === 3 || col === 4 || col === 5 || col === 6 || col === 7 || col === 8 || col === 9 || col === 10 || col === 11 || col === 12 || col === 13 || col === 14 || col === 15 || col === 16 || col === 17 || col === 18 || col === 19 && row >= startRow && y.source.getActiveSheet().getName() === ws ){
    y.source.getActiveSheet().getRange(row,20).setValue(new Date());
  }
 
 }
 
  function addTimeStampFields(z){
  // variables
  var startRow = 2;
  var ws = "Sheet3";

  //get modified row and column
  var row = z.range.getRow();
  var col = z.range.getColumn();

  if(col === 1 || col === 2 || col === 3 || col === 4 || col === 5 || col === 6 || col === 7 || col === 8 || col === 9 || col === 10 || col === 11 || col === 12 || col === 13 || col === 14 || col === 15 || col === 16 && row >= startRow && z.source.getActiveSheet().getName() === ws ){
    z.source.getActiveSheet().getRange(row,17).setValue(new Date());
  }
 
 }

How to use multiple OnEdit functions across multiple Google Sheets

2 Answers2