Get Sheet By Name

Question

I'm trying to get the following formula to work:

function setDataValid(range, sourceRange) {   var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();   range.setDataValidation(rule); }  function onEdit() {   var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();   var aColumn = aCell.getColumn();    if (aColumn == 2 && SpreadsheetApp.getActiveSheet().getName() == 'Local' ) {     var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);     var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());     setDataValid(range, sourceRange)   } } 

When debugging onEdit() it shows that sourceRange in setDataValid(range, sourceRange) is null. As the range is in my sheet 'Local' I'm trying to change the getActiveSpreadsheet() to a get spreadsheet by name. Anyone who can help ?

Answer 1

Use get sheet by name on the spreadsheet:

var sheet = SpreadsheetApp.getActive().getSheetByName('Local') 

Answer 2

Its very simple just get the sheet by name, the syntax is as follows as per the documentation.

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expenses");