Google Apps Script动态下拉异常：前几次点击空白如何解决？

我正在使用google appscript为WebApp添加动态下拉列表，通过客户端javascript代码调用服务端从google-sheets获取数据。经过多次尝试后已部分成功，但点击“Add Product”按钮时，前1-2次生成下拉的数组为空，导致下拉菜单空白，之后可正常工作。相关文件内容如下：

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

2. 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);
}

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

问题根源：异步操作执行顺序错误 #

google.script.run是异步执行的，在addInputField函数中调用google.script.run.withSuccessHandler(updateOptionList).getOptionArray()后，代码不会等待服务端返回数据，而是直接执行后续的logVal和for循环逻辑：

1. 第一次点击按钮时，异步请求还未返回，optionList仍是初始空数组，生成的下拉菜单自然没有选项
2. 异步请求完成后，updateOptionList才会将服务端返回的数组赋值给optionList
3. 第二次点击时，optionList已有数据，下拉菜单正常显示

解决方案 #

方案1：页面加载预加载数据（推荐）

页面初始化时就请求产品数据，后续点击直接使用缓存数据，提升体验：

<script>
  let counter = 0;
  let optionList = [];
  
  // 页面加载时预加载产品数据
  window.addEventListener('load', () => {
    google.script.run.withSuccessHandler(updateOptionList).getOptionArray();
  });

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

  function addInputField(){
    counter++;
    
    const newDivTag = document.createElement('div');
    const newSelectTag = document.createElement('select');

    newDivTag.className = "row"; // 修复：DOM元素类名属性为className，而非class
    newSelectTag.id = "product" + counter.toString();

    // 直接使用预加载的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);
  }

  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，直接赋值即可
  }
</script>

方案2：每次点击请求最新数据

若需要保证产品数据实时更新，可将生成下拉菜单的逻辑放到异步请求的回调函数中：

function addInputField(){
  counter++;
  
  const newDivTag = document.createElement('div');
  const newSelectTag = document.createElement('select');

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

  // 每次点击请求最新数据，回调中生成选项
  google.script.run.withSuccessHandler((arr) => {
    optionList = arr;
    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();
}

额外修复点

• 将newDivTag.class = "row"改为newDivTag.className = "row"，DOM元素的类名属性是className，直接使用class会因保留字问题失效。

内容的提问来源于stack exchange，提问作者arniem