0

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);
   

}
2
  • I just posted an answer. If you need something more specific, please add a brief description of your search/research efforts or post more specific questions (try to identify the underliying problems and fix them one by one). – Rubén Oct 4 '20 at 16:27
  • 1
    Thank you, yes that gave me an idea and your comment was really appreciated I think I need to focus on changing my logic more when I do this stuff. – Junixxx Oct 15 '20 at 22:26
0

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.