I am trying to develop a WebApp in which user enters his/her id and script will search that ID in the Google sheet and retrieve the respective data row from sheet which contains that ID. Now script is searching the ID in sheet and retrieve the specific row as an array. But I want to import that data in Table in WebApp. But couldn't find any reasonable solution. Following is the script:
function doGet() {
return HtmlService
.createTemplateFromFile('Index')
.evaluate();
}
function FetchData(val) { //'val' is entered by user in WebApp
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Sheet1");
var lc=formSS.getLastColumn();
var lr=formSS.getLastRow();
for (var i=2;i<=lr;i++)
{
var UID = formSS.getRange(i, 1).getValue();
if (val==UID) //Searching Google Sheet ID's and User Entered ID
{
var res=formSS.getRange(i, 1, 1,lc).getValues()[0];
return res; //contains the data of specific row which we want to put in WebApp Table
}
}
}
This is HTML Code
<body>
<script>
document.getElementById("btn").addEventListener("click",SearchID);
function SearchID() //Searching ID in Google Sheet
{
var id=document.getElementById("userid").value;
google.script.run.FetchData(id);
document.getElementById("userid").value="";
}
</script>
</body>
</html>
Is there any way that we can put this data in the table of WebApp HTML page. Any Guidance would be much appreciated. This is sheet Link:
https://docs.google.com/spreadsheets/d/119wJ3sBY3coGpEo2CHDnW1hPv_WQbgRaQKUwv7HxyFY/edit?usp=sharing
As others mentioned, you need to construct a HTML table based on the results received from the server. getValues() returns Object[][], but in your case, the function returns when it finds the first result, so you have only one row.
After receiving that, your useData() function should create a TABLE element using HTML syntax, so you need to add tags like <TABLE>, <TR>, <TH> and <TD>. These tags can be added to a variable that is used to construct the table, appending tags and their contents as you iterate over the data received:
function useData(data) {
var output = document.getElementById('OutPut');
// Start building table
var html = '<table>';
// Add table header
html += `<tr>
<th>Unique ID</th>
<th>Student name</th>
<th>Course</th>
<th>Issued on</th>
<th>certificate link</th>
</tr>`;
// Add table row, assuming there's only one row based on what is being done by Apps Script
html += '<tr>';
for (var col = 0; col < data[0].length; col++) {
html += '<td>' + data[0][col] + '</td>';
}
html += '</tr>';
// Stop building table
html += '</table>';
// Add table to existing element
output.innerHTML = html;
}