CODEWITHSUNDEEP
javascriptgoogle-apps-script
Bruno Vaz
Bruno Vaz

Reputation: 5025

How to manipulate select lists with Google Apps Script?

I have a spreadsheet file with some sidebars to enter data and do some other things.

In a sidebar, I have a form to enter data, and along with the data there's 3 select lists to define the position of a Metallic Unit in a warehouse. The position consists of 3 informations: level, letter and number. Level can be N1, N2 or N3, letter can be A1, A2, B1, B2 until M2, and number can be 1 to 66. Visually speaking it's like this:

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<p>Position:</p>
<select>
  <option selected disabled>Level</option>
  <option>N1</option>
  <option>N2</option>
  <option>N3</option>
</select>
<select>
  <option selected disabled>Letter</option>
  <option>A1</option>
  <option>A2</option>
  <option>B1</option>
  <option>B2</option>
  <option>C1</option>
  <option>C2</option>
  <option>...</option>
</select>
<select>
  <option selected disabled>Number</option>
  <option>1</option>
  <option>2</option>
  <option>3</option>
  <option>4</option>
  <option>5</option>
  <option>6</option>
  <option>...</option>
</select>

The problem is, each level has some own details, like:

So, the ideal form would change the content of the latter 2 selects based in the first option I select. The problem is, GAS is limited to manipulate these elements, and I can't find a way that really works, because the content should change dynamically depending on the Level selected option.

Upvotes: 0

Views: 144

Answers (1)

Alan Wells
Alan Wells

Reputation: 31310

You can use jQuery/javascript and DOM properties and methods to change the HTML in the select fields. GAS is not limited. There is very little that you can not do.

You need to use the onchange() event to populate the other select fields when the previous one has been selected:

<!-- the onchange attribute make the function run when a choice is made -->
<select id="idLevels" onchange="fncPopulateLetterSelect()">
  <option selected disabled>Level</option>
  <option>N1</option>
  <option>N2</option>
</select>

<select id="idLtrOptions">
  <option selected disabled>Letter</option>
  <option>A1</option>
  <option>A2</option>
  <option>B1</option>
  <option>...</option>
</select>

<script>
  //This is an anonymous function assigned to the browsers window object.
  window.fncPopulateLetterSelect = function() {
    /* Create an object with a specific list of values associated with
     * that particular choice. Values inside brackets are elements in 
     * an array */
    var objValOneToLetterChoices = {"N1":["P3","G9","y23"]};
    var selectOneValue = document.getElementById("idLevels").value;
    alert('It ran!');
    console.log('selectOneValue: ' + selectOneValue);

    //Get the list of choices that is in the array.
    var arrayOfNewOptions = objValOneToLetterChoices[selectOneValue];
    console.log('arrayOfNewOptions: ' + arrayOfNewOptions);

    //define multiple variables at once for the loop
    var i=0, newOptions = "", thisOption="";

    /*Loop through all the elements of the array, and create HTML to
     * inject into the select tag */
    for (i=0;i<arrayOfNewOptions.length;i+=1) {
      thisOption=arrayOfNewOptions[i];
      console.log("thisOption: " + thisOption);
      //On every loop concatenate the new option to the previous options
      newOptions += "<option>" + thisOption + "</option>"
    };
    /*Get a reference to the HTML Select element that will receive the
     * new list of options */
    var elmtLtrOptions = document.getElementById("idLtrOptions");
    //Use the DOM innerHTML property to set the new HTML
    elmtLtrOptions.innerHTML = newOptions;
  };
</script>

innerHTML

To get the third select box choices to change based on the second select box, simply use the same code structure, but different HTML element ID's and of course, different settings in the object array.

Upvotes: 1

Related Questions