javascript - 显示错误，数据验证规则的项目数超过了 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);            
    }      
  } 
  }

标签： javascriptgoogle-apps-scriptgoogle-sheets

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);            
    }      
  } 
  }