时间戳

首页 > 解决方案 > 根据使用 Google Web App 选择的其他两个下拉菜单项填充下拉菜单

javascript - 根据使用 Google Web App 选择的其他两个下拉菜单项填充下拉菜单

问题描述

我正在尝试根据用户从两个不同的下拉菜单（1. 选择部门，2. 选择级别）中的两个单独的选择来填充下拉菜单，以便使用相应的选项（项目）填充第三个下拉菜单名称）将从 Google 表格的第三列中提取。努力尝试比较两个 html 元素以确定第三个元素。从连接前端功能和服务器端功能开始，我开始迷失在不同级别的代码中。

HTML 代码

<body>
  <div class="row">
       <div class="input-field col s6">
        <select id="departments"  required>
          <option value="" disabled selected>Select your Department</option>
          <option>Department A</option>
          <option>Department B</option>
          <option>Department C</option>
          <option>Department D</option>
          <option>Department E</option> 
          <option>Department F</option>
        </select>      
      </div>
    </div>
    <div class="row">
       <div class="input-field col s6">
        <select id="level" required>
          <option value="" disabled selected>Select Level</option>
          <option>1</option>
          <option>2</option>
          <option>3</option>
          <option>4</option>
        </select>      
      </div>
    </div>
    <div class="row">
     <div class="input-field col s6">
        <select id="project" name="selectedProject" required>
        </select>
     </div>
  </div>     <div class="row">
      <button id="btn" class="waves-effect waves-light btn-large">Submit<i class="material-icons left">inbox</i></button>
   </div> 
</body>

Javascript

function getProjectName(department, level){
  var url = "url link to spreadsheet";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("projects");
  var data = ws.getRange(1,1).getDataRange.getValues();

  
  var projectOptions = {};
 
  data.forEach(function(value){    
    projectOptions[value[2]] = null;        
  });

  return projectOptions;
 
}



 function populateProjects(codes){
    
     const elm = document.getElementById('project');
     const code = listCodes
       .map(row => `<option value="${row}">${row}</option>`)
      .join('');
      
      elm.innerHTML =
      '<option disabled selected>Select Project Code</option>' + code;
    setTimeout(() => M.FormSelect.init(elm), 0);
    }
<script>
document.getElementById("btn").addEventListener("click",collectForm);


google.script.run.withSuccessHandler(populateProjects).getProjectName(department, level); 

*** need function to get both selections from both drop-down menus on change
function filterSelections(){

document.getElementById("department").addEventListener("change", ????);
document.getElementById("level").addEventListener("change", ????);

//我不知道如何过滤和映射这两个选择来运行一个函数来填充第三个下拉菜单。}

电子表格数据 |部门 | 水平 | 项目 | |A部| 1| AP01| |A部| 2| AP02| |A部| 3| AP03| |A部| 4| AP04| |A部| 3| AP03| |B部| 4| BP04| |B部| 2| BP02| |B部| 1| AP01| |B部| 3| BP03| |B部| 2| BP02| |C部| 3| CP03| |C部| 2| CP02| |C部| 1| AP01| |C部| 4| CP04| |C部| 1| AP01| |D部| 2| DP02| |D部| 1| AP01| |D部| 3| DP03| |D部| 4| DP04| |D部| 4| DP04| |E部| 1| EP01| |E部| 2| EP02| |E部| 3| EP03| |E部| 4| EP04| |E部| 4| EP04| |F部| 2| FP02| |F部| 1| AP01| |F部| 4| FP04| |F部| 3| FP03| |F部| 2| FP02|

标签： javascriptgoogle-apps-scriptweb-applications

解决方案

假设您将电子表格中的数据放入如下对象数组中：

 const data = [
    {
      department: 'Department A',
      level: '1',
      project: 'AP01'
    },
    {
      department: 'Department A',
      level: '1',
      project: 'AP02'
    },
    {
      department: 'Department A',
      level: '2',
      project: 'AP02'
    },
    {
      department: 'Department A',
      level: '3',
      project: 'AP03'
    },
    {
      department: 'Department A',
      level: '4',
      project: 'AP04'
    },
    {
      department: 'Department B',
      level: '1',
      project: 'BP01'
    },
    {
      department: 'Department B',
      level: '2',
      project: 'BP02'
    },
    {
      department: 'Department B',
      level: '3',
      project: 'BP03'
    },
  ]

然后你可以这样做：

      const department = document.getElementById('departments');
      const level = document.getElementById('level');
      const project = document.getElementById('project');

      function populate(){
        const selectedDepartment = department.value;
        const selectedLevel = level.value;
        const projects = [];

        for(row of data){
          if(row.department === selectedDepartment && row.level === selectedLevel){
            projects.push(row.project);
          }
        }

        while (project.firstChild) {
          project.removeChild(project.lastChild);
        }
        projects.forEach(p => {
          let projectOption = document.createElement('option');
          projectOption.textContent = p;
          project.appendChild(projectOption);
        });

      }
  department.addEventListener('change', populate);
  level.addEventListener('change', populate);

每次前两个下拉列表中的一个更改时，这将填充第三个下拉列表。

你可以看到它在这里工作：jsfiddle

推荐阅读