Selector Change Based On Previous Selectors With Dynamic Lists

March 31, 2024 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:

functiondoGet() {  
return HtmlService.createHtmlOutputFromFile("index");
}

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

html file:

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

Solution 2:

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

<div><selectid = 'reason1'onchange="MyFunction(0)"><optionvalue=""disabledselected>Choose Reason</option><optionvalue = 'prec'>PREC</option><optionvalue = 'crh'>CRH</option><optionvalue = 'bh'>BH</option><optionvalue = 'ih'>IH</option><optionvalue = 'rh'>RH</option></select><Label>Call Reason</Label></div><div><div><selectid = 'reason2'onchange="MyFunction(1)"><!--select[1]--><optionvalue=""disabledselected>Choose Reason</option><!--opt[0]--><optionvalue = 'prec2'>PREC</option><!--opt[1]--><optionvalue = 'crh2'>CRH</option><!--opt[2]--><optionvalue = 'bh2'>BH</option><!--opt[3]--><optionvalue = 'ih2'>IH</option><!--opt[4]--><optionvalue = 'rh2'>RH</option><!--opt[5]--></select><Label>Call Reason</Label></div><div><div><selectid = 'reason3'onchange="MyFunction(2)"><!--select[3]--><optionvalue=""disabledselected>Choose Reason</option><optionvalue = 'prec3'>PREC</option><optionvalue = 'crh3'>CRH</option><optionvalue = 'bh3'>BH</option></select><Label>Call Reason</Label></div><div><script>functionMyFunction(x) {
    //x - position of select in the arrayvar selects = [document.getElementById('reason1'), document.getElementById('reason2'), document.getElementById('reason3')]; //store all your selects in an array to easily get themvar url = 'your url here'; 
    var selectedOption = selects[x].selectedIndex; //index of selected optionvar selectedSelectId = event.target.id; //id of select changedvar 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>

Html5 Tips