CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-web-component
Maxamis4
Maxamis4

Reputation: 188

Google Appscript WebApp Dropdown list from sheet

I am having trouble getting my form to load a dropdown from my sheet. When the form-form.html loads, I get all the HTML content, but the dropdowns don't load. Things to know:

It should be noted that I will be using the same data list for two other drop downs, and my intention will be to add another constant in my HTML function to include data for the other two dropdowns. This is a secondary goal but explains why the function_populateDropDown is written the way it is.

Here is my code:

config/configuration.gs

  // Open the spreadsheet containing authorized emails
  var ss                 = SpreadsheetApp.openById('1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
  var dataSheet          = ss.getSheetByName('Data'); // Change to the name of your sheet if different
  var coachSheet         = ss.getSheetByName('Coach_Recommendations'); // Change to the name of your sheet if different

form-appscript.gs

function loadAuthorizedForm() {
  console.log('Loading form-form.html');
  var template = HtmlService.createTemplateFromFile('form-form');
  return template.evaluate().getContent();
};

function getPositionDropdown() {
  var colindex          = getColumnIndexByHeader("Position",dataSheet);
  var lcol              = dataSheet.getLastRow();
  var positionColumn    = dataSheet.getRange(2,colindex,lcol).getValues();

  return positionColumn

}

form-form.html

<!DOCTYPE html>
<script>

  function populateDropdown() {
    console.log('populateDropdown function called');

    // Retrieve the dropdown element
    const primaryPositionDropdown = document.getElementById('primaryPosition');

    // Fetch data from server-side function
    google.script.run.withSuccessHandler(positions => {
      // Create and append option elements
      positions.forEach(position => {
        const optionElement = document.createElement('option');
        optionElement.value = position[0];
        optionElement.textContent = position[0];
        primaryPositionDropdown.appendChild(optionElement);
      });
    }).getPositionDropdown();
  }

   window.addEventListener('load', populateDropdown);
</script>

<html>
<head>
  <base target="_top">
</head>
<body>
  <form id="dataEntryForm">
    <label for="playerFirstName">Player First Name:</label>
    <input type="text" id="playerFirstName" name="playerFirstName" maxlength="25"><br><br>

    <label for="playerLastName">Player Last Name:</label>
    <input type="text" id="playerLastName" name="playerLastName" maxlength="25"><br><br>

    <label for="middleInitial">Middle Initial:</label>
    <input type="text" id="middleInitial" name="middleInitial" maxlength="3"><br><br>

    <label for="dominantFoot">Dominant Foot:</label>
    <select id="dominantFoot" name="dominantFoot">
      <option value="left">Left</option>
      <option value="right">Right</option>
      <option value="both">Both</option>
    </select><br><br>

    <label for="primaryPosition">Primary Position:</label>
    <select id="primaryPosition" name="primaryPosition">
      <option value="">Select a Value</option>
    </select><br><br>

    <label for="secondaryPosition">Secondary Position:</label>
    <select id="secondaryPosition" name="secondaryPosition">
      <option value="">Select a Value</option>
    </select><br><br>

    <label for="alternatePosition">Alternate Position:</label>
    <select id="alternatePosition" name="alternatePosition">
      <option value="">Select a Value</option>
    </select><br><br>

    <label for="coachName">Coach Name:</label>
    <select id="coachName" name="coachName">
      <option value="">Select a Value</option>
    </select><br><br>

    <input type="checkbox" id="question1Acknowledge" name="question1Acknowledge">
    <label for="question1Acknowledge">You acknowledge that the player you have chosen for the All-Stars team possesses a team-oriented attitude and has consistently demonstrated excellent sportsmanship throughout the season</label><br><br>

    <input type="checkbox" id="question2Acknowledge" name="question2Acknowledge">
    <label for="question2Acknowledge">The player you've chosen has displayed remarkable determination and has excelled both in their individual performance and in their collaboration with teammates.</label><br><br>

    <input type="checkbox" id="question3Acknowledge" name="question3Acknowledge">
    <label for="question3Acknowledge">You recognize that the player you've selected not only possesses strong teamwork skills but also exhibits a competitive spirit that makes them a suitable representative for BRYC in a competitive tournament like All Stars</label><br><br>

    <label for="positionComments">Position Comments:</label><br>
    <textarea id="positionComments" name="positionComments" rows="4" cols="50"></textarea><br><br>

    <label for="playerAttitudeComments">Player Attitude Comments:</label><br>
    <textarea id="playerAttitudeComments" name="playerAttitudeComments" rows="4" cols="50"></textarea><br><br>

    <label for="coachComments">Coach Comments:</label><br>
    <textarea id="coachComments" name="coachComments" rows="4" cols="50"></textarea><br><br>

    <!-- Add a hidden input for the submitter's email address -->
    <input type="hidden" id="submitterEmail" name="submitterEmail">

    <button type="button" id="submitDataBtn">Submit Data</button>
  </form>

</body>
</html>

As you can see in the function with the HTML, I have console statements that don't show up. This leads me to belive that the HTML isn't loading the dropdown. I did check the debugger on chrome to see if there were any errors, and I am not seeing anything, which again leads me to believe the function isn't getting called on load.

Upvotes: 0

Views: 917

Answers (1)

Cooper
Cooper

Reputation: 64100

Here's how an example of how I did a similar thing:

html:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <title>SO Tools 2</title>
</head>
<body>
  <input type="text" size="20" id="str1" name="str1" placeholder="Enter String"><input type="text" id="str2" name="str1" size="8" placeholder="Length">
  <br><input type="button" value="Get Length" onClick="stringLength();">
  <br><input type="button" value="Clear Text" onClick="clearText();">
  <br><select id="sel1" onChange="getChangeValue(this);"></select>
  <script>
    window.onload = () => {
      google.script.run
      .withSuccessHandler((vs) => {
        updateSelect(vs);
        }).getSelectOptions101();
    }
    function stringLength() {
      console.log(`stringLength: Entry`);
      document.getElementById("str2").value = document.getElementById("str1").value.length
    }
    function clearText() {
      console.clear();
      console.log("clearText: Entry")
      var inputs = document.getElementsByTagName("input");
      for(var i = 0;i < inputs.length; i++) {
        if(inputs[i].type.toLowerCase() == "text") {
          inputs[i].value = "";
        }
      }
    }
    function updateSelect(vA,id="sel1"){
      var select = document.getElementById(id);
      select.options.length = 0; 
      vA.unshift("");
      for(var i=1;i<vA.length;i++){
        select.options[i] = new Option(vA[i][1],vA[i][0]);
      }
    }
    function getChangeValue(ob) {
      console.log('item: %s,value: %s',ob.options[ob.selectedIndex].text,ob.value);
      
    }

  </script>  
</body>
</html>

GS:

function soTools2() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile('ah3').setTitle("SO Tools 2"));
}
 
function getSelectOptions101(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,2).getValues();
  return vs;
}

Sheet0:

A B
1 COL1 COL2
2 1 Item1
3 2 Item2
4 3 Item3
5 4 Item4
6 5 Item5
7 6 Item6
8 7 Item7
9 8 Item8
10 9 Item9
11 10 Item10

Upvotes: 0

Related Questions