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.
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.
Important ! : If you are creating the project with new Google Apps Script (instead of making a copy of the Apps Script project given above), you need to enable Google Sheets API Service for your project.
To add Google Sheets Service,
- Click the + icon in the Services tab,
- Select Google Sheets API from the list in the Add a service popup box.
- Then, click Add.
1. Index.html file
The search form is from lines 89 to 97. Once you hit the search button, it will execute the
createTable function (line 30).
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.
2. 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.
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.
search function looks 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.
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.
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.