AutoComplete with smart search

Here, Auto complete - smart search refers to a feature in computer systems where as a user starts typing in a input box, the system provides suggestions for completion of the search query. The suggestions are generated based on Google sheet refered column value. The aim of this feature is to make the search process faster and easier for the user.

This is a JavaScript code that runs on Google Apps Script to perform various functions in a Google Spreadsheet.

doGet(e) function returns the HTML template file "index" and sets the title of the page to "CodyLab- auto complete".
var z is a function to pad a number with leading zeros to the specified number of places.
var ws opens the Google Spreadsheet using its unique ID.
var sn and var sv refer to two sheets within the Google Spreadsheet with names "Sheet1" and "Sheet2" respectively.
include(filename) function returns the content of an HTML file.
auto() function retrieves values from columns B to H (2 to 8) in the "Sheet1" and returns a two-dimensional array.
fin(x) function searches for a match of a value 'x' in column B (2) of "Sheet1". If a match is found, the function returns an array of values from columns C to H (3 to 8) for that row. If no match is found, it returns an empty array.
subf(v) function takes an object 'v' as input, which contains properties 'id', 'name', 'v0', 'v1', 'v2', 'v3', 'v4'. It searches for a match of 'v.id' in column B (2) of "Sheet1". If a match is found, the function appends the values of 'v' to the "Sheet2". If no match is found, it appends the values of 'v' to the "Sheet1".

Code.gs

function doGet(e){

return HtmlService.createTemplateFromFile("index")

.evaluate()

.setTitle("CodyLab- auto complete");

}

var z = (num, places) => String(num).padStart(places, '0');

var ws = SpreadsheetApp.openById(" ")

var sn = ws.getSheetByName("Sheet1");

var sv = ws.getSheetByName("Sheet2");

function include(filename) {

return HtmlService.createHtmlOutputFromFile(filename).getContent();

}

function auto(){

var v1 = sn.getRange('b2:b').getValues()

var v2 = sn.getRange('c2:c').getValues()

var v3 = sn.getRange('d2:d').getValues()

var v4 = sn.getRange('e2:e').getValues()

var v5 = sn.getRange('f2:f').getValues()

var v6 = sn.getRange('g2:g').getValues()

var v7 = sn.getRange('h2:h').getValues()

return [v1,v2,v3,v4,v5,v6,v7];

}

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 j = sv.getLastRow();

var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'yyyy-MM-dd HH:mm:ss');

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;

sv.appendRow([dt,v.id,v.name,v.v0,v.v1,v.v2,v.v3,v.v4])

sv.getRange(j+1,1).setNumberFormat('@STRING@');

var data = 'Added in Sheet2';

return data;}}

if(flag==1){

sn.appendRow([dt,id,v.name,v.v0,v.v1,v.v2,v.v3,v.v4])

sn.getRange(l+1,1).setNumberFormat('@STRING@');

var data = 'Added in Sheet1';

return data;

}};

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"> <link rel="stylesheet" href="//code.jquery.com/ui/1.13.2/themes/base/jquery-ui.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 + autoComplete</i></div> <div class="w3-row-padding"> <div class="w3-col s4 ui-widget"> <lavel for="id">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 ui-widget"> <lavel for="name">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 ui-widget"> <lavel for="v0">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 ui-widget"> <lavel for="v1">Value1:</lavel> <input type="text"name="v1"id="v1"class="w3-input w3-border w3-round-large"> </div> <div class="w3-col s6 ui-widget"> <lavel for="v2">Value2:</lavel> <input type="text"name="v2"id="v2"class="w3-input w3-border w3-round-large"> </div> <div class="w3-col s6 ui-widget"> <lavel for="v3">Value3:</lavel> <input type="tel"name="v3"id="v3"class="w3-input w3-border w3-round-large"> </div> <div class="w3-col s6 ui-widget"> <lavel for="v4">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>

js.html

There's a function "subm" that is called when the form is submitted. It displays a loading icon and runs the "subf" function on the Google Script server, with "fail" and "pass" functions as failure and success handlers, respectively. The "fail" function displays an error message, while the "pass" function displays a success message and resets the form.

$(document).ready(function() {autoComplete();});

function autoComplete(){

google.script.run.withSuccessHandler(function(ar){

listA = [];

ar[0].forEach(function(item, index){

listA.push(item[0]);

});

$("#id").autocomplete({

source: listA

});

listB = [];

ar[1].forEach(function(item, index){

listB.push(item[0]);

});

$("#name").autocomplete({

source: listB

});

listC = [];

ar[2].forEach(function(item, index){

listC.push(item[0]);

});

$("#v0").autocomplete({

source: listC

});

listD = [];

ar[3].forEach(function(item, index){

listD.push(item[0]);

});

$("#v1").autocomplete({

source: listD

});

listE = [];

ar[4].forEach(function(item, index){

listE.push(item[0]);

});

$("#v2").autocomplete({

source: listE

});

listF = [];

ar[5].forEach(function(item, index){

listF.push(item[0]);

});

$("#v4").autocomplete({

source: listF

});

listG = [];

ar[6].forEach(function(item, index){

listG.push(item[0]);

});

$("#v4").autocomplete({

source: listG

});

}).auto();

}

</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>

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>

The code provides styles for three modal elements with ids lod, succ, and failp respectively. The first modal, lod, contains a spinning loader with a border that rotates continuously using a 2 second animation. The second modal, succ, displays a green checkmark, a "Awesome!" message, and a response message in a paragraph with id "rsp". The third modal, failp, displays a red cross mark, a "Failure!" message, and a response message in a paragraph with id "rsf". The modals are displayed and hidden when the respective buttons are clicked.

popup.html

<style>

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

<h4 class="w3-text-green">Awesome!</h4>

</div>

</div>

<h4 class="w3-text-red">Failure!</h4>

</div>

</div>

LIVE DEMO

CodyLab

AutoComplete with smart search

Post a Comment

How to create a template for AppsScript project

Online school result using google sheet and apps script

School Management Software || Manage Classes

Create School Management System || Student list

Login through Email - Google web App