CODEWITHSUNDEEP
javascripthtmlgoogle-apps-scriptgoogle-sheets
GPni
GPni

Reputation: 143

Show in HTML the Options Value which is the output of the code from Google Apps Script

EDITED:

I have coded in JavaScript to call a function on Google Apps Script which will check my data in google sheet and get the values needed.

code for JavaScript:

   function setTime(){
   var lookDate = document.getElementById("subDate").value;
   google.script.run.withSuccessHandler(dateOk).timeCheck(lookDate);
   }
   
   function dateOk(dateData){
   document.getElementById("subTime").innerHTML = dateData;
   console.log(dateData);
   }

Sample scenario for this, When a user select a Date in my Web App, it will run the function setTime() and then it will go to the Google Apps Script code. I have two functions function timeCheck(lookDate) and function testRow(dataDisable,lookDate). Function timeCheck(lookDate) will check my values in my Google Sheet and will look for the limits, and function testRow(dataDisable,lookDate) will look for the limit values and change the values in its corresponding column in my Google Sheet.

for Google Apps Script:

function timeCheck(lookDate){
  
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Test_Data");  
  var myData = ws.getRange(2, 7, ws.getLastRow()-1, 2).getValues();
  var arrData = [];
  myData.forEach(function(row){
    
  var lookup = lookDate;
  var dte = new Date (row[0]);
  var form = Utilities.formatDate(dte, 'GMT+8', 'MMM dd, yyyy');

    if(form == lookup){
      arrData.push(row[1]); 
    }    
  });
  var myVals = arrData;
  var CheckLimitReached = function (V) {
    var records= {};
    V.forEach(function (x) { records[x] = (records[x] || 0) + 1; });
    var limit_reached = Object.keys(records).filter(function (R) {
      return records[R] >= 3;});
    return limit_reached;
    
  };
   var dataDisable = CheckLimitReached(myVals);
  //testRow(dataDisable,lookDate);
  var list1= testRow(dataDisable,lookDate)
  return list1 
}



function testRow(dataDisable,lookDate){

       var ss = SpreadsheetApp.openByUrl(url); 
  var ts = ss.getSheetByName("Time_Select");
  var checkData = ts.getRange(1, 1, 1, ts.getLastColumn()).getDisplayValues()[0];

  var index = checkData.indexOf(lookDate)+1;
  var x = [];
  var dateValues = ts.getRange(2, index, ts.getLastRow()-1, 1).getValues();
  //var checkSplit = dateValues.map(function(row){return row[0]; });
  for(var i=0;i<dataDisable.length;i++){
    for(var j=0;j<dateValues.length;j++){
      if(dataDisable[i]==dateValues[j][0]){
        dateValues[j][0]="Not Available"
      }
    }
  }
  ts.getRange(2, index, ts.getLastRow()-1, 1).setValues(dateValues);
  var listVal = ts.getRange(2, index, ts.getLastRow()-1, 1);
  var list1 = listVal.getValues();//.toString();
  return list1;
}

The log result of this function is:

[19-09-10 13:36:58:034 HKT] [[Not Available], [Not Available], [10:00 AM], [11:00 AM], [12:00 NN], [1:00 PM], [Not Available], [3:00 PM]

My problem is how to reflect the values in my html as options.

Here is the link of my Google Sheet: https://docs.google.com/spreadsheets/d/1lEfzjG1zzJVPMN8r-OpeZm6q9_IqSwk9DNCEY-q7ozY/edit?usp=sharing

and here is my html code for the web app:

<!-- DATE SELECTION -->
       <div class="row">
          <div class="input-field col s4">
             <input id="subDate" type="text" class="datepicker">
             <label for="subDate">Select Date</label>
          </div> <!-- CLOSE TIME FIELD -->
       
 <!-- TIME SELECTION -->      
          <div class="input-field col s4">
             <select id="subTime">
                <option value="" disabled selected>Choose your preferred time</option>                  
              <?!= list1; ?>
             </select>
             <label>Select Time</label>
          </div> <!-- CLOSE TIME FIELD -->
       </div> <!-- CLOSE ROW -->

Thank you in advance for your answers and inputs.

EDIT/ UPDATE:

I've tried this code in JavaScript to remove the existing options and add the values as new options in HMTL. Unfortunately nothing happened.

JavaScript:

function dateOk(dateData){
console.log(dateData)
var selectRemove = document.getElementById("subTime");
selectRemove.innerHTML = "";
           
for (var option in dateData){
var newOption = document.createElement("option");
newOption.innerHTML = option;
selectRemove.options.add(newOption);
}

Problem: How to remove the existing Select Options and replace it with the return values from the function? Or How to create a dropdown list box with the values reflected?

Upvotes: 2

Views: 289

Answers (1)

TheMaster
TheMaster

Reputation: 50644

Issue:

  • No return value provided for any function called.
  • innetHtml should be innerHTML

Snippet:

function testRow(dataDisable,lookDate){
  /*...rest of code*/
  var list1 = listVal.getValues();
  Logger.log(list1);
  return list1;//ADDED: returns to `timecheck`
  //REMOVEDdoGet(list1); 
}

function timeCheck(lookDate){
  /*...rest of code*/
  var dataDisable = CheckLimitReached(myVals);
  return testRow(dataDisable,lookDate);//MODIFIED return the value of function `testRow` from `timeCheck` to `dateOk`
}

function dateOk(dateData){
 console.log(dateData);
 document.getElementById("subTime").innerHTML = dateData.reduce((a,c)=>{
    return a+'<option>' + (c[0] === 'Not Available' ? '' : c[0]) + '</option>'
 },'');
}

To Read and practice:

Live Snippet:

<div class="input-field col s4">
<select id="subTime">
   <option value="" disabled selected>Choose your preferred time</option>                  
 
</select>
<label>Select Time</label>
 </div> <!-- CLOSE TIME FIELD -->
<script>
function dateOk(dateData){
 console.log(dateData);
 document.getElementById("subTime").innerHTML = dateData.reduce((a,c)=>{
return a+'<option>' + (c[0] === 'Not Available' ? '' : c[0]) + '</option>'
 },'');
}
dateOk([["Not Available"], ["10:00AM"], ["5:00PM"]]);//call function as if called from successHandler
</script>

Upvotes: 2

Related Questions