Google Web App Dynamic Dependent Dropdown-CodePudding

I've been trying to add 3rd and 4th level dependent dropdown using the code from Code with Curt(

Here's the code:

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 class = e.parameters.class.toString(); //class is a reserved word

  AddRecord(name, color, fruit, class);

  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 AddRecord(name, color, fruit, class) {
  var url = '';   //URL OF GOOGLE SHEET;
  var ss = SpreadsheetApp.openByUrl(url);
  var dataSheet = ss.getSheetByName("DATA");
  dataSheet.appendRow([name, color, fruit, class, 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) 
    {

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

 function getClass(queue)
{

google.script.run.withSuccessHandler(function(ar) 
{

console.log(ar);

class.length = 0;

let option = document.createElement("option");
option.value = "";
option.text = "";
class.appendChild(option);

ar.forEach(function(item, index) 
{    
  let option = document.createElement("option");
  option.value = item;
  option.text = item;
  class.appendChild(option);    
});

}).getClass(queue);

};
  </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" >
      </select><br><br>
    <label style="font-size: 20px" >Class</label><br>
    <select name="location" id="location" style="font-size: 20px" >
  <option value="" selected disabled>Select Class</option>
  </select><br><br>

    <label style="font-size: 20px" >Brand</label><br>
    <select name="location" id="location" 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>

CodePudding user response：

You may use the following GAS and HTML:

Google Apps Script

I have added the

Web App

Output

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

Modification points:

When a loop process is used using the HTML template, the process cost becomes high. Ref
- In this case, the HTML template is used for replacing the values.
When google.script.run is used, the process cost becomes high.
- In this case, google.script.run is used for sending the values to the Google Apps Script side instead of the form submission.
- From your showing script, I thought that the values might not be required to be sent with the HTML request. So, in this modification, the values are sent with google.script.run.
Creations of the options in the select tag are done in Javascript using the 1st loaded values.
In your Google Apps Script side, getValue() is used in a loop. In this case, the process cost becomes high. Ref

When these points are reflected in your showing script, how about the following modification?

Google Apps Script side:

function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LOV");
  var [, ...values] = sheet.getDataRange().getDisplayValues();
  var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
  htmlOutput.values = JSON.stringify(values);
  htmlOutput.message = '';
  return htmlOutput.evaluate();
}

function addRecord({ name, color, fruit, clas, brand }) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA");
  sheet.appendRow([name, color, fruit, clas, brand, new Date()]);
}

HTML & Javascript side:

<h1>Web App Dependent Drop Down</h1>
<form>
  <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 id="colors" name="color" style="font-size: 20px" onchange="setOptions('fruit', getValues(this.value, 0))" ></select><br><br>
  <label style="font-size: 20px" >Fruit</label><br>
  <select name="fruit" id="fruit" style="font-size: 20px" onchange="setOptions('clas', getValues(this.value, 1))" ></select><br><br>
  <label style="font-size: 20px" >Class</label><br>
  <select name="clas" id="clas" style="font-size: 20px" onchange="setOptions('brand', getValues(this.value, 2))" ></select><br><br>
  <label style="font-size: 20px" >Brand</label><br>
  <select name="brand" id="brand" style="font-size: 20px" ></select><br><br>
  <input type="button" name="submitButton" value="Submit" style="font-size: 20px" onclick="sample(this.parentNode)" >
  <span style="font-size: 20px" ><?= message ?></span>
</form>
<script>
const values = JSON.parse(<?= values ?>);

function setOptions(id, v) {
  const s = document.getElementById(id);
  s.innerHTML = "";
  v.forEach(a => {
    const option = document.createElement("option");
    option.value = a;
    option.innerHTML = a;
    s.appendChild(option);
 });
}

function getValues(e, i) {
  return ["", ...new Set(values.reduce((ar, r) => (r[i] == e && ar.push(r[i   1]), ar), []))];
}

window.onload = function() {
  setOptions("colors", ["", ...new Set(values.map(([a]) => a))]);
}

function sample(e) {
  google.script.run.addRecord(e);
}
</script>

Note:

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in the report "Redeploying Web Apps without Changing URL of Web Apps for new IDE".