Auto Copy & Paste to different tabs depending on which option is selected from a dropdown menu

Question

I'm pretty new to Coding but I'm really struggling with this issue. I need the sheet to auto Copy & Paste to different tabs depending on which option is selected from a dropdown menu. I can get one to work perfectly with the code below:

var sourceSheet = "100 List"
var destinationSheet = "Warm Leads"
var check = {
 "col":1,
 "changeVal": "Warm Lead",
 };
var pasteRange = {
 "start": 1,
 "cols": 20
 };
function onEdit() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet()
  if(sheet.getName() === sourceSheet){
   //Get active cell
   var cell = sheet.getActiveCell();
   var cellCol = cell.getColumn();
   var cellRow = cell.getRow();
  
   if(cellCol === check.col){
     if(cell.getValue() === check.changeVal){
     
       var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
      
       var pasteDestination = ss.getSheetByName(destinationSheet);
       var pasteEmptyBottomRow = pasteDestination.getLastRow () +1;
      
       exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
                          SpreadsheetApp.CopyPasteType.PASTE_VALUES);
   
       };
     };
   };
 };

However, when I create a separate file, with the alternative version for a different drop-down option only the most recently entered will work on the sheet.

This is the alternate version. Both work separately.

var sourceSheet = "100 List"
var destinationSheet = "Conversations"
var check = {
"col":1,
"changeVal": "DNC",
};
var pasteRange = {
"start": 1,
"cols": 20
};
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
 if(sheet.getName() === sourceSheet){
  //Get active cell
  var cell = sheet.getActiveCell();
  var cellCol = cell.getColumn();
  var cellRow = cell.getRow();
 
  if(cellCol === check.col){
    if(cell.getValue() === check.changeVal){
    
      var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
     
      var pasteDestination = ss.getSheetByName(destinationSheet);
      var pasteEmptyBottomRow = pasteDestination.getLastRow () +1;
     
      exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
                         SpreadsheetApp.CopyPasteType.PASTE_VALUES);
  
      };
    };
  };
};

I have tried creating two separate edit requests similar to below and putting them both on the same file but I could still only get one function to run.

function onEdit()
{
 onEdit1(); 
 onEdit2();
}

Any help would be really appreciated as I feel like I'm now going round and round in circles.

user_1595451 · Answer

In a Google Apps Script project all the files share the same global scope, meaning that variable and function names should have unique names across all files.

In other words, while renaming each onEdit function to give to each of them an unique name is a step in the right direction you have to do the same with the global variables:

destinationSheet
check

Regarding sourceSheet and pasteRange since them have the same value in both files, it's better to keep one declaration of each of this variables and delete the others.

user_14606046 · Answer

Not sure if your main reason for introducing variables check and pasteRange so that your code could be modified/managed easily since this could be easily resolved by adding if-else condition. But if you want to stick with your current design, you can refer to the sample code below.

Here is a sample modified code:

var sourceSheet = "100 List"
var check = {
"col":1,
"changeVal": ["DNC", "Warm Lead"],
"destinationSheet": ["Conversations", "Warm Leads"]
};
var pasteRange = {
"start": 1,
"cols": 20
};

function onEdit() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet()

  if(sheet.getName() === sourceSheet){
   //Get active cell
   var cell = sheet.getActiveCell();
   var cellCol = cell.getColumn();
   var cellRow = cell.getRow();
  
   if(cellCol === check.col){
     if(check.changeVal.includes(cell.getValue())){
       var index = check.changeVal.indexOf(cell.getValue());
       var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);
       Logger.log(index);
       Logger.log(check.destinationSheet[index]);
       var pasteDestination = ss.getSheetByName(check.destinationSheet[index]);
       var pasteEmptyBottomRow = pasteDestination.getLastRow () +1;
      
       exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
                          SpreadsheetApp.CopyPasteType.PASTE_VALUES);
   
       };
     };
   };
 };

What did I do?

I combined all your possible option on a single variable check.changeVal using arrays inside a JavaScript Object.
I combined all your matching destination sheet on a single variable check.destinationSheet using arrays inside a JavaScript Object.
When checking the cell value, I used Array.prototype.includes() to check if the current cell value exists in check.changeVal array.

if(check.changeVal.includes(cell.getValue())){
I get the index where the cell value exist in check.changeVal array using Array.prototype.indexOf().

var index = check.changeVal.indexOf(cell.getValue());
I accessed the matching destination sheet based on the check.changeVal selected using the index obtained in step 4.

var pasteDestination = ss.getSheetByName(check.destinationSheet[index]);

OUTPUT:

Auto Copy & Paste to different tabs depending on which option is selected from a dropdown menu

2 Answers

What did I do?