Can I have onEdit work on only one sheet?

Question

I am trying to setup dynamic, dependent, dropdown data validation lists. The intent is that when I select an item from a list in column A, the dropdown list in column B will be changed automatically to reflect possible options.

Even though I am new to coding, I have managed to get this part working on the sheet I want.

However, when I make any change on another sheet, well, it also makes changes to that other sheet. I need this script to work on only 1 sheet and can't figure out how.

It seems people have been running in similar issues here, but I haven't been able to adapt their solutions to my situation.

I have also tried also to replace the getActiveSheet by getSheetByName with the sheet i want changed but it doesn't seem to work.

function onEdit() {

var activecell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sectors");


if(activecell.getColumn() == 3 && activecell.getRow() >1 ) {

activecell.offset(0, 1).clearContent().clearDataValidations(); 

    if(activecell.isBlank()){

 }
    var sector1 = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();  
    var sector1Index = sector1[0].indexOf(activecell.getValue()) +1 ;  


    var validationRange1 = datass.getRange(2, sector1Index, 25);   
    var validationRule1 =  SpreadsheetApp.newDataValidation().requireValueInRange(validationRange1); 

    activecell.offset(0, 1).setDataValidation(validationRule1)

    }    
}

when I make any change on another sheet, well, it also makes changes to that other sheet. I need this script to work on only 1 sheet and can't figure out how.

Thank you

Take a look at this [answer](https://stackoverflow.com/a/54028228/7215091) — Cooper, Jan 03 '19 at 20:15
Related [1](https://stackoverflow.com/q/10986277/1595451), [2](https://stackoverflow.com/q/32622217/1595451), [3](https://stackoverflow.com/q/27107903/1595451) — Rubén, Jan 03 '19 at 21:15
@Cooper Thank you for your help. I've been trying to integrate your other response in here, but I'm probably missing something as I still can't have this work. I'm getting this error : Cannot read property "range" from undefined — Tam, Jan 03 '19 at 23:06
I was able to resolve my issue with the following: 'code' function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if (s.getName() == 'Dealflow') { — Tam, Jan 03 '19 at 23:22
If you use the event object in your code you can get it with var `s=e.range.getSheet();` — Cooper, Jan 04 '19 at 01:04

score 0 · Answer 1 · edited Jan 27 '19 at 13:45

Not sure if this will help in your situation, but I have used a combination of Data Validation rules and Filter formulas to achieve a similar result in the past. My solution does not utilize App Script, but it may be a simpler way of solving your problem. In my case, I utilize three separate sheets.

Sheet1 ("VIEW") contains the dropdown selection fields and results,
Sheet2 ("FILTER") is the filtered column data which responds to the entries from Sheet1, and
Sheet3 ("DATA") is the raw data which you are attempting to filter.

Essentially, you are using inputs from the first dropdown on Sheet1 to filter the data on Sheet2, and you are using the filtered data on Sheet2 to populate the selections for the next dropdown list.

I have linked a Sheet which demonstrates this concept with sample data.

Notice the Filter formulas in 'FILTER'!A3 and 'FILTER'!B3, they are the key to making this work. You then setup your data validation rules on cells "VIEW!A2" and "VIEW!B2" to select from Ranges "FILTER"!A2:A and "FILTER"!B2:B, respectively.

I include static values labeled "SHOW ALL" at the top of each filter column which allow the user to set that column as a wildcard. Depending on your use-case, this may or may not be useful.

Please note that accommodations need to be made for these values in your filter formula if they are used. I use an IF statement which shows all values where the opposing variable is found in the corresponding column.

Sorry if this is an overly simplified (or complicated) method, but I hope it is helpful. I have used similar configurations to support very large sets of data, and they are generally easy to manage.

https://docs.google.com/spreadsheets/d/1GpvSVRmXLkrtr2VFZmxyZXQjLWK8nrt3DvpNc6spLDg/edit?usp=sharing

Can I have onEdit work on only one sheet?

1 Answers1