In this post, I demonstrate how to use the JQuery Autocomplete Field within a Bootstrap Form on Google Web App. This helps with narrowing down a list of values for easier selection on a dropdown.
How to Video:
Video Notes:
- JQuery JS Fiddle Online Example. Click Here
- JQuery and Bootstrap Libraries
- JQuery Autocomplete Documentation
- For Further Details on Deploying a Web App Click Here
- For Other Related Posts Click Here
Code in Video:
function doGet(e) {
return HtmlService.createTemplateFromFile('AutoComplete').evaluate();
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function AddRecord(firstname, lastname, street, city, state, zip, email) {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var autoCompleteSheet = ss.getSheetByName("FORM DATA");
autoCompleteSheet.appendRow([firstname, lastname, street, city, state, zip, email, new Date()]);
}
function getStates()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var statesSheet = ss.getSheetByName("STATES");
var statesRange = statesSheet.getRange("A2:A51");
var statesValues = statesRange.getValues();
return statesValues;
}<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" ></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js" ></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />
<?!= include('AutoCompleteStyle'); ?>
<script>
$( document ).ready(function() {
getStates();
});
function AddRow()
{
var firstname = document.getElementById("firstname").value;
var lastname = document.getElementById("lastname").value;
var street = document.getElementById("street").value;
var city = document.getElementById("city").value;
var state = document.getElementById("state").value;
var zip = document.getElementById("zip").value;
var email = document.getElementById("email").value;
if(firstname != '' && lastname != '' && street != '' && city != '' && state != '' && zip != '' && email != '')
{
google.script.run.AddRecord(firstname, lastname, street, city, state, zip, email);
document.getElementById("firstname").value = '';
document.getElementById("lastname").value = '';
document.getElementById("street").value = '';
document.getElementById("city").value = '';
document.getElementById("state").value = '';
document.getElementById("zip").value = '';
document.getElementById("email").value = '';
document.getElementById("display_error").innerHTML = "Record Added";
}
else
{
document.getElementById("display_error").innerHTML = "Please Enter All Information!";
}
}
function getStates()
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
statesArray = [];
ar.forEach(function(item, index)
{
statesArray.push(item[0]);
});
$("#state").autocomplete({
source: statesArray
});
}).getStates();
}
</script>
</head>
<body>
<div style="padding: 10px;" >
<form>
<div class="form-row">
<div class="form-group col-md-3">
<label for="firstname">First Name</label>
<input type="text" id="firstname" class="form-control" />
</div>
<div class="form-group col-md-3">
<label for="lastname">Last Name</label>
<input type="text" id="lastname" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="street">Street</label>
<input type="text" id="street" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-3">
<label for="city">City</label>
<input type="text" id="city" class="form-control" />
</div>
<div class="form-group col-md-2">
<label for="state">State</label>
<input type="text" id="state" class="form-control" />
</div>
<div class="form-group col-md-1">
<label for="zip" >Zip</label>
<input type="text" id="zip" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-3">
<label for="email" >Email</label>
<input type="text" id="email" class="form-control "/>
</div>
</div>
<div class="form-group col-md-3">
<input type="button" value="Submit" class="btn btn-primary" onclick="AddRow()" />
<div id="display_error" ></div>
</div>
</form>
</div>
</body>
</html><style>
.ui-autocomplete {
position: absolute;
top: 100%;
left: 0;
z-index: 1000;
display: none;
float: left;
min-width: 160px;
padding: 5px 0;
margin: 2px 0 0;
list-style: none;
font-size: 14px;
text-align: left;
background-color: #ffffff;
border: 1px solid #cccccc;
border: 1px solid rgba(0, 0, 0, 0.15);
border-radius: 4px;
-webkit-box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
background-clip: padding-box;
}
.ui-autocomplete > li > div {
display: block;
padding: 3px 20px;
clear: both;
font-weight: normal;
line-height: 1.42857143;
color: #333333;
white-space: nowrap;
}
.ui-state-hover,
.ui-state-active,
.ui-state-focus {
text-decoration: none;
color: #262626;
background-color: #f5f5f5;
cursor: pointer;
}
.ui-helper-hidden-accessible {
border: 0;
clip: rect(0 0 0 0);
height: 1px;
margin: -1px;
overflow: hidden;
padding: 0;
position: absolute;
width: 1px;
}
</style>
Related Posts
Embed Map with Marker on Google Web App - In this post, I demonstrate how to embed a map with a marker on a Web App using locations from Google Sheets. I am using the Leaflet Javascript Library.
Create Charts in Web Apps using Google Sheets - In this post, I demonstrate how to use the Chart.js JavaScript library to create Charts on a Web App using data from Google Sheets.
How to Create Date Picker on Google Web App - In this post, I demonstrate how to create a Date Picker on HTML form in a Google Web App. This helps the user to easily select the date as well as keep it in a uniform format.
JQuery Tablesorter on Google Web App - In this post, I demonstrate how to use the JQuery Tablesorter on a Google Web App. This is a good way to sort, filter and scroll through a table data.
Create Nested View on Google Web App using Colorbox - In this post, I demonstrate how to create a nested view using Colorbox jQuery library on Google Web Application.