之前先建立第一層第二層的連動文件

google資料驗證設定後，下拉選單連動都沒問題

在(B2:B)驗證資料，出處和Pay都可以連動第二層的顯示

但我想要指定(D10),驗證資料pay卻帶不出來

為何呢?

附連結
https://docs.google.com/spreadsheets/d/1Qam4vvoqc72rTOAucUUynGba-LNK-jJxXAbMiWZCVUc/edit?usp=sharing

指令碼編輯器如下

function depDrop_(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 == 1 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
else if (aColumn == 2 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}

}