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