In previous tutorials, we talked about building web apps to interact with Google Sheets using Google Apps Script. 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.
Table of Contents
Live demo
You can access the live web form and the Google Sheet used in this tutorial from the following links.
Go to Google sheet
(Data Source)
How to create this 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
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 the Bootstrap related files in, lines 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.
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.
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 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.
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.
Hi,very nice post. I have tried it and its working perfectly fine. Even though i have modified it and fetched only 1 column which is url to google drive but could not make the URL clickable (hyperlink). Please help me suggest how to make the result clickable?
Hi.
I did everything like tutorials but ir didn´t work.
When i tried to search it always give “Data not found”
Regarding google sheets API we just have to activate it?
Hi,
If you are creating the project with a 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,
If you have correctly added the sheets API, you can see it just below the service tab.
You can also start working with the Google Apps Script file link given in the post. Make a copy of that file to your Drive and deploy it as a web app without changing anything. You should see it is working as explained.
Hi.
Thanks for your answer to my question.
I create a new project and activated sheets API.
My questions is, how this configuration connects with the spreedsheet (project created?
Sorry if my question is stupid but i am new on this.
Thank you.
Thanks for your tutorial.
I have enable google sheets API.
but, still didn’t work.
no table shown. just “Data not Found”.
please help me.
is it possible to adjust the search so that it is not case sensitive nor require the entire contents of the cell. Basically do a Like search
Did you get an answer to this?
Hi, I must commend you for your wonderful articles, you’re doing great.
Please just one more thing I would want to do to the search result, I would want to have a dropdown option on the last cell and a submit button to the which when a search is made, the result comes out, then you scroll to the last cell in the search result and select an option from the list of dropdowns, then click the submit button, which would now save the selected option to the last cell on the sheet. and display Updated, telling me that my option have been updated.
Thank you
I was using it before without any problems, now when I select input it gets too close to the right corner of the page, what could be the problem? this problem only happens in android chrome browser
Hi,
I have create a web app to extract data from google spreadsheet and added a few checkboxes to the table array. But how do i retrieve these checkboxes values and update them back into the google spreadsheet according to their assigned id?
Eg. below of HTML Table created using for loop method similar to your video example but i have added checkboxes to each row. Only the ID & name is created in the Google Sheet.
Sorry I’m just a beginner. Thank you
Id Name Q1 Q2 Q3
1 John Chkbox1 Chkbox2 Chkbox3
2 Pete Chkbox1 Chkbox2 Chkbox3
2 Tate Chkbox1 Chkbox2 Chkbox3
Hello,
Let it search only in column A:A
I m try to get data on web page but showing sheet not defined in 19 lines
I didn’t get it to work – the search bar and button is displayed but not data is found when I tried to search for something.
is that possible to edit
This is amazing!! I have it all working, BUT I also need to know how to allow the search to only search info from 1 column. Info is personal and sensitive and others could potentially access by searching for generic info from another column (I’ve tested and they are able to bring up other people by doing this). I need it to only search column A which would have a person’s specific identifier number.
Hi, please how did you get to work, because mine didnt
Hi, I want to implement this with my sample data, It all works, Thank you! but I have a column where all the cells have links, and the web app shows the text, but how can I make it show the links too?
Hi, I want to implement this with my sample data, It all works, Thank you! but I have a column where all the cells have links, and the web app shows the text, but how can I make it show the links too?
Hi This worked great for me, is it possible to search multiple “worksheets”. This would help me alot!
Hi admin , l was searching this for years and I got this. You made my day happy but I would like to say that it is only 99 % solutions Because I want that people will search only their id which is in coloum A, and get their data, but when people searches something common in the search box then all common data of other user are displayed. I want that you please help me to make a search box that will search in coloum A and give data of the
Respective row. So please help me. I will be grateful. Thank you
How to make searchtext to search in coloum A only. Thank you, please help me
Is it possible to put the results in a htmltemplate rather than a table ?
Search will only look for numeric values ??
why id doesn´t get the informatión of columns with calcs?
how can I search only in column A to match searchtext and if matches get data
Hi, I would also like to know how to do this as well. Only search one column to pull data.