How to run a different script on multiple Google Sheet tabs?

Question

The code writes an auto email once a trigger is met in the sheet.

There are 3 different tabs pulled from the main sheet and each tab should run the script but with a different message for each tab.

When I duplicate the script and change the tab variable only the last script runs.

Should each tab have its own script or do I need to combine the variables into one script?

Here's the code:

// This constant is written in column E for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails2() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('CAN WE R - HS');
  var startRow = 3; // First row of data to process
  var numRows = 1000; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 10)
   // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[1]; // Second column
    var message1 = 'HS Co 1 / HS co 2 / HS co 3';
    var emailSent = row[4]; // Sixth column
    if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
      var subject1 = 'Sending emails from a Spreadsheet CAN WE R - HS';
      MailApp.sendEmail(emailAddress, subject1, message1);
      sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT)
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}
var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails2() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('CAN WE R - DRAWING');
  var startRow = 3; // First row of data to process
  var numRows = 1000; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 10)
   // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[1]; // Second column
    var message1 = 'drawing Co 1 / drawing co 2';
    var emailSent = row[4]; // Sixth column
    if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
      var subject1 = 'Sending emails from a Spreadsheet CAN WE R - drawing';
      MailApp.sendEmail(emailAddress, subject1, message1);
      sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT)
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Answer 1

You have an installable onEdit() trigger referencing script name "sendEmails2". There are three sheets that the script can apply to, but the script as written is sheet specific. When you edit duplicate the script and update attributes for the second sheet, you find that only the second script is running.

There are several reasons that only the second script executes:
1 - the onEdit trigger can only reference one script. Even if you had three scripts, one for sheet, onedit can only reference one sheet.
2 - the scripts each have the same name. Google sheets can only run one OR the other.
3 - the scripts do not daisy chain to each other.

The solution is:

• develop a single script
• include the names of the key sheets in the script (as variables - as in the answer, or even in an array accessible by the script).
• include the subject and message values of each sheet as variables.
• allow the script to compare the name of the edited sheet to the names of the key sheets and execute accordingly.

There are many ways in which the code for this answer might have been written. Please consider this as one solution to your question.

---

function sendEmails2() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // names of the three tabs
  var sheetname01 = "CAN WE R - HS";
  var sheetname02 = "CAN WE R - DRAWING";
  var sheetname03 = "CAN WE R - third";
 
  // assume there are many sheets and we want the trigger to 
  // execute if the edit is on one of three specific sheets   
 if ((sheet.getSheetName() === sheetname01) || (sheet.getSheetName() === sheetname02) || (sheet.getSheetName() === sheetname03)){
   // sent email
   // Logger.log("DEBUG: do something");
   
   // populate the subject and message fields
   if (sheet.getSheetName() === sheetname01){
     var message1 = 'HS Co 1 / HS co 2 / HS co 3';
     var subject1 = 'Sending emails from a Spreadsheet CAN WE R - HS';
   } else if (sheet.getSheetName() === sheetname02){
     var message1 = 'drawing Co 1 / drawing co 2';
     var subject1 = 'Sending emails from a Spreadsheet CAN WE R - drawing';
   } else if (sheet.getSheetName() === sheetname02){
     var message1 = 'stuff  1 / stuff 2';
     var subject1 = 'Sending emails from a Spreadsheet CAN WE R - third';
   } // end if set message and subjects
   
   // This constant is written in column E for rows for which an email
   // has been sent successfully.
   var EMAIL_SENT = 'EMAIL_SENT';
   
   // get the data
   var startRow = 3; // First row of data to process
   var numRows = 1000; // Number of rows to process
   var dataRange = sheet.getRange(startRow, 1, numRows, 10)
   // Fetch values for each row in the Range.
   var data = dataRange.getValues();
   for (var i = 0; i < data.length; ++i) {
     var row = data[i];
     var emailAddress = row[1]; // Second column
     var message1 = 'HS Co 1 / HS co 2 / HS co 3';
     var emailSent = row[4]; // Sixth column
     if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
       var subject1 = 'Sending emails from a Spreadsheet CAN WE R - HS';
       MailApp.sendEmail(emailAddress, subject1, message1);
       sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT)
       // Make sure the cell is updated right away in case the script is interrupted
       SpreadsheetApp.flush();
       
     } // end if email sent
   }// end loop through data
 }// end IF sheet condition  
  return;
}

---

On a tangent... onEdit scripts are often designed to take advantage of Event Objects. You did not explain the full details of the trigger (other than onEdit), but possibly Event Objects might be used to make the script more dynamic.