Search text - Google sheet & apps script tutorial.
In this tutorial we have to try to create such a Web project that helps us to find the math text from spreadsheet using apps script, javascript and html tables. You can see how this work in following example.
Let's know how I have created this web app. I have created two files in this app. First Code.gs and second Index.html . In Code.gs file, I have used this code.
Code.gs
function doGet(e) {
var htmlOutput = HtmlService.createTemplateFromFile('Index');
return htmlOutput.evaluate();
}
function searchtxt ( searchtext ) {
var fromss = SpreadsheetApp.openById('1EmyJvd0afqxLYCmfI_gFoO
0GLZQVtPDz1fvndKvW7Pg');
var fromSheet = fromss.getSheetByName('Users');
var lr = fromSheet.getLastRow();
var fromData = fromSheet.getDataRange().getValues();
var matchrows = [];
for (var i = 0; i < lr; i++) {
for(var j=0; j<fromData[i].length ; j++){
if(fromData[i][j] === searchtext){
matchrows.push(fromData[i]);
}}}
return matchrows;
}
Note: above function works on Case senstive, to remove it you have to replace
if(fromData[i][j] === searchtext)
by
if(fromData[i][j].toLowerCase() === searchtext.toLowerCase()) .
And in Index.html file, I have used following html code.
Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
</head>
<body>
<div class="w3-row" >
<table class="w3-table w3-bordered">
<tr class="w3-col s12 w3-light-red">
<td>
<input class="w3-col s12 w3-round w3-border w3-center"id="searchtext">
</td>
<td>
<input type="submit"value=" SEARCH "
class="w3-blue w3-hover-red w3-round w3-border" onclick="searchclick()"/>
</td>
<td id="search-info"></td>
</tr>
</table>
</div>
<div style="overflow: auto;height:480px;">
<div id="search-result"></div>
</div>
<script>
function searchclick() {
let searchtext = document.querySelector('#searchtext').value;
let updateLocation = document.querySelector('#search-info');
updateLocation.innerHTML = 'Searching for <b>'+searchtext+'</b>';
if (searchtext === ""){
updateLocation.innerHTML = "<span style='color:red'>
Type name to search!</span>";
}else{
function onFailure(error){
updateLocation.innerHTML = "<span style='color:red'>"+error+"</span>";
};
function onSuccess(resp){
updateLocation.innerHTML = "";
if(resp != ""){
var rsheet = "<table class='w3-table w3-bordered'>"+
"<thead style='white-space: nowrap'>"+
"<tr>"+
"<th>ID</th>"+
"<th>Name</th>"+
"<th>Contact</th>"+
"<th>Email</th>"+
"<th>Village</th>"+
"<th>Class</th>"+
"<th>Total</th>"+
"</tr>"+
"</thead>";
for(var i=0; i<resp.length; i++) {
rsheet += "<tr><td>"+resp[i][0]+"</td><td>"+resp[i][1]+
"</td><td>"+resp[i][2]+"</td><td>"+resp[i][3]+"</td><td>"+resp[i][4]+
"</td><td>"+resp[i][5]+"</td><td>"+resp[i][6]+" "+resp[i][7]+"</td></tr>";}
}else{
var rsheet = "No result found for your search '
<span style='color:red'><b>"+searchtext+"</b></span>'.";
}
document.getElementById("search-result").innerHTML = rsheet;
}
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.searchtxt(searchtext);}
};
</script>
</body>
</html>
>>>TRY TO CHECK OUT , IF ANY ERROR FOUND. PLEASE LET ME KNOW BY COMMENT.
I'LL TRY MY LEVEL BEST TO FIX THE PROBLEM.
THANKS FOR VISITING CodyLab
Have a nice day!
-------------------------- -------------------------