AppScript筛选函数对含特殊字符的RI No.无响应问题排查

问题描述 #

我有一个包含Group、RI No.、Supplier三列的电子表格，实现了三级联动下拉：选择Group后生成对应RI No.的下拉，选择RI No.后生成对应Supplier的下拉。但选择部分含“-”的RI No.（如6181-1、Cu-18mm、CU-25x3等）时，Supplier下拉无选项，实际应有2个选项；但6310-1这类带“-”的选项却能正常显示供应商。

原因分析 #

问题出在数据类型/格式不匹配导致的全等比较失败：

• 选择RI No.时用getDisplayValue()获取的是字符串类型的显示值
• 从数据源supplier details表用getValues()获取的RI No.（o[4]）可能因单元格格式问题，被解析为非字符串类型（如数字、日期对象），或者存在大小写、前后空格差异
• 全等运算符===会同时比较值和类型，类型不匹配时直接返回false，导致过滤结果为空

解决方案 #

统一比较逻辑：将数据源中的值和选中值都转换为字符串，去除前后空格，并统一大小写（避免大小写差异导致的匹配失败），再进行比较。

修改后的代码 #

var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var wsopt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sorted items");
var opts = wsopt.getRange(2,1,wsopt.getLastRow()-1,13).getValues();
var firstlevelcolumn = 2;
var secondlevelcolumn = 5;
var thirdlevelcolumn= 6;
var wsopt2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("supplier details");
var opts2 = wsopt2.getRange(3,1,wsopt2.getLastRow()-1,12).getValues();


function onEdit(e)
{ var activecell= e.range;
  var val = activecell.getDisplayValue();
  var r= activecell.getRow();
  var c= activecell.getColumn();
  var wsName= activecell.getSheet().getName();
  if (wsName == "JPS")
  {
    if (c == firstlevelcolumn && r > 2)
    { applyFirstLevelValidation(val,r);}
    if (c == secondlevelcolumn && r > 2)
    { applySecondLevelValidation(val,r);}
  }//JPS
  else if (wsName == "DDNI")
  {
    if (c == firstlevelcolumn && r > 2)
    {applyFirstLevelValidation(val,r);}
    if (c == secondlevelcolumn && r > 2)
    { applySecondLevelValidation(val,r);}
  }//DDNI
}//close onEdiT

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();
    } else
    {
      ws.getRange(r,secondlevelcolumn).clearContent();
      ws.getRange(r,secondlevelcolumn).clearDataValidations();
      ws.getRange(r,thirdlevelcolumn).clearContent();
      ws.getRange(r,thirdlevelcolumn).clearDataValidations();
      var filteredopts = opts.filter(function(o){
        return String(o[0]).trim().toLowerCase() === val.trim().toLowerCase();
      });
      var listToApply = filteredopts.map(function(o){return o[1]});
      var cell=ws.getRange(r,secondlevelcolumn);
      applyValidationToCell(listToApply,cell);
    }//close else
}//close applyFirstLevelValidation

function applySecondLevelValidation(val,r)
{
  if(val=== "")
    {
      ws.getRange(r,thirdlevelcolumn).clearContent();
      ws.getRange(r,thirdlevelcolumn).clearDataValidations();
    } else
    {
      ws.getRange(r,thirdlevelcolumn).clearContent();
      ws.getRange(r,thirdlevelcolumn).clearDataValidations();
      var firstlevelcolvalue = ws.getRange(r,firstlevelcolumn).getDisplayValue();
      var filteredopts2 = opts2.filter(function(o){
        // 统一转换为字符串、去除空格、统一大小写后比较
        var riNo = String(o[4]).trim().toLowerCase();
        var selectedRiNo = val.trim().toLowerCase();
        var group = String(o[6]).trim().toLowerCase();
        var selectedGroup = firstlevelcolvalue.trim().toLowerCase();
        return group === selectedGroup && riNo === selectedRiNo;
      });
      var listToApply2 = filteredopts2.map(function(o){return o[0]});
      var cell2=ws.getRange(r,thirdlevelcolumn);
      applyValidationToCell(listToApply2,cell2);
    }//close else
}

function applyValidationToCell(list,cell)
{
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
  cell.setDataValidation(rule);
}

额外建议：检查supplier details表中RI No.列的单元格格式，统一设置为文本格式，避免Google Sheets自动解析为其他类型。

内容的提问来源于stack exchange，提问作者Anushka