In previous tutorials, we talked about building web apps with Google Apps Script to interact with Google Sheets. When you build those types of web apps, you may need to implement a search field to get data from Google Sheets back.
This tutorial will show you how you can create a web form to search and get data from Google Sheets. You can share this web form with others and let them search for data in your Google Sheet.
This is also useful when you want to share a part of your Google Sheets without giving access to your entire Spreadsheet. In such cases, you can create user-friendly web pages to share your Google Sheets data using this method.
There are two methods that you can follow to work with Google Apps Script. The first is Container-bound Scripts, and the second is Standalone Scripts. Here I am using the second, so it does not bound the script to the Google Sheet.
Live demo
You can access the live web form and the Google Sheet used in this tutorial from the following links.
How to create the above web form to get data from Google Sheets
Step 01 – Prepare your Google Sheet
For this tutorial, you can either use your own data sheet (A Google Sheet) or you can make a copy of the Google Sheet used in this tutorial from the following link. You can also use the Spreadsheet ID of this Google Sheet, even without copying it to your Google Drive.
Step 02 – Make a copy of the Google Apps Script file
You can make a copy of the Google Apps Script file to your drive from the following link.
The above Google Apps Script file contains the following two files. Here I am not going to explain the code line by line. Instead, I will briefly explain the purpose of each file and function below.
1. Index.html file
This file includes the HTML code for the search form and required JavaScript files and functions. Here I am using Bootstrap for styling the form and table. You can see included Bootstrap related files in, line 5,6, and 7.
The search form is from lines 89 to 97. Once you hit the search button, it will execute the handleFormSubmit JavaScript function (in, line 24). This function gets the relevant search results from the server (explain below) and passes them to the createTable function (line 30).
In the createTable function, lines from 35 to 59 are the column headers of the result table. You should write this in the same order as in your Google Sheet. The dataArray variable contains the search results received from handleFormSubmit function. The createTable function creates the table from the values in the dataArray variable and print the results inside <div id="search-results"> in line 106.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
<!--##JAVASCRIPT FUNCTIONS ---------------------------------------------------- -->
<script>
//PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener("load", preventFormSubmit, true);
//HANDLE FORM SUBMISSION
function handleFormSubmit(formObject) {
google.script.run.withSuccessHandler(createTable).processForm(formObject);
document.getElementById("search-form").reset();
}
//CREATE THE DATA TABLE
function createTable(dataArray) {
if(dataArray && dataArray !== undefined && dataArray.length != 0){
var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
"<thead style='white-space: nowrap'>"+
"<tr>"+ //Change table headings to match witht he Google Sheet
"<th scope='col'>ORDERNUMBER</th>"+
"<th scope='col'>QUANTITYORDERED</th>"+
"<th scope='col'>PRICEEACH</th>"+
"<th scope='col'>ORDERLINENUMBER</th>"+
"<th scope='col'>SALES</th>"+
"<th scope='col'>ORDERDATE</th>"+
"<th scope='col'>STATUS</th>"+
"<th scope='col'>QTR_ID</th>"+
"<th scope='col'>MONTH_ID</th>"+
"<th scope='col'>YEAR_ID</th>"+
"<th scope='col'>PRODUCTLINE</th>"+
"<th scope='col'>MSRP</th>"+
"<th scope='col'>PRODUCTCODE</th>"+
"<th scope='col'>CUSTOMERNAME</th>"+
"<th scope='col'>PHONE</th>"+
"<th scope='col'>ADDRESSLINE1</th>"+
"<th scope='col'>ADDRESSLINE2</th>"+
"<th scope='col'>CITY</th>"+
"<th scope='col'>STATE</th>"+
"<th scope='col'>POSTALCODE</th>"+
"<th scope='col'>COUNTRY</th>"+
"<th scope='col'>TERRITORY</th>"+
"<th scope='col'>CONTACTLASTNAME</th>"+
"<th scope='col'>CONTACTFIRSTNAME</th>"+
"<th scope='col'>DEALSIZE</th>"+
"</tr>"+
"</thead>";
for(var i=0; i<dataArray.length; i++) {
result += "<tr>";
for(var j=0; j<dataArray[i].length; j++){
result += "<td>"+dataArray[i][j]+"</td>";
}
result += "</tr>";
}
result += "</table>";
var div = document.getElementById('search-results');
div.innerHTML = result;
}else{
var div = document.getElementById('search-results');
//div.empty()
div.innerHTML = "Data not found!";
}
}
</script>
<!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->
</head>
<body>
<div class="container">
<br>
<div class="row">
<div class="col">
<!-- ## SEARCH FORM ------------------------------------------------ -->
<form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
<div class="form-group mb-2">
<label for="searchtext">Search Text</label>
</div>
<div class="form-group mx-sm-3 mb-2">
<input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Search Text">
</div>
<button type="submit" class="btn btn-primary mb-2">Search</button>
</form>
<!-- ## SEARCH FORM ~ END ------------------------------------------- -->
</div>
</div>
<div class="row">
<div class="col">
<!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
<div id="search-results" class="table-responsive">
<!-- The Data Table is inserted here by JavaScript -->
</div>
<!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
</div>
</div>
</div>
</body>
</html>
1. Code.gs file
This file contains the server-side script. The above mentioned function handleFormSubmit in the Index.html file passes the formObject to the processForm function in this Code.gs file.
If the formObject contain searchtext, it passes to the search function.
In the search function, replace the spreadhseetId and the dataRange as per your Google Sheet and data range.
The search function look for matching contents in the Google Sheet and returns an array of rows back to the processForm function. It returns the results back to the handleFormSubmit function in the Index.html file.
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
/* PROCESS FORM */
function processForm(formObject){
var result = "";
if(formObject.searchtext){//Execute if form passes search text
result = search(formObject.searchtext);
}
return result;
}
//SEARCH FOR MATCHED CONTENTS
function search(searchtext){
var spreadsheetId = '142FbylWc7109R0RsXQ42NuN_TFs-D3RTtIYJOSBR1QM'; //** CHANGE !!!
var dataRage = 'Data!A2:Y'; //** CHANGE !!!
var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
var ar = [];
data.forEach(function(f) {
if (~f.indexOf(searchtext)) {
ar.push(f);
}
});
return ar;
}
Step 03 – Publish as a web app
Once you have done all the coding, you can get a URL to this web app by deploying it as a web app. To do that,
- Go to Google Apps Script file
- Then go to “Publish” and select “Deploy as web app…“
- For “Execute the app as“; field select your email.
- Under “Who has access to the app“: option select “Anyone“
- Click “Update“
- Copy the URL in the next window and paste in the browser to access the form.
Wrapping Up
With Google Apps Script, you can build various types of web apps for free. When building those web apps you may need to build a search option to get data from Google Sheets to the web app.
This tutorial explained to you how to create a basic search form in your web app to retrieve data from Google Sheets.
Can you make this topic in video
And explain each step one by one
It is important for me …
Hi
I appreciate your great job on this topic.
I face some trouble in coding
I try to make same coding and I changed what you mention Like spreadsheetId and dataRage in code file and //Change table headings to match with the Google Sheet in HTML file
when I published I get this massage
(( ReferenceError: data is not defined (line 8, file “Code”) ))
https://script.google.com/macros/s/AKfycbxRs4YNT0kobeJFBkLAk1PrtfphIJFt2_37nnWSnShZZDKPB_w/exec
how can I solve this problem?
thank you again
Hi, Mohammad
It’s difficult to answer without looking at the code.
For the time being, check whether you are using an undefined variable called “data” in line 8 of your Code.gs file.
We hope to make video and demonstrate line by line for this subject because I confuse I have some struble in matching with new sheet .
I appreciate your effort ..
Hi Good Day,
First and foremost. Thank you for this!
Excellent script.
Can we tweak it, so that it will be able to search for everything?
i.e Source in google sheet is “Alexis Sanchez”; currently, im only able to search if i type it in as “Alexis Sanchez” in the search button.
Can it be tweaked with wildcards so that if i just type in “Alexis”, everything named Alexis should appear?
//SEARCH FOR MATCHED CONTENTS
function search(searchtext){
var spreadsheetId = ‘SheetName’; //** CHANGE !!!
var dataRage = ‘Data!A2:R’; //** CHANGE !!!
var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
var ar = [];
data.forEach(function(f) {
if (~f.indexOf(searchtext)) {
ar.push(f);
}
});
return ar;
}
Hi Mario,
Try replacing,
data.forEach(function(f) { if (~f.indexOf(searchtext)) { ar.push(f); } });With
for(var i=0;i<data.length;i++){ for(var j=0;j<data[i].length;j++){ if(data[i][j].search(searchtext)!=-1){ ar.push(data[i]) } } }Dear Admin,
Thank you so much for this! it worked like a charm.
You made my day!
Dear Admin,
Sorry to be a bother again. Im trying to make it not case sensitive now.
I tried the following, but to no avail.
if(data[i][j].toLowerCase().search(searchtext)!=-1){
Thank you so much again.
Hi,
Why don’t you convert both strings to the lower case like below.
for(var i=0;i<data.length;i++){ for(var j=0;j<data[i].length;j++){ if(data[i][j].toLowerCase().search(searchtext.toLowerCase())!=-1){ ar.push(data[i]) } } }Dear Admin,
Thank you so much again!. Fantastic!. Im in your debt.
We have this ERROR : TypeError: Cannot read property ‘searchtext’ of undefined (line 9, file “Code”) — Just copy your code any paste, i can not find the reason. please help
Can you make a copy of the Apps Script file from the following link and start over,
https://script.google.com/d/14PFA3tLhkZTSp5TRAFZfPdc4EAH-heYLICQ13xau7MbK3SejKSnwqGKi/edit?usp=sharing
Thank you admin for this post. I find it very useful. I am having problem in putting a dropdown list in the search box. A dropdown list that draws options from the spreadsheet. This functionality would make the form more efficient. I hope to get some guidance on this. Thank you in advance.
KS Seah, this youtube video https://www.youtube.com/watch?v=pmQdrAIdfGM may provide you some guidance.
Thanks Hassan Mubin. However I am still unable to get it aftter several attempts. Any other help would be greatly appreciated. Thank you in advance.
How can we make a web app that can clock in and out many times in web app?
Good day! I tried to integrate your Search Code in the codes you’ve shared for CRUD operation. However, I got no display. I hope you can help me on this. Thanks a lot.
Here’s the link to the code:
https://script.google.com/d/1WmHzODO5Ed9zYX5V1hUN81qyAMOSVT2kaSxjiumIrTI_-Th8sxjeRG8-/edit?usp=sharing
Hello Connie,
You have included a number of unnecessary code snippets in your project.
Instead of modifying your code, I updated the code shared in the post for CRUD operation. You can copy/see them from the following links.
Thank you very much. This is very useful to my work.. Thanks a lot..
In your sample Google Sheet. How to prevent Columns A-C from editing? Only columns D-G shall be updated. Data from Columns A to C were duplicated using importrange from other Google Sheet file, so only cols D to G shall be updated. When I try to edit a certain record, all records were gone, where data from A to C were importrange from other files.
Hi! thanks for posting this, im trying to deploy but i have this ERROR : TypeError: Cannot read property ‘searchtext’ of undefined (line 9, file “Code”) — Just copy your code any paste, i can not find the reason. i tried to copy again from here https://script.google.com/d/14PFA3tLhkZTSp5TRAFZfPdc4EAbuH-heYLICQ13xau7MbK3SejKSnwqGKi/edit?usp=sharing as you explained before, but i cant solve this, can you help me?
Thank you for sharing such useful code. It will help with my project a lot. However, I encounter a problem. When I click ‘search’ the page is not showing. Just blank. As if part of the code is not executing well. What do I need to look out for?