Display a data according to the choice of several menus in cascade (Google Script)-CodePudding

I am developing a tool that allows to collect requests via a form created on Google Script.

The user selects via cascading drop-down menus the site, the area, the zone and finally the room for which he wants to make his request. I would like to be able to display the manager who corresponds to the selected room but I can't. There shouldn't be much to change but I can only display in relation to the selected area.

Here are the different codes used:

server side code :

function getQtyOnHand(site, secteur, zone, salle){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Table");
  const data = ws.getRange(2, 1, ws.getLastRow()-1, 7).getValues();
  var filtre = {};
  const filteredData = data.filter(r => r[0] === site && r[1] === secteur && r[2] === zone && r[4] === salle);
  filtre.filtreSecteur = filteredData.length === 0 ? 0 : filteredData.reduce((subtotal, r) => subtotal   r[6],"");
  return filtre;
}

the code's functions on the front side

function updatedQtyOnHand(){
 var site = document.getElementById("item-site").value;
 var secteur = document.getElementById("item-secteur").value;        
 var zone = document.getElementById("item-zone").value;        
google.script.run.withSuccessHandler(afterQtyOnHandReturned).getQtyOnHand(site,secteur,zone);  
}

function afterQtyOnHandReturned(qty){
  document.getElementById("on-hand").textContent = ""; 
  var responsable = qty.filtreSecteur.split("@");
  var nomPrenomResp = responsable[0];
  var adresseResponsable = nomPrenomResp "@test.com";    
  document.getElementById("on-hand").textContent = adresseResponsable; 
}

the html's code on the front side to show responsable (sorry for the indentation but every time I publish here, I have to redo it with spaces and I'm struggling):

<!-- HTML DU FORMULAIRE  -->
<div id="userform" class="userForm">
  <h1 class="mb-5" id="titreFormulaire">FORMULAIRE DEMANDE TRAVAUX NEUFS</h1>
  <div class="row mt-5 ml-3 mr-3">
  <!-- Sélection du lieu -->
    <div class="blocEndroitFull">
      <!-- Choix du site -->
      <div class ="blocEndroit">
        <label for="item-site" id="labelSite">Site :</label>
        <select class="form-control" id="item-site">
        </select>
      </div>
      <!-- Choix du secteur -->
      <div class ="blocEndroit">
        <label for="item-secteur" id="labelSecteur">Secteur :</label>
        <select class="form-control" id="item-secteur">
        </select>
      </div>
      <!-- Choix de la zone -->
      <div class ="blocEndroit">
        <label for="item-zone" id="labelZone">Désignation zone :</label>
        <select class="form-control" id="item-zone">
        </select>
      </div>
      <!-- Choix de la salle -->
      <div class ="blocEndroit">
        <label for="item-salle" id="labelSalle">Salle :</label>
        <select class="form-control" id="item-salle"> 
        </select>
      </div>
   </div> 
   <!-- Affichage du responsable -->
   <div id="responsable">
     <span class="input-group-text" id="txt-responsable">Responsable :
       <span  class="ml-1">
         <span id="on-hand"></span>
       </span>
    </span>
  </div>
  <!-- Plan -->
  <div class="col-12">
    <img id="image" width="80%" class ="rounded mx-auto d-block image">
  </div>
  <!-- Description -->
  <hr width="100%" color="black">
  <div class="form-group" id="descriptionBloc">
    <label for="delivery-note"><b>Veuillez décrire votre demande en quelques mots :</b></label>
    <textarea type="text" class="form-control" id="delivery-note" rows="5" cols="100" required></textarea>  
    <div class="invalid-feedback">Décrivez votre demande</div>
  </div>
  <!-- Informations complémentaires (checklists) -->
  <div id="infoComplementairesFull">
    <div class="blocInfos">
     <label for="date-received"><b>Délai :</b></label>
     <div class="form-check">
      <input class="form-check-input" type="radio" name="radio" id="trimestre" value="trimestre" required>
      <label class="form-check-label" for="trimestre">Trimestre</label>
     </div>
     <div class="form-check">
       <input class="form-check-input" type="radio" name="radio" id="semestre" value="semestre">
       <label class="form-check-label" for="semestre">Semestre</label>
       <div class="invalid-feedback">Choisissez un délai.</div>
     </div>
   </div>
   <div class="blocInfos">
     <label for="item-inter"><b>Intervention possible en production :</b></label>
      <div class="form-check">
        <input class="form-check-input" type="radio" name="radio2" id="interOui" value="oui">
        <label class="form-check-label" for="interOui">Oui</label>
      </div>
      <div class="form-check">
        <input class="form-check-input" type="radio" name="radio2" id="interNon" value="non" checked>
        <label class="form-check-label" for="interNon">Non</label>
      </div>
    </div>
    <div class="blocInfos">
      <label for="item-dispo"><b>Disponibilité de la zone :</b></label>
      <div class="form-check">
        <input class="form-check-input" type="radio" name="radio3" id="dispoNon" value="zone toujours disponible">
        <label class="form-check-label" for="dispoNon">Zone toujours disponible</label>
      </div>
      <div class="form-check">
        <input class="form-check-input" type="radio" name="radio3" id="dispoOui" value="travaux à planifier" checked>
        <label class="form-check-label" for="dispoOui">Travaux à planifier</label>
     </div>
   </div>
   <div class="blocInfos">
   <label><b>Budget est-il prévu ?</b></label>
   <div class="form-check">
     <input class="form-check-input" type="radio" name="radio4" id="budgetNon" value="non">
     <label class="form-check-label" for="budgetNon">
      Non
     </label>
   </div>
   <div class="form-check">
     <input class="form-check-input" type="radio" name="radio4" id="budgetNeSaitPas" value="ne sait pas" checked>
     <label class="form-check-label" for="budgetNeSaitPas">
      Ne sait pas
     </label>
   </div>
   <div class="form-check">
     <input class="form-check-input" type="radio" name="radio4" id="budgetOui" value="oui">
     <label class="form-check-label" for="budgetOui">
      Oui
     </label>
   </div> 
   <div id="idBudget" class="input-group input-line mb-1 d-none">
     <input id="inputIdBudget" list="listeBudget" name="radio4" type="text" class="form-control" placeholder="ID budget ...">
   </div>
 </div>

Choisissez une photo de près :
Choisissez une photo de près Choisissez une photo de loin : Choisissez une photo de loin Envoyer Demande Confirmation ×

the link to the Sheet file if you want to look at it by yourself :

I tried the code below but it doesn't work and I don't understand why

server side code :

function getQtyOnHand(site, secteur, zone, salle){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Table");
  const data = ws.getRange(2, 1, ws.getLastRow()-1, 7).getValues();
  var filtre = {};
  const filteredData = data.filter(r => r[0] === site && r[1] === secteur && r[2] === zone && r[4] === salle);
  filtre.filtreSecteur = filteredData.length === 0 ? 0 : filteredData.reduce((subtotal, r) => subtotal   r[6],"");
  return filtre;
}

the code's function on the front side :

function updatedQtyOnHand(){
var site = document.getElementById("item-site").value;
var secteur = document.getElementById("item-secteur").value;        
var zone = document.getElementById("item-zone").value;        
var zone = document.getElementById("item-salle").value;        
google.script.run.withSuccessHandler(afterQtyOnHandReturned).getQtyOnHand(site,secteur,zone,salle);  
}

the code's function where i used updatedQtyOnHand's function (in dropdown's menus:

 function afterDropDownArrayReturned(arrayOfArrays){
   arrayOfValues = arrayOfArrays.filter(function(r){ return true; });
  var item = document.getElementById("item-site");
  addUniqueOptionsToDropdownList(item,arrayOfValues,0);
  afterFirstDropDownChanged();
  afterSecondDropDownChanged();
  afterThirdDropDownChanged();
 }
 function addUniqueOptionsToDropdownList(el,arrayOfArrays,index){
   var currentlyAdded = [];
   el.innerHTML = '';
   arrayOfArrays.forEach(function(r){
     if(currentlyAdded.indexOf(r[index]) === -1){
       var option = document.createElement("option");
       option.textContent = r[index];
       el.appendChild(option);
       currentlyAdded.push(r[index]);
     }
   });
   var option = document.createElement("option");
   option.textContent = "Non défini";
   el.appendChild(option);
 }
 function afterFirstDropDownChanged(){
   var itemSecteur = document.getElementById("item-secteur");
   var secteur = document.getElementById("item-site").value;
   var filteredArrayOfValues = arrayOfValues.filter(function(r){ return r[0] === secteur });
addUniqueOptionsToDropdownList(itemSecteur,filteredArrayOfValues,1);
afterSecondDropDownChanged();
updatedQtyOnHand();
} 

 function afterSecondDropDownChanged(){

   var itemZone = document.getElementById("item-zone");
   var site = document.getElementById("item-site").value;
   var itemSecteur = document.getElementById("item-secteur").value;
   var filteredArrayOfValues = arrayOfValues.filter(function(r){ return r[0] === site && r[1] === itemSecteur});
addUniqueOptionsToDropdownList(itemZone,filteredArrayOfValues,2);
updatedQtyOnHand();
  }

 function afterThirdDropDownChanged(){
   var itemZone = document.getElementById("item-zone").value;
   var site = document.getElementById("item-site").value;
   var itemSecteur = document.getElementById("item-secteur").value;
   var itemSalle = document.getElementById("item-salle");
   var filteredArrayOfValues = arrayOfValues.filter(function(r){ return r[0] === site && r[1] === itemSecteur && r[2] === itemZone});
addUniqueOptionsToDropdownList(itemSalle,filteredArrayOfValues,4);
updatedQtyOnHand();
   }

Thanks in advance for your help and sorry for my poor English.

CodePudding user response：

As I committed yesterday, here is a html template focused on dependent cascading dropdown (4 levels) with sources fetched from google sheet.

On gs side:

function transfertHeaders() {
  var bdd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DB')
  return JSON.stringify(bdd.getRange(1,1,1,bdd.getLastColumn()).getValues())
}
function transfertData() {
  var bdd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DB')
  return JSON.stringify(bdd.getRange(2,1,bdd.getLastRow(),bdd.getLastColumn()).getValues())
}

on html side:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>

<body>
  <script>
    <? 
      var headers = JSON.parse(transfertHeaders());
    ?>
    var data = JSON.parse(<?=transfertData()?>);
    
    window.onload = function() {
      var level1sel = document.getElementById("level1");
      var level2sel = document.getElementById("level2");
      var level3sel = document.getElementById("level3");
      var level4sel = document.getElementById("level4");
      var dico = new Map()

      for (var i = 0; i < data.length; i  ) {
        dico.set(data[i][0],"")
      }
      dico.forEach(function(value, key) {
        level1sel.options[level1sel.options.length] = new Option(key, key);
      })

      level1sel.onchange = function(){
        level2sel.length = 1;
        level3sel.length = 1;
        level4sel.length = 1;
        dico.clear()
        for (var i = 0; i < data.length; i  ) {
          if (data[i][0]==this.value) {dico.set(data[i][1],"")}
        }
        dico.forEach(function(value, key) {
          level2sel.options[level2sel.options.length] = new Option(key, key);
        })
      }

      level2sel.onchange = function(){
        level3sel.length = 1;
        level4sel.length = 1;
        var select1 = document.getElementById('level1');
        var value1 = select1.options[select1.selectedIndex].value;
        dico.clear()
        for (var i = 0; i < data.length; i  ) {
          if (data[i][1]==this.value && data[i][0]==value1) {dico.set(data[i][2],"")}
        }
        dico.forEach(function(value, key) {
          level3sel.options[level3sel.options.length] = new Option(key, key);
        })
      }

      level3sel.onchange = function(){
        level4sel.length = 1;
        var select1 = document.getElementById('level1');
        var value1 = select1.options[select1.selectedIndex].value;
        var select2 = document.getElementById('level2');
        var value2 = select2.options[select2.selectedIndex].value;
        dico.clear()
        for (var i = 0; i < data.length; i  ) {
          if (data[i][2]==this.value && data[i][0]==value1 && data[i][1]==value2) {dico.set(data[i][3],"")}
        }
        dico.forEach(function(value, key) {
          level4sel.options[level4sel.options.length] = new Option(key, key);
        })
      }

    }
  
  </script>
  <h3>Cascading Dropdown</h3>
  <form name="form" id="form" >
    <?!= headers[0][0] ?>: <select name="level1" id="level1">
      <option value="" selected="selected">Select <?!= headers[0][0] ?></option>
    </select>
    <br>
    <?!= headers[0][1] ?>: <select name="level2" id="level2">
      <option value="" selected="selected">Please select <?!= headers[0][0] ?> first</option>
    </select>
    <br>
    <?!= headers[0][2] ?>: <select name="level3" id="level3">
      <option value="" selected="selected">Please select <?!= headers[0][1] ?> first</option>
    </select>
    <br>
    <?!= headers[0][3] ?>: <select name="level4" id="level4">
      <option value="" selected="selected">Please select <?!= headers[0][2] ?> first</option>
    </select>
    <br>
  </form>

</body>
</html>

CodePudding user response：

var zone is re-defined again just after first definition. It should be salle.