google sheets - How do I make Last Edit timestamp specific to each tab, with multiple tabs to be added in future?

Question

UPDATE: Got this to work with the following script:

function onEdit(e) {

var range = e.range;
var sheet = range.getSheet();
var name = sheet.getName();
var currentDate = new Date();

  //variable
  var startRow = 1;
  
  //get modified row and column
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var currentDate = new Date();
  
  if(col === 7 && row === 3 && sheet.getRange(1,14).getValue() == "" && 
     sheet.getName() != "4-Transfer Walkthru" && 
     sheet.getName() != "1-Cover Sheet" && 
     sheet.getName() != "2-Inventory" && 
     sheet.getName() != "3-Template"){
         sheet.getRange(1,14).setValue(currentDate);
       }
  
  else if(row >= startRow){
    
    sheet.getRange(2,14).setValue(currentDate);
       
  }  
}

Original Post: I'm attempting to add a "last edited" timestamp to a cell within each tab of a doc that has many tabs, and will continue to have more tabs added/removed. Everything I've found so far either creates a timestamp whenever anything within the entire doc is edited, or it requires listing out each tab name in the script (which is not ideal as the tabs in this doc are ever-changing).

Is there a way to make edit timestamps specific to each tab, without having to list out each tab's name?

Here's what I have so far, which included a timestamp for when the tab is first created (run by any edits made to cell J3), as well as the timestamp that updates across all tabs any time anything is edited in entire doc:

function onEdit(e) {

  addTimestamp(e);
  
}


function addTimestamp(e){
  //variable
  var startRow = 1;
  
  //get modified row and column
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var currentDate = new Date();
  
  if(col === 7 && row === 3 && e.source.getActiveSheet().getRange(1,14).getValue() == ""){
         e.source.getActiveSheet().getRange(1,14).setValue(currentDate);
       }
  
  if(row >= startRow && 
     e.source.getActiveSheet().getName() != "Transfer Walkthru" && 
     e.source.getActiveSheet().getName() != "Cover Sheet" && 
     e.source.getActiveSheet().getName() != "Inventory" && 
     e.source.getActiveSheet().getName() != "Template"){
    
    e.source.getActiveSheet().getRange(2,14).setValue(currentDate);
       
  }
  
}

ETA: Example sheet

Thank you!

Answer 1

onEdit allows you to access the event object range from which you cn deduct the sheet where the edit took place

Then you can implement conditional statements into your code to specify what shall happen depending on which sheet has been edited.

The problem with your code is probably due to e.source.getActiveSheet() because the active sheet might not always be the edited sheet.

I recommend you to retrieve the sheet with e.range.getSheet() instead.

Sample:

function onEdit(e) {

var range = e.range;
var sheet = range.getSheet();
var name = sheet.getName();
var currentDate = new Date();

  //variable
  var startRow = 1;
  
  //get modified row and column
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var currentDate = new Date();
  
  if(col === 7 && row === 3 && sheet.getRange(1,14).getValue() == ""){
         sheet.getRange(1,14).setValue(currentDate);
       }
  
  else if(row >= startRow && 
     sheet.getName() != "Transfer Walkthru" && 
     sheet.getName() != "Cover Sheet" && 
     sheet.getName() != "Inventory" && 
     sheet.getName() != "Template"){
    
    sheet.getRange(2,14).setValue(currentDate);
       
  }  
}