显示错误时，数据验证规则包含的项目数超过了500的限制.请改用范围条件中的列表

Question

以下代码显示由于限制为500而导致的错误。我试图解决这个问题，并查阅了许多文档，但都失败了。有什么办法解决吗？

var spreadsheet = SpreadsheetApp.getActive();  
var dashboard = spreadsheet.getSheetByName("Dashboard");
var wsOptions = spreadsheet.getSheetByName("Master");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1,6).getDisplayValues();

function onEdit(e){
var as = e.source.getActiveSheet();
var val = e.range.getValue();
var val_not = e.range.getA1Notation();

if (val_not =='F5' && as.getName() == "Dashboard"){

if(val === "All"){
dashboard.getRange('G5').setValue(new Date()).setNumberFormat("yyyy-mm-dd");
dashboard.getRange('G5').clearDataValidations();      
}
else{
var filteredOptions = options.filter(function(o){return o[5] === val});
var listToApply = filteredOptions.map(function(o){return o[0]}).sort().reverse();

var cell = dashboard.getRange('G5');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).setAllowInvalid(false).build();
cell.clearContent();
cell.clearDataValidations();
cell.setDataValidation(rule);            
}      
} 
}

Answer 1

解释：

正如错误所示：

数据验证规则的项数超过了500的限制。使用改为"从范围中列出"条件。

您应该使用requireValueInRange((。

为了使用后者，您需要定义范围的数据验证项。在下面的方法中，我创建了一系列这些项opt_range，并将其用作requireValueInRange()函数的参数。

解决方案1:

function onEdit(e){      
var as = e.source.getActiveSheet();
var val = e.range.getValue();
var val_not = e.range.getA1Notation(); 

var spreadsheet = SpreadsheetApp.getActive();  
var dashboard = spreadsheet.getSheetByName("Dashboard");
var wsOptions = spreadsheet.getSheetByName("Master");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1,6).getDisplayValues();


if (val_not =='F5' && as.getName() == "Dashboard"){

if(val === "All"){
dashboard.getRange('G5').setValue(new Date()).setNumberFormat("yyyy-mm-dd");
dashboard.getRange('G5').clearDataValidations();      
}
else{
var filteredOptions = options.filter(function(o){return o[5] === val});
var listToApply = filteredOptions.map(function(o){return o[0]}).sort().reverse();
var listToApply2D = listToApply.map(ta=>[ta]);       
var jSize = wsOptions.getRange('J:J').getValues().filter(String).length;

if (jSize>0){ wsOptions.getRange(1,10,jSize,1).clearContent()};
wsOptions.getRange(1,10,listToApply2D.length,listToApply2D[0].length).setValues(listToApply2D);
var opt_range = wsOptions.getRange(1,10,listToApply2D.length,listToApply2D[0].length);        
var cell = dashboard.getRange('G5');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(opt_range).build();
cell.clearContent();
cell.clearDataValidations();
cell.setDataValidation(rule);            
}      
} 
}    

解决方案2(推荐(：

假设你的列表中没有500个唯一的项目，你仍然可以使用你发布的代码，但使用项目的唯一列表：

function onEdit(e){
var spreadsheet = SpreadsheetApp.getActive();  
var dashboard = spreadsheet.getSheetByName("Dashboard");
var wsOptions = spreadsheet.getSheetByName("Master");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1,6).getDisplayValues();
var as = e.source.getActiveSheet();
var val = e.range.getValue();
var val_not = e.range.getA1Notation();


if (val_not =='F5' && as.getName() == "Dashboard"){

if(val === "All"){
dashboard.getRange('G5').setValue(new Date()).setNumberFormat("yyyy-mm-dd");
dashboard.getRange('G5').clearDataValidations();      
}
else{
var filteredOptions = options.filter(function(o){return o[5] === val});
var listToApply = filteredOptions.map(function(o){return o[0]}).sort().reverse();
var uniqueList = listToApply.filter((v, i, a) => a.indexOf(v) === i); // <= New code

var cell = dashboard.getRange('G5');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(uniqueList).setAllowInvalid(false).build();
cell.clearContent();
cell.clearDataValidations();
cell.setDataValidation(rule);            
}      
} 
}

Answer 2

不要使用requireValueInList，而是使用requireValueInRange。这意味着您应该将列表中的值添加到一个范围中。

资源

• https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder#requireValueInRange(范围(
• https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder#requirevalueinrangerange，-showdropdown