Selector Change based on previous selectors with dynamic lists

Question

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 of my selections I have 20+ options that could be chosen.

I have tried to put options on various sheets within the spread sheet and run a if/else if statement so the drop down will populate with tasks that are from one of the sheets associated with a selection option.

<!--my selection i want to base the next selection options off of-->
<select id = 'reason'>
<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>

<!-- function that generates tasks dynamically from a sheet -->
function getTasks(){

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

<!--essentially If someone chose "CRH" I would want it to open the sheet 
with the CRH options -->

The way I wrote the loop didn't work.

function getTasks(){

  var ss= SpreadsheetApp.openByUrl(url);
  var options = {};
//basically added else ifs for each reason with the same code just dif 
//sheet names
  if (document.getElementById('reason')='prec'){
  var ws = ss.getSheetByName('PREC');
  var list = ws.getRange(1,1).getDataRegion().getValues();

  list.forEach(function(v){
    options[v[0]]= null;
}
  });
return options
}

Hawthorn · Answer 1 · 2019-08-02T16:23:45.627

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

<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>

I don't think that's what I am looking for. if reason = prec then these options display if crh a different list ecetera the problem is I have 20 for prec, 10 for crh, 7 for the others and they're all different — samdaily34, Aug 02 '19 at 00:23
no, I am using materialize selectors and while it's initialized and the function is working and printing the right selections in the console, the id that is shown on the console is not the same id targeted in html — samdaily34, Aug 02 '19 at 19:33
I don't even know if that makes sense because I have never seen it or had it happen to me before — samdaily34, Aug 02 '19 at 19:33
I've never worked with materialize, I think this question will help you more: https://stackoverflow.com/questions/28258106/materialize-css-select-doesnt-seem-to-render — Hawthorn, Aug 03 '19 at 16:59

score 0 · Accepted Answer · answered Aug 03 '19 at 09:57

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>

do you know how this would change if I wanted to do a dynamic multiple select? still changing options based on the sheet just into a list form instead of autocomplete — samdaily34, Aug 07 '19 at 18:22
Something like this: https://stackoverflow.com/questions/5866169/how-to-get-all-selected-values-of-a-multiple-select-box You can loop through all options and perform google.script.run within the if statement - it will run for each selected sheet. Not sure what you mean by "a list form instead of autocomplete", I do not know what exactly you want to do with your sheet data and list. Check here how to dynamically create a button and html page - maybe this is what you mean: https://stackoverflow.com/questions/57310460/build-a-reporting-history-on-my-google-web-app/57326975#57326975 — ziganotschka, Aug 07 '19 at 20:28

Selector Change based on previous selectors with dynamic lists

2 Answers2

A Google Web App is in general composed of:

The interaction between those components:

For your case:

Linked