How to search data quickly from Google sheet using apps script
Google Sheets is a powerful tool for managing and analyzing data, but sometimes it can be difficult to find the information you need quickly and easily. One solution to this problem is to use Google Apps Script to create a smart search function that can help you quickly locate the data you need.
Apps Script is a scripting language that allows you to create custom functions and automate tasks within Google Sheets. With a few lines of code, you can create a custom search function that can search through your data and return the results you need.
To create a smart search function, you will first need to open your Google Sheet and select "Extensions" from the menu. From there, select "Script editor" to open the Apps Script editor.
Once you are in the script editor, you can start by creating a new function that will be used as the search function. This function can take in a search term as a parameter and then search through the data in your sheet to find any matches.
Let's have a look of example sheet:
This script is for a Google Sheets spreadsheet. It defines two functions, "fin()" and "subf()", which interact with the spreadsheet in different ways. The first line of the script opens the spreadsheet by its ID, and assigns it to the variable "wss". Then it assigns the first sheet in the spreadsheet to the variable "sn".
adtxt
The first function, "fin(x)", takes in a parameter "x" and searches for it in the second column of the sheet. If it finds a match, it retrieves the values in the next 5 columns and returns them as an array. If it does not find a match, it returns an empty array.
adtxt
The second function, "subf(v)", takes in an object "v" with properties "id", "name", "v0", "v1", "v2", "v3", and "v4". It then searches for the value of "v.id" in the second column of the sheet. If it finds a match, it updates the values of the next 5 columns with the values of "v.name", "v.v0", "v.v1", "v.v2", "v.v3" and "v.v4". And return "Update successfull" . If it does not find a match, it appends a new row to the sheet with the values of "v.id", "v.name", "v.v0", "v.v1", "v.v2", "v.v3", and "v.v4" and return "Successfully added".
Code.gs
function doGet(e){
return HtmlService.createTemplateFromFile("index")
.evaluate()
.setTitle("Smart search - CodyLab");
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
//padding zero
var z = (num, places) => String(num).padStart(places, '0');
var wss = SpreadsheetApp.openById("1E4HnFKPybVcVjZQdozbbcHD0EhVBtUJMhSvHGpBIVa8")
var sn = wss.getSheetByName("Sheet1");
function fin(x){
var l = sn.getLastRow()+1;
var flag = 1 ;
for(var i = 1;i <= l;i++){
if(sn.getRange(i,2).getValue()==x){
flag = 0;
var a = sn.getRange(i,3).getValue();
var b = sn.getRange(i,4).getValue();
var c = sn.getRange(i,5).getValue();
var d = sn.getRange(i,6).getValue();
var e = sn.getRange(i,7).getValue();
var f = sn.getRange(i,8).getValue();
return [a,b,c,d,e,f];
}}if(flag==1){
return[];
}}
function subf(v){
var l = sn.getLastRow();
var id = "1"+z(l,3)
var flag = 1 ;
for(var i = 1;i <= l;i++){
if(v.id==sn.getRange(i,2).getValue()){
flag = 0;
sn.getRange(i,3).setValue(v.name);
sn.getRange(i,4).setValue(v.v0);
sn.getRange(i,5).setValue(v.v1);
sn.getRange(i,6).setValue(v.v2);
sn.getRange(i,7).setValue(v.v3);
sn.getRange(i,8).setValue(v.v4);
var data = "Update successfull.";
return data;}}
if(flag==1){
var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'yyyy-MM-dd HH:mm:ss');
sn.appendRow([dt,id,v.name,v.v0,v.v1,v.v2,v.v3,v.v4])
sn.getRange(l+1,1).setValue(dt).setNumberFormat('@STRING@');
var data = 'Successfully added.';
return data;
}};
This is an HTML document that creates a form with various input fields for ID, Name, and multiple Value fields. It also includes links to CSS stylesheets from w3schools and Font Awesome for styling and icons. There is also an included JavaScript function for submit and reset button, and a function for searching. The form is enclosed in a container with a class for styling from w3css library. The form has a title "Smart Search" and is using the method onsubmit to prevent default form submission and call subm() function instead.
index.html
<!DOCTYPE html>
<html>
<title>CodyLab</title>
<base target="_top">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<?!= include('js'); ?>
<?!= include('popup'); ?>
<body>
<div class="w3-row w3-border w3-small">
<form id="lab" onsubmit="event.preventDefault();subm(this)">
<div class="w3-container w3-half w3-white w3-round-xlarge"style="margin-top:40px;margin-left:25%;">
<div class="w3-center"><i class="fa fa-search w3-xlarge w3-margin-top"> Smart search</i></div>
<div class="w3-row-padding">
<div class="w3-col s4">
<lavel>ID:</lavel>
<input type="text"name="id"id="id"placeholder="Enter Id"class="w3-input w3-border w3-round-large"onkeyup="myFun()">
</div>
<div class="w3-col s4">
<lavel>Name:</lavel>
<input type="text"name="name"id="name"placeholder="Name"class="w3-input w3-border w3-round-large"required>
</div>
<div class="w3-col s4">
<lavel>Value0:</lavel>
<input type="text"name="v0"id="v0"placeholder="value0"class="w3-input w3-border w3-round-large">
</div>
<div class="w3-col s6">
<lavel>Value1:</lavel>
<input type="text"name="v1"id="v1"class="w3-input w3-border w3-round-large">
</div>
<div class="w3-col s6">
<lavel>Value2:</lavel>
<input type="text"name="v2"id="v2"class="w3-input w3-border w3-round-large">
</div>
<div class="w3-col s6">
<lavel>Value3:</lavel>
<input type="tel"name="v3"id="v3"class="w3-input w3-border w3-round-large">
</div>
<div class="w3-col s6">
<lavel>Value4:</lavel>
<input type="text"name="v4"id="v4"class="w3-input w3-border w3-round-large">
</div>
<div class="w3-col s12 w3-center w3-margin-top w3-margin-bottom">
<input type="submit"class="w3-button w3-blue w3-round-large w3-margin-right">
<input type="reset"class="w3-button w3-light-grey w3-round-large">
</div>
</div>
</div>
</form>
</div>
</body>
</html>
This code is a combination of CSS and HTML that creates three modals (pop-up windows) for a website. The first modal, "lod", displays a spinning loader animation. The second modal, "succ", displays a message with a green checkmark and text that says "Awesome!" when triggered. The third modal, "failp", displays a message with a red X and text that says "Failure!" when triggered. The modals can be closed by clicking the OK button.
popup.html
<style>
body{font-family:times;}
.loader {margin-left:45%;
border: 2px solid #f3f3f3;
border-radius: 50%;
border-top: 2px solid #3498db;
width: 120px;
height: 120px;
-webkit-animation: spin 2s linear infinite; /* Safari */
animation: spin 2s linear infinite;
}
/* Safari */
@-webkit-keyframes spin {
0% { -webkit-transform: rotate(0deg); }
100% { -webkit-transform: rotate(360deg); }
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}
</style>
<div id="lod" class="w3-modal">
<div class="w3-center">
<div class="loader"></div>
</div>
</div>
<div id="succ" class="w3-modal">
<div class="w3-modal-content w3-white w3-round-large"style="width:300px;">
<div class="w3-center w3-margin-bottom">
<i class="w3-xxxlarge w3-round-xxlarge w3-text-green 3-bordered">✓</i>
<h4 class="w3-text-green">Awesome!</h4>
<div class="w3-container">
<p id="rsp"></p>
</div>
<button onclick="document.getElementById('succ').style.display='none'" class="w3-button w3-center w3-text-green">OK</button><br><br>
</div>
</div>
</div>
<div id="failp" class="w3-modal">
<div class="w3-modal-content w3-white w3-round-large"style="width:300px;">
<div class="w3-center w3-margin-bottom">
<i class="w3-xxxlarge w3-round-xxlarge w3-text-red 3-bordered">✗</i>
<h4 class="w3-text-red">Failure!</h4>
<div class="w3-container">
<p id="rsf"></p>
</div>
<button onclick="document.getElementById('failp').style.display='none'" class="w3-button w3-center w3-text-red">OK</button><br><br>
</div>
</div>
</div>
This script contains two functions for interacting with a Google Script API. The first function, myFun(), is triggered when a user inputs a value into a form field with the id "id". If the length of the input value is equal to 4, it disables the input field, shows a loading spinner, and runs a function named "fin" from the Google Script API, passing the input value as a parameter. If the input value's length is not equal to 4, it re-enables the input field and hides the loading spinner. The second function, subm(v), is triggered when a user submits a form. It shows a loading spinner, runs a function named "subf" from the Google Script API, passing a parameter "v" and sets success and failure handlers that display success/failure messages and hide loading spinner after that.
js.html
<script>
function myFun(){
let x = document.getElementById("id").value;
if(x.length==4){
document.getElementById("id").disabled = true;
document.getElementById('lod').style.display='block';
google.script.run.withFailureHandler(re)
.withSuccessHandler(re)
.fin(x);}else{
document.getElementById("id").disabled = false;
document.getElementById('lod').style.display='none'
}
}
function re(v){
document.getElementById('lod').style.display='none'
document.getElementById("id").disabled = false;
if(v!=""){
var a = document.getElementById("name").value=v[0];
var b = document.getElementById("v0").value=v[1];
var c = document.getElementById("v1").value=v[2];
var d = document.getElementById("v2").value=v[3];
var e = document.getElementById("v3").value=v[4];
var f = document.getElementById("v4").value=v[5];
}else{
document.getElementById("lab").reset();
}
}
</script>
<script>
function subm(v){
document.getElementById('lod').style.display='block'
google.script.run.withFailureHandler(fail)
.withSuccessHandler(pass)
.subf(v);}
function fail(v){
document.getElementById('lod').style.display='none'
document.getElementById('failp').style.display='block'
document.getElementById("rsf").innerHTML = v;}
function pass(v){
document.getElementById('lod').style.display='none'
document.getElementById('succ').style.display='block'
document.getElementById("rsp").innerHTML = v;
document.getElementById("lab").reset();}
</script>
>>>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
Have a nice day!
-------------------------- -------------------------