Google-sheets – Generate Table from Sheet to HTML Web App using Google App Script

google sheetsgoogle-apps-script

I am working on a web app via Google Script where every time I search for a specific text it will generate a table that I linked in Google Sheet in that Web App.

This is what I have so far:

  • Google Sheet which has a QUERY function to populate my desired result
  • Web App Script where I can insert input and it will be added to the Google Sheet and trigger the Query
  • Web App Script where it fetches the table from Google Sheet and displays it on the Web App

The script seems to be working fine because when I enter the text in the Input field it is being added but it does not automatically show in the Web App the QUERY Table result and seems that I have to hit refresh first before it will show the new QUERY Table in Google Sheet.

Here is my HTML JS Script:

<html>
  <head>
    <base target="_top">
  </head>
  <body>
  
  <div>
  
  <label for="fname">First Name</label><input type="text" id="fname">
  <button id="btn">Search</button>
  
  </div>
  <br><br>
  <div>
  <table>
    <thead>
       <tr>
         <th>First Name<th>
       </tr>
    </thead>
    
    <tbody id="FnameData">
    
    </tbody>
  </table>
  </div>
  
 <script>
 
     document.getElementById("btn").addEventListener("click",SearchFunction);
     
     
     
     document.addEventListener("DOMContentLoaded", function() {
     google.script.run.withSuccessHandler(generateTable).searchFunc();
     
     });
     
     
     
     function SearchFunction() {
     
         var sdata = document.getElementById("fname").value;
         google.script.run.sQuery(sdata);
     
      }
   
     
     function generateTable(tableDataArray) {
  
         var tbody = document.getElementById("FnameData");
     
         tableDataArray.forEach(function(r){
     
         var row = document.createElement("tr");
         var col1 = document.createElement("td");
     
         col1.textContent = r[0];
         row.appendChild(col1);
         tbody.appendChild(row);
     
         }) 
       }
       
  
  </script>
    
  </body>
</html>

Here are my JavaScript Scripts in Google App Scripts:

Generating the Table

function searchFunc() {
 
  var url = "";
  var SSheet = SpreadsheetApp.openByUrl(url);
  
  var WSheet = SSheet.getSheetByName("Test");
  var Data = WSheet.getRange(5, 2, WSheet.getLastRow()-1, 1).getValues();
  //Logger.log(Data);
  return Data;
  
}

Adding Data on the QUERY Table

function sQuery(EData) {
 
  var SS = SpreadsheetApp.openByUrl("");
  var WS = SS.getSheetByName("Test");
  
  var WData = WS.getRange(1, 1).setValue(EData);
   

}

Best Answer

On the client-side code (the Google Apps Script HTMLOutput object, HTML/CSS/JavaScript) you could use the JavaScript global object Promise to handle the sequence of asynchronous tasks (like calling google.script.run)

Another option might be to change the logic of your web app so instead calling different functions call only one that call the server-side function in the sequence that you need.

NOTE: On the server side code you might need to add SpreadsheetApp.flush().

Related Topic