Why my dropdown options are not displayed in a dependable form done using Google Apps Script, Html & JavaScript

user_7153222 0
Viewed 199

WebApp form I am working on - States options are appearing but after selecting them, they are disapearing; But City and Outlets options are not displayed in the drop down at all.

This is google sheets with data, Workign sheet name is "Base", Columns - Outlet Name, Indian State Name, City are required to be populated within web app form. When State is selected, cities within that states are to displayed in the city drop-down, similarly when a city is selected, outlets within that city should be populated in the last drop-down menu.

CODE Apps Script:

function doGet(e) {
  var htmlOutput =  HtmlService.createTemplateFromFile('DependableSelect');
  var states = getStates();
  var cities = getCities();
  var outlets = getOutlets();
  htmlOutput.message = '';
  htmlOutput.states = states;
  htmlOutput.cities = cities;
  htmlOutput.outlets = outlets;
  return htmlOutput.evaluate();
}

function doPost(e) {
  
  Logger.log(JSON.stringify(e));
  
  var name = e.parameters.name.toString();
  var state = e.parameters.state.toString();
  var city = e.parameters.city.toString();
  var outlet = e.parameters.outlet.toString();
  
  AddRecord(name, state, city);
  
  var htmlOutput =  HtmlService.createTemplateFromFile('DependentSelect');
  var states = getStates();
  htmlOutput.message = 'Record Added';
  htmlOutput.states = states;
  htmlOutput.cities = cities;
  return htmlOutput.evaluate(); 
  
}

function getStates() { 
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Base"); 
  //var getLastRow = lovSheet.getLastRow();
  // var getRange =  sheet.getRange(2, 3, 26, 1);
  var getRangeLastRow =  sheet.getLastRow();
  Logger.log(getRangeLastRow);
  var return_array = [];
  for(var i = 2; i <= getRangeLastRow; i++)
  {
      if(return_array.indexOf(sheet.getRange(i, 2).getValue()) === -1) {
        return_array.push(sheet.getRange(i, 2).getValue());
      }
  }

  Logger.log(return_array);
  return return_array;  
}

function getCities(state) { 
  //  state ='Haryana' //remove after test
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Base"); 
  var getRangeLastRow =   sheet.getLastRow();;
  var return_array = [];
  for(var i = 2; i <= getRangeLastRow; i++)
  {
      if(sheet.getRange(i, 2).getValue() === state) {
        return_array.push(sheet.getRange(i, 7).getValue());
      }
  }

  Logger.log(return_array);
  return return_array;  
}


function getOutlets(state, city) { 
  // state ='Haryana' //remove after test
  // city ='Gurgaon-122107' //remove after test
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Base"); 
  var getRangeLastRow =   sheet.getLastRow();;
  var return_array = [];
  for(var i = 2; i <= getRangeLastRow; i++)
  {
      if(sheet.getRange(i, 2).getValue() === state) {
         if(sheet.getRange(i, 7).getValue() === city) {
        return_array.push(sheet.getRange(i, 1).getValue());
      }
      }
  }

  Logger.log(return_array);
  return return_array;  
}


function AddRecord(name, color, fruit) {
  var url = '';   //URL OF GOOGLE SHEET;
  var ss= SpreadsheetApp.openByUrl(url);
  var dataSheet = ss.getSheetByName("DATA");
  dataSheet.appendRow([name, color, fruit, new Date()]);
}

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

CODE HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  
   <script>
    function GetStates() 
    {
    
    google.script.run.withSuccessHandler(function(ar) 
    {

    console.log(ar);
    
    state.length = 0;
    
    let option = document.createElement("option");
    option.value = "";
    option.text = "";
    state.appendChild(option);
    
    ar.forEach(function(item, index) 
    {    
      let option = document.createElement("option");
      option.value = item;
      option.text = item;
      state.appendChild(option);    
    });
    
    }).getStates();
    
    };
  </script>  

   <script>
    function GetCities(state) 
    {
     state = document.getElementById('state');
    google.script.run.withSuccessHandler(function(ar) 
    {

    console.log(ar);
    
    city.length = 0;
    
    let option = document.createElement("option");
    option.value = "";
    option.text = "";
    city.appendChild(option);
    
    ar.forEach(function(item, index) 
    {    
      let option = document.createElement("option");
      option.value = item;
      option.text = item;
      city.appendChild(option);    
    });
    
    }).getCities(state);
    
    };
  </script>  
  

 <script>
    function GetOutlets(state,city) 
    {
    state = document.getElementById('state');
    city = document.getElementById('city');
    google.script.run.withSuccessHandler(function(ar) 
    {

    console.log(ar);
    
    outlet.length = 0;
    
    let option = document.createElement("option");
    option.value = "";
    option.text = "";
    outlet.appendChild(option);
    
    ar.forEach(function(item, index) 
    {    
      let option = document.createElement("option");
      option.value = item;
      option.text = item;
      outlet.appendChild(option);    
    });
    
    }).getOutlets(state,city);
    
    };
  </script>  


 

  </head>
  <body>
    <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" >States</label><br>
      <select name="state" id="state" style="font-size: 20px" onchange="GetStates()" >
      <option value="" ></option>
      <? for(var i = 0; i < states.length; i++) { ?>      
      <option value="<?= states[i] ?>" ><?= states[i] ?></option>
      <? } ?>
      </select><br><br>
      <label style="font-size: 20px" >City</label><br>
      <select name="city" id="city" style="font-size: 20px" onchange="GetCities(this.value)" >
      <option value="" ></option>
      <? for(var i = 0; i < cities.length; i++) { ?>      
      <option value="<?= cities[i] ?>" ><?= cities[i] ?></option>
      <? } ?>
      </select><br><br>
      <label style="font-size: 20px" >Outlets</label><br>
      <select name="outlet" id="outlet" style="font-size: 20px" onchange="GetOutlets(this.value)" >
      <option value="" ></option>
      <? for(var i = 0; i < outlets.length; i++) { ?>      
      <option value="<?= outlets[i] ?>" ><?= outlets[i] ?></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>

I am new to apps sctip and google sheets stuff. I am sorry if I have posted stupid question.

google-apps-script google-sheets web-applications javascript
1 Answers

I'm not that expert when it comes to web apps and html but here are some of my observations and how I was able to fix your code:

  1. The reason why your States drop-down keeps resetting after you select a state is because you are setting its option value again. In addition, when you select a state, you called GetStates() in onchange which will add options to your drop-down list.

When you select a state, you should trigger GetCities(state) to update the city drop-down list

<select name="state" id="state" style="font-size: 20px" onchange="GetStates()" >
<option value="" ></option>
<? for(var i = 0; i < states.length; i++) { ?>      
<option value="<?= states[i] ?>" ><?= states[i] ?></option>
<? } ?>
</select><br><br>
  1. Same goes for the City drop-down. When you select a city, you will call GetCities(state) which will again update the city options.

When you select a city, you should trigger GetOutlets(state,city) to update the outlet drop-down list

  1. For Outlets drop-down, no need to call/set a function since it is the last drop-down that needs to be updated.

  2. If you are wondering when we will update the states, It should be updated when you open the window. You can use this code to perform that operation

window.onload = GetStates;
  1. In GetCities(state) and GetOutlets(state,city), you are passing an html element as arguments for getCities(state) and getOutlets(state,city). You should pass the element's value instead.
Example: getCities(state.value)
  1. Lastly, you forgot to remove duplicates when you are listing your cities in getCities(state)

Updated Code.gs

function getCities(state) { 
  //  state ='Haryana' //remove after test
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Base"); 
  var getRangeLastRow =   sheet.getLastRow();;
  var return_array = [];
  for(var i = 2; i <= getRangeLastRow; i++)
  {
      if(sheet.getRange(i, 2).getValue() === state) {
        if(return_array.indexOf(sheet.getRange(i, 7).getValue()) === -1) {
          return_array.push(sheet.getRange(i, 7).getValue());
        }
      }
  }

  Logger.log(return_array);
  return return_array;  
}

Updated DependableSelect.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  
   <script>
    function GetStates() 
    {
    
    google.script.run.withSuccessHandler(function(ar) 
    {

    console.log(ar);
    
    state.length = 0;
    
    let option = document.createElement("option");
    option.value = "";
    option.text = "";
    state.appendChild(option);
    
    ar.forEach(function(item, index) 
    {    
      let option = document.createElement("option");
      option.value = item;
      option.text = item;
      state.appendChild(option);    
    });
    
    }).getStates();
    
    };

    function GetCities(state) 
    {
      state = document.getElementById('state');
    google.script.run.withSuccessHandler(function(ar) 
    {

    console.log(ar);
    
    city.length = 0;
    
    let option = document.createElement("option");
    option.value = "";
    option.text = "";
    city.appendChild(option);
    
    ar.forEach(function(item, index) 
    {    
      let option = document.createElement("option");
      option.value = item;
      option.text = item;
      city.appendChild(option);    
    });
    
    }).getCities(state.value);
    
    };


    function GetOutlets(state,city) 
    {
    state = document.getElementById('state');
    city = document.getElementById('city');
    google.script.run.withSuccessHandler(function(ar) 
    {

    console.log(ar);
    
    outlet.length = 0;
    
    let option = document.createElement("option");
    option.value = "";
    option.text = "";
    outlet.appendChild(option);
    
    ar.forEach(function(item, index) 
    {    
      let option = document.createElement("option");
      option.value = item;
      option.text = item;
      outlet.appendChild(option);    
    });
    
    }).getOutlets(state.value,city.value);
    
    };

    window.onload = GetStates;
  </script>  


 

  </head>
  <body>
    <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" >States</label><br>
      <select name="state" id="state" style="font-size: 20px" onchange="GetCities(this.value)" >
      </select><br><br>
      <label style="font-size: 20px" >City</label><br>
      <select name="city" id="city" style="font-size: 20px" onchange="GetOutlets(this.value)" >
      </select><br><br>
      <label style="font-size: 20px" >Outlets</label><br>
      <select name="outlet" id="outlet" style="font-size: 20px" >
      </select><br><br>
      <input type="submit" name="submitButton" value="Submit" style="font-size: 20px" /> 
      <span style="font-size: 20px" ><?= message ?></span>      
    </form>


  </body>
</html>

Output:

enter image description here

user_146060460
Related