Google Apps Script实现双参数过滤的HTML联动下拉菜单问题咨询

核心错误点：

• 全局变量customerHtmlValue在页面初始化时就赋值，此时下拉框默认是空选项，后续用户选择新值后该变量不会自动更新，导致传给GS的参数永远为空
• GS端修改后的代码错误将getUniqueAssociatedElement返回的字符串数组直接push到新数组，形成二维数组，后续遍历取值异常
• 下拉选项更新前没有清空旧选项，会出现新旧选项堆叠的问题
• JS中获取动态键值的对象属性时，不能用点语法，需要用方括号语法（初始代码里的user.returnedAssociatedData是错误写法，应该是user[returnedAssociatedData]）
• 遍历数组时的length-1写法会漏掉最后一个选项

GS端修正代码 #

// 请确保uniqueContractScopeData、uniqueAcTypeData、uniqueCustomerData为GS端全局可访问的字符串数组
// 请确保getUniqueAssociatedElement函数逻辑正常，返回过滤后的一维字符串数组
function processGet_jo(element, returnedAssociatedData) {
  console.log("element: " + element);
  console.log("returnedAssociatedData: " + returnedAssociatedData);
  // 直接使用返回的字符串数组，不要套一层push避免变成二维数组
  var uniqueData = getUniqueAssociatedElement(element, returnedAssociatedData);
  var jo = {};
  var dataArray = [];
  for (var i=0; i < uniqueData.length; i++ ) {
    var record = {};
    record[returnedAssociatedData] = uniqueData[i];
    dataArray.push(record);
  }
  jo.user = dataArray;
  return JSON.stringify(jo);
}

function doGet(){
  return HtmlService.createTemplateFromFile("FormFilter").evaluate();
}

HTML端修正代码 #

<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <!-- form -->
    <form id="filterForm" onSubmit="handleFormSubmitFilter(this)">
    <label for="customer"> Customer </label><br/>
    <select id="customer" name="customer" onChange="getcustomerHtmlValue()">
      <option selected></option><br/>
    </select><br/><br/>
      <label for="contractScope"> Contract Scope </label><br/>
      <select name="contractScope" id="contractScope">
        <option selected></option><br/>
      </select><br/><br/>
     
      <label for="acType"> AC Type </label><br/>
      <select multiple id="acType" name="acType">
        <option></option><br/>
      </select><br/><br/>
    </form>
    <script>
    var customerHtml = document.getElementById("customer");
    var contractScopeHtml = document.getElementById("contractScope");
    var acTypeHtml = document.getElementById("acType");
    
    // 页面初始化加载全量选项
    (function () {
      google.script.run.withSuccessHandler(
        function(value) {
          var jo = JSON.parse(value);             
          for(var i=0; i < jo.user.length; i++) {
            var user = jo.user[i];
            var option = document.createElement("option");
            option.text = user.customer;
            option.value = option.text;
            customerHtml.add(option);
          }
        }
      ).get_joCustomer();

      google.script.run.withSuccessHandler(
        function(value) {
          var jo = JSON.parse(value);
          for( var i=0; i < jo.user.length; i++ ) {
            var user = jo.user[i];
            var option = document.createElement("option");
            option.text = user.contractScope;
            option.value = option.text;
            contractScopeHtml.add(option);
          }
        }
      ).get_joContractScope();

      google.script.run.withSuccessHandler(
        function(value) {
          var jo = JSON.parse(value);
          for( var i=0; i < jo.user.length; i++ ) {
            var user = jo.user[i];
            var option = document.createElement("option");
            option.text = user.acType;
            option.value = option.text;
            acTypeHtml.add(option);
          }
        }
      ).get_joAcType();
    }());

    function getcustomerHtmlValue() {
        // 事件触发时实时获取当前选中的客户值
        var selectedCustomer = customerHtml.options[customerHtml.selectedIndex].value;
        // 先清空现有选项，仅保留默认空选项
        var L = contractScopeHtml.options.length;
        for(var i = L; i >= 1; i--) {
            contractScopeHtml.remove(i);
        }
        google.script.run.withSuccessHandler(
          function(value) {
            console.log("value: " + value);
            var jo = JSON.parse(value);
            for (var i=0; i < jo.user.length; i++) { 
              var user = jo.user[i];
              var option = document.createElement("option");
              option.text = user.contractScope;
              option.value = option.text;
              contractScopeHtml.add(option);
            }
          }
        ).processGet_jo(selectedCustomer, "contractScope");
      }
</script>
</body>
</html>

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