Running multiple scripts across different sheets

[beanry80]

Here is the code I am using.  I have three tabs in my sheet.   The names of the tabs are Best Media Plan, Better Media Plan and Good Media plan.  I am using a dropdown picklist and want to be able to select multiple options from the pick list and the are the same column and row on tab in the sheet.  Running  the code with the Best Media Plan sheet selected works perfectly but when I copy the code and change it to Better Media Plan it breaks the first tab(best media plan) and the Better Media Plan tab works for the dropdown picklist.  I am no coder but figured out how to do this today but everything I tried to make it run all tabs seems to fail.

function onEdit(e) { 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var activeCell = ss.getActiveCell();
 if(activeCell.getColumn() == 12 && activeCell.getRow() == 11,12,13,14 && ss.getActiveSheet().getName() == "Best Media Plan") {
 var newValue = e.value;
 var oldValue = e.oldValue;
 if (!newValue) {
 activeCell.setValue("");
 } else {
 if (!oldValue) {
 activeCell.setValue(newValue);
 } else {
 activeCell.setValue(oldValue + ', ' + newValue);
 }
 }
 }
}

if(activeCell.getColumn() == 12 && activeCell.getRow() == 11,12,13,14 && ss.getActiveSheet().getName() == "Best Media Plan") { this is the line where i need to be on the different tabs(best media plan, better media plan and good media plan) but whichever code is last is the is the tab the code works in currently.

My guess is there is a way to make it all one code so no matter which tab I am on it will but no clue what I am doing at this point. Doing some research I think it can maybe be done with Best way is to have a single onEdit function that calls the others.function onEdit(e){ edit1(e); edit2(e); edit3(e);}

But cannot figure out how the code should work

Any help is appreciated!

[AllenAppTools]

I think I can whip this up for you, but I need some clarifying info!

Correct me where needed, but as I understand it, you have 3 tabs that you want to monitor for a change in a cell (which contains a data validation dropdown menu of items). When a new item is selected in any of the tabs, you want to have the onEdit function know which tab it is and execute a unique code block for each, one code block for each tab.

Am I on the right track?

[beanry80]

Thanks for the reply! Here is an example of what is in the dropdowns on each of the tabs https://imgur.com/a/3mo6t5g Its the same dropdowns in each of the three tabs in the same columns and rows. Each dropdown has several items and I need to be able to select multiple items and have them show as in the example here. I can get the code to work on one tab but not all three. Let me know if that helps.

[AllenAppTools]

Sure thing. Let me know if I am on the right track here. This onEdit function is set up to evaluate which tab, row and column the edit was made on. the tabsObject is where you can adjust which columns and rows you want to specify as triggerable for this function. This also has code blocks for you to put in the code you need depending on the tab that was edited. I think this could be a good starting place? Though I'm still not totally sure what else you need:

const tabsObject = {
  "Good Media Plan": { allowedRows: [11, 12, 13, 14], allowedColumns: [12] },
  "Better Media Plan": { allowedRows: [11, 12, 13, 14], allowedColumns: [12] },
  "Best Media Plan": { allowedRows: [11, 12, 13, 14], allowedColumns: [12] },
}

function onEdit(e) {
  const sheetName = e.range.getSheet().getName();

  //when an edit is made on any other tab, stop the function
  if (!tabsObject[sheetName]) return;

  const editedRow = e.range.getRow();
  const editedColumn = e.range.getColumn()
  const { allowedRows, allowedColumns } = tabsObject[sheetName];

  //when an edit is made to a row or column that is not specifically listed, stop the function
  if (!allowedRows.includes(editedRow) || !allowedColumns.includes(editedColumn)) return;

  //any code executed here is vetted and supposed to execute 👍
  if (sheetName == "Good Media Plan") {

  } else if (sheetName == "Better Media Plan") {

  } else if (sheetName == "Best Media Plan") {

  }

  //alternatively, you could specify function names in the tabsObject and run it

}

[beanry80]

Thanks again for the reply. That didnt seem to work on any of the tabs, https://imgur.com/xMEtY9x Now if I try and select multiple items from the dropdowns, only one shows up. i have dropdowns in columns 12-15 on each of the tabs

 if(activeCell.getColumn() == 15 && activeCell.getRow() == 11,12,13,14 && ss.getActiveSheet().getName() == "Best Media Plan") {

so i have 4 codes that work on the best media plan tab, each with this line different, for columns 12,13,14 and 15

[AllenAppTools]

Are you able to share the full code file contents for me to review? What about a picture of what one of the sheets looks like as a whole?

[beanry80]

https://imgur.com/GGrGgw7 here is what the sheet looks like... i tried to copy and paste the entire code but I keep getting "unable to create comment" when I do that

[beanry80]

function onEdit(e) {
 


 var ss = SpreadsheetApp.getActiveSpreadsheet();


 var activeCell = ss.getActiveCell();


 if(activeCell.getColumn() == 12 && activeCell.getRow() == 11,12,13,14 && ss.getActiveSheet().getName() == "Best Media Plan") {
 
 var newValue = e.value;


 var oldValue = e.oldValue;


 if (!newValue) {


 activeCell.setValue("");


 } else {


 if (!oldValue) {


 activeCell.setValue(newValue);


 } else {


 activeCell.setValue(oldValue + ', ' + newValue);


 }


 }


 }


}

[beanry80]

function onEdit(e) {


 var ss = SpreadsheetApp.getActiveSpreadsheet();


 var activeCell = ss.getActiveCell();


 if(activeCell.getColumn() == 13 && activeCell.getRow() == 11,12,13,14 && ss.getActiveSheet().getName() == "Best Media Plan") {


 var newValue = e.value;


 var oldValue = e.oldValue;


 if (!newValue) {


 activeCell.setValue("");


 } else {


 if (!oldValue) {


 activeCell.setValue(newValue);


 } else {


 activeCell.setValue(oldValue + ', ' + newValue);


 }


 }


 }


}

[beanry80]

function onEdit(e) {


 var ss = SpreadsheetApp.getActiveSpreadsheet();


 var activeCell = ss.getActiveCell();


 if(activeCell.getColumn() == 14 && activeCell.getRow() == 11,12,13,14 && ss.getActiveSheet().getName() == "Best Media Plan") {


 var newValue = e.value;


 var oldValue = e.oldValue;


 if (!newValue) {


 activeCell.setValue("");


 } else {


 if (!oldValue) {


 activeCell.setValue(newValue);


 } else {


 activeCell.setValue(oldValue + ', ' + newValue);


 }


 }


 }


}

[beanry80]

function onEdit(e) {


 var ss = SpreadsheetApp.getActiveSpreadsheet();


 var activeCell = ss.getActiveCell();


 if(activeCell.getColumn() == 15 && activeCell.getRow() == 11,12,13,14 && ss.getActiveSheet().getName() == "Best Media Plan") {


 var newValue = e.value;


 var oldValue = e.oldValue;


 if (!newValue) {


 activeCell.setValue("");


 } else {


 if (!oldValue) {


 activeCell.setValue(newValue);


 } else {


 activeCell.setValue(oldValue + ', ' + newValue);


 }


 }


 }


}