How to Run Script on Multiple Spreadsheet Tabs

I am writing this to understand how to run a script on multiple tabs (worksheets) in one google spreadsheet file.

1. What I am trying to do with the script:

• when specific column (here, 'status' column) been edited, add an auto timestamp in the column right after 'status' (here, 'timestamp' column).

2. Background of all tabs in the spreadsheet:

• 6 tabs in the spreadsheet.
• 'status' and 'timestamp' are not always in the same column of every tabs. For example, on tab Apple, 'status' is in column A and 'timestamp' is in column B. However, on tab Banana, 'status' is in column C and 'timestamp' is in column D. These cannot be changed.

3. Problem that I am having:

   • Only the last script would work right after I save it.

   step1: write down code for tab Apple and save, script will work on Apple.

   step2: write down code for tab Banana (in the same script file, right after code for Apple) and save, script will work on Banana, but not working on Apple anymore.

4. What I have tried so far (not working):

• Separate code by creating multiple script files

5. Code that I am using:

   function onEdit(e) {
     addTimestamp(e);
   }
   
   function addTimestamp(e){
     var startRow = 2;
     var targetColumn = 24;
     var ws = "Apple";
   
     var row = e.range.getRow();
     var col = e.range.getColumn();
   
     if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws){
   
       var time = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
   
       e.source.getActiveSheet().getRange(row,25).setValue(time);  
       }
   }
   
   function addTimestamp(e){
     var startRow = 2;
     var targetColumn = 66;　
     var ws = "Banana"; 
   
     var row = e.range.getRow();
     var col = e.range.getColumn();
   
     if(col === targetColumn && row >= startRow && e.source.getActiveSheet().getName() === ws){
   
       var time = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
   
       e.source.getActiveSheet().getRange(row,67).setValue(time);
       }
   } 
   

• Assuming that status is the header value of the status column, you could programmatically find the column index for this header using getRange(a1Notation) and findIndex; there's no need to specify that explicitly for all your sheets (which can be feasible when there are only two sheets, but can become annoying with 6 sheets).
• You could also use Range.offset() in order to get the cell from the next column, instead of using getRange(row, column).

Code snippet:

Considering the previous points, your code could be greatly simplified:

function addTimestamp(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const statusColumn = sheet.getRange("1:1").getValues()[0]
                            .findIndex(header => header === "status") + 1;
  if (range.getColumn() === statusColumn && range.getRow() > 1) {
    const time = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
    range.offset(0,1).setValue(time);
  }
}

Update:

If you want to find the timestamp column by header name too, just replace this:

range.offset(0,1).setValue(time);

With this:

const timestampColumn = sheet.getRange("1:1").getValues()[0]
                             .findIndex(header => header === "timestamp") + 1;
sheet.getRange(range.getRow(), timestampColumn).setValue(time);