TypeError：無法讀取未定義的屬性“范圍”（第 7 行，文件“multipledatavalidation”）
第 7 行；如果 (exclude.includes(e.range.getSheet().getName())) { 返回;
應該注意的是，我的工作表上還有另一個腳本在運行。一個可以讓我在鎖定到位的情況下復制工作表。我希望能夠在我制作的原始工作表的所有副本上運行此代碼。這是我的工作表的共享、可編輯版本的鏈接。
var optionsWsName = "Backend-Prices";
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var exclude = ["BackendImportedData", "donotedit", "BackendDataSort", "09/20_DS7"]; // add sheet names as desired
// function to update each time the file is Edited
function onEdit(e){
if (exclude.includes(e.range.getSheet().getName())) {
return;
}
var Options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1, 5).getValues();
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
if (r <= 8) return;
var c = activeCell.getColumn();
var FirstLevelColumn = 1;
var SecondLevelColumn = 2;
var ThirdLevelColumn = 3;
var FourthLevelColumn = 4;
if(c === FirstLevelColumn){
applyFirstLevelValidation(val, r);
} else if(c === SecondLevelColumn){
applySecondLevelValidation(val, r);
} else if(c === ThirdLevelColumn){
applyThirdLevelValidation(val, r);
}
}//end onEdit
// function for second level of data validation to work correctly
function applyFirstLevelValidation(val, r){
if(val === ""){
ws.getRange(r, SecondLevelColumn).clearContent();
ws.getRange(r, SecondLevelColumn).clearDataValidations();
ws.getRange(r, ThirdLevelColumn).clearContent();
ws.getRange(r, ThirdLevelColumn).clearDataValidations();
ws.getRange(r, FourthLevelColumn).clearContent();
ws.getRange(r, FourthLevelColumn).clearDataValidations();
} else {
ws.getRange(r, SecondLevelColumn).clearContent();
ws.getRange(r, SecondLevelColumn).clearDataValidations();
ws.getRange(r, ThirdLevelColumn).clearContent();
ws.getRange(r, ThirdLevelColumn).clearDataValidations();
ws.getRange(r, FourthLevelColumn).clearContent();
ws.getRange(r, FourthLevelColumn).clearDataValidations();
var filteredOptions = Options.filter(function(o){ return o[0] === val });
var listToApply = filteredOptions.map(function(o){ return o[1] });
var cell = ws.getRange(r, SecondLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
// function for third level of data validation to work correctly
function applySecondLevelValidation(val, r){
if(val === ""){
ws.getRange(r, ThirdLevelColumn).clearContent();
ws.getRange(r, ThirdLevelColumn).clearDataValidations();
ws.getRange(r, FourthLevelColumn).clearContent();
ws.getRange(r, FourthLevelColumn).clearDataValidations();
} else {
ws.getRange(r, ThirdLevelColumn).clearContent();
ws.getRange(r, ThirdLevelColumn).clearDataValidations();
ws.getRange(r, FourthLevelColumn).clearContent();
ws.getRange(r, FourthLevelColumn).clearDataValidations();
var firstlevelColValue = ws.getRange(r, FirstLevelColumn).getValue();
var filteredOptions = Options.filter(function(o){ return o[0] === firstlevelColValue && o[1] === val });
var listToApply = filteredOptions.map(function(o){ return o[2] });
var cell = ws.getRange(r, ThirdLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
// function for fourth level of data validation to work correctly
function applyThirdLevelValidation(val, r){
if(val === ""){
ws.getRange(r, FourthLevelColumn).clearContent();
ws.getRange(r, FourthLevelColumn).clearDataValidations();
} else {
ws.getRange(r, FourthLevelColumn).clearContent();
var firstlevelColValue = ws.getRange(r, FirstLevelColumn).getValue();
var secondlevelColValue = ws.getRange(r, SecondLevelColumn).getValue();
var filteredOptions = Options.filter(function(o){ return o[0] === firstlevelColValue && o[1] === secondlevelColValue && o[2] === val });
var listToApply = filteredOptions.map(function(o){ return o[3] });
var cell = ws.getRange(r, FourthLevelColumn);
applyValidationToCell(listToApply,cell);
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule)
}
