Selector Change Based On Previous Selectors With Dynamic Lists

June 10, 2022 Post a Comment

I am trying to generate a selector that populates with various tasks based on a previous selector. I have seen the short way to do it with if/else if block code, however for each o

Solution 1:

A Google Web App is in general composed of:

Apps Script code in the .gsfile
HTML code in the htmlfile
JavaScript code within the <script></script> tags

The interaction between those components:

In the .gs file a doGet()function must be implemented to create an HTML output
A JS function can be called from the htmlcode, e.g. <select id = 'reason' onchange="getSheet()">
An Apps Script function can be called from JS within the <script></script> part (alternatively within the <?...?> scriplets) with google.script.run, parameters can be passed to this function from the html file
A return value of a called Apps Script function can be passed back to JS with the withSuccessHandler

For your case:

The chosen dropdown option can accessed with JS (not with Apps Script!) part

var dropdown=document.getElementById('reason');
var sheet=dropdown.options[dropdown.selectedIndex].value;

The chosen sheet name needs to be passed to an Apps Script function where SpreadsheetApp methods are accessible
The options returned by the Apps Script function can be returned back to JS and implemented into the html framework

Here is how you can do it:

.gs file:

function doGet() {  
return HtmlService.createHtmlOutputFromFile("index");
}

function getTasks(sheet){
  var ss= SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName(sheet);
  var list = ws.getRange(1,1).getDataRegion().getValues(); 
  var options = {};
  list.forEach(function(v){
    options[v[0]]= null;
  });
  return options;
}

html file:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  <div>
    <!--my selection i want to base the next selection options off of-->
<select id = 'reason' onchange="getSheet()">
<option value="" disabled selected>Choose Reason</option>
<option value = 'prec'>PREC</option>
<option value = 'crh'>CRH</option>
<option value = 'bh'>BH</option>
<option value = 'ih'>IH</option>
<option value = 'rh'>RH</option>
</select>
<Label>Call Reason</Label>
</div>
<script>
function getSheet(){
var dropdown=document.getElementById('reason');
var sheet=dropdown.options[dropdown.selectedIndex].value;
console.log(sheet);
google.script.run.withSuccessHandler(onSuccess).getTasks(sheet);
  }
function onSuccess(options) {        
        //do something
      }
</script>
  </body>
</html>

Solution 2:

I'm not sure if this is what you mean - why not use "onchange"?

Baca Juga

<div>
    <select id = 'reason1' onchange="MyFunction(0)">
    <option value="" disabled selected>Choose Reason</option>
    <option value = 'prec'>PREC</option>
    <option value = 'crh'>CRH</option>
    <option value = 'bh'>BH</option>
    <option value = 'ih'>IH</option>
    <option value = 'rh'>RH</option>

    </select>
    <Label>Call Reason</Label>
    </div>
<div>

<div>
    <select id = 'reason2' onchange="MyFunction(1)"><!--select[1]-->
    <option value="" disabled selected>Choose Reason</option><!--opt[0]-->
    <option value = 'prec2'>PREC</option><!--opt[1]-->
    <option value = 'crh2'>CRH</option><!--opt[2]-->
    <option value = 'bh2'>BH</option><!--opt[3]-->
    <option value = 'ih2'>IH</option><!--opt[4]-->
    <option value = 'rh2'>RH</option><!--opt[5]-->

    </select>
    <Label>Call Reason</Label>
    </div>
<div>

<div>
    <select id = 'reason3' onchange="MyFunction(2)"><!--select[3]-->
    <option value="" disabled selected>Choose Reason</option>
    <option value = 'prec3'>PREC</option>
    <option value = 'crh3'>CRH</option>
    <option value = 'bh3'>BH</option>
    </select>
    <Label>Call Reason</Label>
    </div>
<div>


<script>
function MyFunction(x) {
    //x - position of select in the array
    var selects = [document.getElementById('reason1'), document.getElementById('reason2'), document.getElementById('reason3')]; //store all your selects in an array to easily get them
    var url = 'your url here'; 
    var selectedOption = selects[x].selectedIndex; //index of selected option
    var selectedSelectId = event.target.id; //id of select changed
    var ss = SpreadsheetApp.openByUrl(url);
    var wsName = selectedSelectId + '-' + selectedOption;
    var ws = ss.getSheetByName(wsName); //now you should name each of your spreedsheed "id of the select - option index", for example "reason1-1" for "prec", "reason3-2" for "crh3" etc;
    //rest of your code here
}
</script>

JavaScript Free