Google Sheets is a great tool to store spreadsheets online, you can access them anytime, anywhere, and are always ready to share with others.

If you only want to share a portion of the spreadsheet, creating a new sheet and using the importRange function may have met your requirements. However, if you want to provide a more intuitive interface for the user to perform operations such as sort, search, highlight, paging … then you can write a web app using Google’s Apps Script .

This article consists of 2 parts. Part 1 we will display all the data each time we load the web app. Part 2 we will create the Search function and display only the correct data that the user requests. Note, this post is for beginners.

1. Display data table

To create tables, we can use pure JavaScript, but we use the Data Tables jQuery plug-in to make creating tables easier and has many functions available.

I looked for the jQuery plug-in table on Google and saw this plug-in appear first, so I used it. If you know which plug-in is better then let me know.

Let’s go over the results before starting to code.

Code Apps Script

As instructed by Google , we need the doGet() or doPost() that returns the HTML Service object to create an interface for the web app. We separate the CSS, JavaScript file with the HTML file according to Google’s best practices guide using the include() function.

Index.html

In the HTML file, we will create a <table> tag with id="table" to render the table after fetching data from a Google Sheets file . Please note, Data Tables jQuery is a plug-in of jQuery so do not forget to insert the CDN jQuery link into the HTML file before inserting the plug-in link.

Now that I know how to insert the JavaScript file link, the code must be run in the correct order. Back then, I was self-taught, so no one taught me about it.

JavaScript code

We use jQuery’s ready function to execute a callback function when the DOM is ready. We will use the google.script.run class to run the Apps Script function while on the client-side.

To initialize DataTable instance, we need to pass columns , data and some other options you can refer to at https://datatables.net/ .

Code server- side

On server-side ( Code.gs file) we will write getAllData() function to return data to create table.

Add loader

Each load can take a few seconds so we will add the loader to alert the user that the data is being loaded.

We will create an area with id loading to insert the loader. The CSS code is referenced from the website https://www.w3schools.com/ .

We will hide this loader once the data has loaded.

Result

So we have obtained data from Google Sheets and created a table on the web app using the Data Table jQuery plug-in.

2. Search function

Data Tables The jQuery plug-in also has a search build-in function. However, to search, you need to fetch all the data like part 1 above. In many cases, we do not want to make all the data public, we just want the user to search for their correct information. Such as:

• Look up order status by waybill code.
• Look up invoices by invoice code.
• Look up transaction history by account number (or phone number).
• Look up personnel information by employee code.

We will rewrite the web and display information only according to the data the user searches for.

Search.html

This HTML file is similar to the above, just add <input> tag for users to enter the content to look for.