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-
form.htmlcode.gsjs_script.html- Link to the google sheet
Content of form.html-
<body>
<div >
<div class = "row">
<h1>Order Form</h2>
</div> <!-- end of row -->
<div class = "row">
<input id="orderno" type="text" >
<label for="orderno">Order Number</label>
</div> <!-- end of row -->
<div class = "row">
<input id="clientname" type="text" >
<label for="clientname">Client Name</label>
</div> <!-- end of row -->
<div class = "row">
<input id="clientaddr" type="text" >
<label for="clientaddr">Client Address</label>
</div> <!-- end of row -->
<div class = "row">
<input id="clientphone" type="text" >
<label for="clientphone">Client Phone Number</label>
</div> <!-- end of row -->
<div class = "row">
<input id="ordertype" type="text" >
<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 >
// <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>
CodePudding user response：
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:
When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".