Trying to limit an existing script to a specific sheet

Question

My first issue is this, I have an items log sheet where I want to add and manage individual unique items in our inventory. I created a data validation dependent dropdown list for a main category and found out how to build a script to dynamically create a secondary category dropdown list based on the selected main category.

For Example: If cell B2 (Main Category) is set to Carabiner (based on data validation range on another sheet) THEN cell C2 (secondary Category) will dynamically create a dropdown list relative to the Carabiner main category (i.e. locking, non-locking)

That is simple enough if you only have one row to create the dropdown lists, but I wanted to be able to pick from a secondary category list in each row dependent on which was picked in the main category cell.

I found a video of a script that did just that and got it working just fine.

Now the problem is that the script runs data validation on every other sheet. How can I limit the script to only run on a specific sheet?

Here is the script:

function onEdit() {
// this line just refers to the current file var start = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var current = start.getActiveCell()

// var to refer to the worksheets -lists is where the data validation range will come from, and main is where we want to use that data validation range var list = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Indirect_Categ_Ranges") var main = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Items_Log");

// has the user selected a category? Refers to the column number in the Items_Log sheet where the user has picked the main category
if (current.getColumn()==2)
{
  //to copy the selected sub-category -the 2,1 is the row and column on the Indirect_Categ_Ranges sheet where this script will dynamically update the main category picked to define what the indirect function will display in the next column
  var choice = current.getValue()
  list.getRange(2,1).setValue(choice)

  //clear any validation -the 2,3,1000 looks to start clearing validation at row 2, column 3 and down for up to 1000 entries
  main.getRange(2,3,5000).clearDataValidations()

  // create the rule - var_point defines the offset number of rows and columns to shift to initiate the dynamic dependent dropdown list, the var_items defines where the to look for the range to build the dropdown list
  var point = current.offset(0,1)
  var items = list.getRange(2,2,50)
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(items,true).build();
  point.setDataValidation(rule)
}
}

Also, is there a way to have the clear validations to run no matter how many rows there are?

Answer 1

The function will run anytime there's an edit and there's nothing you can do to stop that. You can, instead, terminate execution preemptively if it's not the sheet you care about.

The event object tells you which range was edited. You can get that range's sheet to know which sheet was edited. If the name matches, then execute the other stuff.

function onEdit(e) {
  if (e.range.getSheet().getName() === 'Items_Log') {
    // Data validation
  }
}

It's not great practice to use .getActiveRange() or .getActiveSheet() when you want what was actually edited because there is a chance, however small, that the edited range may differ from the active range at the time of function execution.

Answer 2

Explanation:

You need to take advantage of the event object.

That object contains relevant information to the edits you make.

For example:

• e.source is equivalent to SpreadsheetApp.getActive()
• e.range is equivalent to .getActiveCell().

To run the code only for a particular sheet, in this case Items_Log, add a condition to check if the name of the active sheet matches that name:

if (current.getColumn()==2 && start.getName()=="Items_Log")

where start is the active sheet:

var start = e.source.getActiveSheet();

Solution:

 function onEdit(e) {
   var start = e.source.getActiveSheet();
   var current = e.range;
   var list = e.source.getSheetByName("Indirect_Categ_Ranges")
   var main = e.source.getSheetByName("Items_Log");

   if (current.getColumn()==2 && start.getName()=="Items_Log")
    {
      var choice = current.getValue()
      list.getRange(2,1).setValue(choice)      
      main.getRange(2,3,5000).clearDataValidations()
      var point = current.offset(0,1)
      var items = list.getRange(2,2,50)
      var rule = SpreadsheetApp.newDataValidation().requireValueInRange(items,true).build();
      point.setDataValidation(rule)
    }
}

Answer 3

Thanks for the help. after looking at your suggestions and trying a couple things, I found that simply adding the code:

&& start.getName()=="Items_Log")

To the end of the line:

if (current....

Worked and solved the issue.