Google Web App多级联动下拉菜单故障及加载优化咨询

问题描述 #

• 基于原有二级联动下拉菜单代码扩展第三、第四级（Class、Brand）联动后，功能无法正常运行
• 从Google Sheets加载下拉选项耗时约3秒，需优化数据获取效率

预期效果 #

当前代码 #

Google Apps Script代码 #

function doGet(e) {
  var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
  var colors = getColors();
  htmlOutput.message = '';
  htmlOutput.colors = colors;
  return htmlOutput.evaluate();
}

function doPost(e) {
  Logger.log(JSON.stringify(e));

  var name = e.parameters.name.toString();
  var color = e.parameters.color.toString();
  var fruit = e.parameters.fruit.toString();
  var classVal = e.parameters.class.toString(); // class是保留字，重命名变量

  AddRecord(name, color, fruit, classVal);

  var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
  var colors = getColors();
  htmlOutput.message = 'Record Added';
  htmlOutput.colors = colors;
  return htmlOutput.evaluate();
}

function getColors() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lovSheet = ss.getSheetByName("LOV");
  var getLastRow = lovSheet.getLastRow();
  var return_array = [];
  for (var i = 2; i <= getLastRow; i++) {
    if (return_array.indexOf(lovSheet.getRange(i, 1).getValue()) === -1) {
      return_array.push(lovSheet.getRange(i, 1).getValue());
    }
  }
  return return_array;
}

function getFruits(color) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lovSheet = ss.getSheetByName("LOV");
  var getLastRow = lovSheet.getLastRow();
  var return_array = [];
  for (var i = 2; i <= getLastRow; i++) {
    if (lovSheet.getRange(i, 1).getValue() === color) {
      return_array.push(lovSheet.getRange(i, 2).getValue());
    }
  }
  return return_array;
}

function getClass(fruit) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lovSheet = ss.getSheetByName("LOV");
  var getLastRow = lovSheet.getLastRow();
  var return_array = [];
  for (var i = 2; i <= getLastRow; i++) {
    if (lovSheet.getRange(i, 2).getValue() === fruit) {
      return_array.push(lovSheet.getRange(i, 3).getValue());
    }
  }
  return return_array.sort();
}

function getBrand(classVal) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lovSheet = ss.getSheetByName("LOV");
  var getLastRow = lovSheet.getLastRow();
  var return_array = [];
  for (var i = 2; i <= getLastRow; i++) {
    if (lovSheet.getRange(i, 3).getValue() === classVal) {
      return_array.push(lovSheet.getRange(i, 4).getValue());
    }
  }
  return return_array.sort();
}

function AddRecord(name, color, fruit, classVal) {
  var url = '';   // 填写你的Google Sheet URL
  var ss = SpreadsheetApp.openByUrl(url);
  var dataSheet = ss.getSheetByName("DATA");
  dataSheet.appendRow([name, color, fruit, classVal, new Date()]);
}

function getUrl() {
  var url = ScriptApp.getService().getUrl();
  return url;
}

HTML代码 #

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <script>
    function GetFruit(color) {
      google.script.run.withSuccessHandler(function(ar) {
        const fruitSelect = document.getElementById('fruit');
        fruitSelect.length = 0;
        
        let option = document.createElement("option");
        option.value = "";
        option.text = "";
        fruitSelect.appendChild(option);

        ar.forEach(function(item) {    
          let option = document.createElement("option");
          option.value = item;
          option.text = item;
          fruitSelect.appendChild(option);    
        });
      }).getFruits(color);
    }

    function GetClass(fruit) {
      google.script.run.withSuccessHandler(function(ar) {
        const classSelect = document.getElementById('class');
        classSelect.length = 0;
        
        let option = document.createElement("option");
        option.value = "";
        option.text = "";
        classSelect.appendChild(option);

        ar.forEach(function(item) {    
          let option = document.createElement("option");
          option.value = item;
          option.text = item;
          classSelect.appendChild(option);    
        });
      }).getClass(fruit);
    }

    function GetBrand(classVal) {
      google.script.run.withSuccessHandler(function(ar) {
        const brandSelect = document.getElementById('brand');
        brandSelect.length = 0;
        
        let option = document.createElement("option");
        option.value = "";
        option.text = "";
        brandSelect.appendChild(option);

        ar.forEach(function(item) {    
          let option = document.createElement("option");
          option.value = item;
          option.text = item;
          brandSelect.appendChild(option);    
        });
      }).getBrand(classVal);
    }
  </script>

  <h1>Web App Dependent Drop Down</h1>
  <?var url = getUrl();?>
  <form method="post" action="<?= url ?>">
    <label style="font-size: 20px">Name</label><br>
    <input type="text" name="name" style="font-size: 20px" /><br><br>
    
    <label style="font-size: 20px">Colors</label><br>
    <select name="color" style="font-size: 20px" onchange="GetFruit(this.value)">
      <option value=""></option>
      <? for(var i = 0; i < colors.length; i++) { ?>      
      <option value="<?= colors[i] ?>"><?= colors[i] ?></option>
      <? } ?>
    </select><br><br>
    
    <label style="font-size: 20px">Fruit</label><br>
    <select name="fruit" id="fruit" style="font-size: 20px" onchange="GetClass(this.value)">
    </select><br><br>
    
    <label style="font-size: 20px">Class</label><br>
    <select name="class" id="class" style="font-size: 20px" onchange="GetBrand(this.value)">
      <option value="" selected disabled>Select Class</option>
    </select><br><br>

    <label style="font-size: 20px">Brand</label><br>
    <select name="brand" id="brand" style="font-size: 20px">
      <option value="" selected disabled>Select Brand</option>
    </select><br><br>
    
    <input type="submit" name="submitButton" value="Submit" style="font-size: 20px" />
    <span style="font-size: 20px"><?= message ?></span>
  </form>
</body>
</html>

核心优化点说明 #

1. 修复联动故障问题 #

• 修正HTML中重复的id="location"，给每个下拉框分配唯一ID
• 给Class下拉框添加onchange事件，触发Brand级联逻辑
• 新增getBrand后端函数，补充第四级联动数据查询逻辑
• 重命名保留字class为classVal，避免语法冲突

2. 性能优化方案 #

将所有数据一次性读取到内存，避免多次调用getRange：

// 替换原有多个数据查询函数，改为一次性获取所有层级数据
function getHierarchyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lovSheet = ss.getSheetByName("LOV");
  // 一次性读取所有数据，减少Sheet交互次数
  var data = lovSheet.getDataRange().getValues();
  var hierarchy = {
    colors: new Set(),
    fruits: new Map(),
    classes: new Map(),
    brands: new Map()
  };

  for(var i=1; i<data.length; i++){
    var color = data[i][0];
    var fruit = data[i][1];
    var classVal = data[i][2];
    var brand = data[i][3];

    hierarchy.colors.add(color);
    
    if(!hierarchy.fruits.has(color)) hierarchy.fruits.set(color, new Set());
    hierarchy.fruits.get(color).add(fruit);

    if(!hierarchy.classes.has(fruit)) hierarchy.classes.set(fruit, new Set());
    hierarchy.classes.get(fruit).add(classVal);

    if(!hierarchy.brands.has(classVal)) hierarchy.brands.set(classVal, new Set());
    hierarchy.brands.get(classVal).add(brand);
  }

  // 转换为数组格式返回
  return {
    colors: Array.from(hierarchy.colors),
    fruits: Object.fromEntries(Array.from(hierarchy.fruits.entries()).map(([k,v]) => [k, Array.from(v)])),
    classes: Object.fromEntries(Array.from(hierarchy.classes.entries()).map(([k,v]) => [k, Array.from(v)])),
    brands: Object.fromEntries(Array.from(hierarchy.brands.entries()).map(([k,v]) => [k, Array.from(v)]))
  };
}

前端改为一次性获取所有层级数据，本地处理联动逻辑，彻底消除多次后端调用的延迟

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