Messing up the dynamic dropdown. What am I doing wrong?

I am trying to add dropdown list dynamically to a WebApp using google appscript. I wrote a few lines of javascript code in the client side to communicate the server side to fetch the data from google-sheets. After a lot of trying, I’m somewhat successful. However, it looks like, whenever I click on the "Add Product" button, for first 1-2 times the array from which the dropdown is generated is empty. As a result the dropdown remains blank. However after 1 or 2 blank dropdowns the it starts working as it’s suppose to.

What am I doing wrong ?

I have 3 files-

  1. form.html
  2. code.gs
  3. js_script.html
  4. Link to the google sheet

Content of form.html

<body>
  <div class="container">
     <div class = "row">
       <h1>Order Form</h2>
     </div>                                    <!-- end of row -->
      
     <div class = "row">
       <input id="orderno" type="text" class="validate">
       <label for="orderno">Order Number</label>
     </div>                                    <!-- end of row -->

     <div class = "row">
       <input id="clientname" type="text" class="validate">
       <label for="clientname">Client Name</label>
     </div>                                    <!-- end of row -->

     <div class = "row">
       <input id="clientaddr" type="text" class="validate">
       <label for="clientaddr">Client Address</label>
     </div>                                    <!-- end of row -->

     <div class = "row">
       <input id="clientphone" type="text" class="validate">
       <label for="clientphone">Client Phone Number</label>
     </div>                                    <!-- end of row -->
      
     <div class = "row">
       <input id="ordertype" type="text" class="validate">
       <label for="ordertype">Order Type</label>
     </div>                                    <!-- end of row -->

     <div id="productsection"></div>
     <div class = "row">   
       <button id="addproduct">Add Product</button>
     </div>                                    <!-- end of row -->
      
     <div class = "row">
       <button id="submitBtn">Submit</button>
     </div>                                    <!-- end of row -->                    
   </div>                                      <!-- End of "container" class -->         
   <?!= include("js_script"); ?>
</body>

Content of code.gs

const ssID = "1YKZYgKctsXU3DKTidVVPUhmPXUkzjjocaiMz1S76JAE";
const ss = SpreadsheetApp.openById(ssID);

function doGet(e){
  Logger.log(e);
  return HtmlService.createTemplateFromFile("form").evaluate();
}

function include(fileName){
  return HtmlService.createHtmlOutputFromFile(fileName).getContent();
}

function appendDataToSheet(userData){
  const ws = ss.getSheetByName("orders");

  ws.appendRow([new Date(), userData.orderNumber, userData.clientName, userData.clientAddress, userData.clientPhone, userData.orderType, userData.products].flat());
}

function getOptionArray(){
  const ws = ss.getSheetByName("product_list");

  const optionList = ws.getRange(2, 1, ws.getRange("A2").getDataRegion().getLastRow() - 1).getValues()
              .map(item => item[0]);

  return optionList;
}

function logVal(data){
  Logger.log(data);
}

Content of js_script.html

<script>
  let counter = 0;
  let optionList = [];
  
  document.getElementById("submitBtn").addEventListener("click", writeDataToSheet);
  document.getElementById("addproduct").addEventListener("click", addInputField);

  function addInputField(){
    counter++;
    
    // The idea is, everytime when "add product" button is clicked, the following element must be added to the "<div id="productoption></div>" tag.

    // <div class="row">
    //   <select id="productX">
    //     <option>option-X</option>
    //   </select>
    // </div>
    
    const newDivTag = document.createElement('div');
    const newSelectTag = document.createElement('select');

    newDivTag.class = "row";
    newSelectTag.id = "product" + counter.toString();

    google.script.run.withSuccessHandler(updateOptionList).getOptionArray();

    google.script.run.logVal(optionList);                //  This is just to test the optionList array if it's updated or not

    for(let i = 0; i < optionList.length; i++){
      const newOptionTag = document.createElement('option');
      newOptionTag.textContent = optionList[i];
      newOptionTag.value = optionList[i];
      newSelectTag.appendChild(newOptionTag);
    }

    newDivTag.appendChild(newSelectTag);
    document.getElementById('productsection').appendChild(newDivTag);
  }

  function writeDataToSheet(){
    const userData = {};
    userData.orderNumber = document.getElementById("orderno").value;
    userData.clientName = document.getElementById("clientname").value;
    userData.clientAddress = document.getElementById("clientaddr").value;
    userData.clientPhone = document.getElementById("clientphone").value;
    userData.orderType = document.getElementById("ordertype").value;
    userData.products = [];

    for(let i = 0; i < counter; i++) {
      let input_id = "product" + (i+1).toString();
      userData.products.push(document.getElementById(input_id).value);
    }

    google.script.run.appendDataToSheet(userData);
  }

  function updateOptionList(arr){
    optionList = arr.map(el => el);
  }
</script>

>Solution :

About your current issue of However, it looks like, whenever I click on the "Add Product" button, for first 1-2 times the array form which the dropdown is generated is empty. As a result the dropdown remains blank. However after 1 or 2 blank dropdowns the it starts working as it's suppose to., when I saw your script, I thought that the reason for your issue might be due to that google.script.run is run with the asynchronous process. If my understanding is correct, how about the following modification?

In this case, your js_script.html is modified.

Modified script:

<script>
let counter = 0;
// let optionList = []; // Removed

document.getElementById("submitBtn").addEventListener("click", writeDataToSheet);
document.getElementById("addproduct").addEventListener("click", addInputField);

// Modified
function addInputField(){
  counter++;
  const newDivTag = document.createElement('div');
  const newSelectTag = document.createElement('select');
  newDivTag.class = "row";
  newSelectTag.id = "product" + counter.toString();
  google.script.run.withSuccessHandler(arr => {
    const optionList = updateOptionList(arr);
    google.script.run.logVal(optionList);
    for(let i = 0; i < optionList.length; i++){
      const newOptionTag = document.createElement('option');
      newOptionTag.textContent = optionList[i];
      newOptionTag.value = optionList[i];
      newSelectTag.appendChild(newOptionTag);
    }
    newDivTag.appendChild(newSelectTag);
    document.getElementById('productsection').appendChild(newDivTag);
  }).getOptionArray();
}

function writeDataToSheet(){
  const userData = {};
  userData.orderNumber = document.getElementById("orderno").value;
  userData.clientName = document.getElementById("clientname").value;
  userData.clientAddress = document.getElementById("clientaddr").value;
  userData.clientPhone = document.getElementById("clientphone").value;
  userData.orderType = document.getElementById("ordertype").value;
  userData.products = [];
  for(let i = 0; i < counter; i++) {
    let input_id = "product" + (i+1).toString();
    userData.products.push(document.getElementById(input_id).value);
  }
  google.script.run.appendDataToSheet(userData);
}

// Modified
function updateOptionList(arr){
  return arr.map(el => el); // I cannot understand this mean.
}
</script>

or, in this case, updateOptionList might not be required to be used as follows.

<script>
let counter = 0;
// let optionList = []; // Removed

document.getElementById("submitBtn").addEventListener("click", writeDataToSheet);
document.getElementById("addproduct").addEventListener("click", addInputField);

// Modified
function addInputField(){
  counter++;
  const newDivTag = document.createElement('div');
  const newSelectTag = document.createElement('select');
  newDivTag.class = "row";
  newSelectTag.id = "product" + counter.toString();
  google.script.run.withSuccessHandler(optionList => {
    google.script.run.logVal(optionList);
    for(let i = 0; i < optionList.length; i++){
      const newOptionTag = document.createElement('option');
      newOptionTag.textContent = optionList[i];
      newOptionTag.value = optionList[i];
      newSelectTag.appendChild(newOptionTag);
    }
    newDivTag.appendChild(newSelectTag);
    document.getElementById('productsection').appendChild(newDivTag);
  }).getOptionArray();
}

function writeDataToSheet(){
  const userData = {};
  userData.orderNumber = document.getElementById("orderno").value;
  userData.clientName = document.getElementById("clientname").value;
  userData.clientAddress = document.getElementById("clientaddr").value;
  userData.clientPhone = document.getElementById("clientphone").value;
  userData.orderType = document.getElementById("ordertype").value;
  userData.products = [];
  for(let i = 0; i < counter; i++) {
    let input_id = "product" + (i+1).toString();
    userData.products.push(document.getElementById(input_id).value);
  }
  google.script.run.appendDataToSheet(userData);
}
</script>

Note:

Reference:

Leave a Reply