Get Sheet By Name
Google Apps-ScriptGoogle SheetsGoogle Apps-Script Problem Overview
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 ?
Google Apps-Script Solutions
Solution 1 - Google Apps-Script
Use get sheet by name on the spreadsheet:
var sheet = SpreadsheetApp.getActive().getSheetByName('Local')
Solution 2 - Google Apps-Script
Its very simple just get the sheet by name, the syntax is as follows as per the documentation.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Expenses");
Solution 3 - Google Apps-Script
Please check the statement
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
This should be the one returning null. For the cause look at the official documentation here.
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Local!TaxRates')
should get the range.