You can build several types of Web Apps using Google Sheets Apps Script. In previous posts, we discussed creating several types of online data entry forms using Google Apps Scripts. This post will show you how to pull data from Google Sheets to HTML table and display it in a Web app created with Google Apps Script.
With this method, you can create beautiful tables, and you can allow visitors to search and sort data in your HTML table. This way, you can show your data to others without giving access to your Google Sheet.
I will use the following technologies to import Google Sheets data to the HTML table in our web app.
All the above technologies are free to use. We use Bootstrap to style the Web App. The jQuery and DataTables plugin is used to add search, sort, and filter functions to the HTML table.
You may also check out our blog post “Display Google Sheets Data on Interactive HTML Tables (Quick & Easy Setup)” which demonstrates how to use the Tabulator library to create HTML tables from Google Sheets data.
Table of Contents
Pull data from Google Sheets to HTML table
Using this tutorial, you will create a web app, as shown in the following video. The data in the table is imported from a Google Sheet.
You can see the live web app from the following link.
Steps to create a web app to pull and show Google Sheets data
First, you need to prepare your Google Sheet. Then you need to create a new Apps Script project and use the code given below to create your web app. You can either modify the code to match your Google Sheet or use the Google Sheet given below.
If you do not like reading, watch the demo video below.
Step 01: Prepare your Google Sheet
First, create a simple table in your Google Sheet. Do not keep merged cells within your data range to be shown in the Web App. You can make a copy of the Google Sheets used in this example from the following link.
Step 02: Create a new Apps Script project / Make a copy
We use Google Apps Script to pull data from Google Sheets to an HTML table. There are two ways to use 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. So, you need to create the App Script Project separately, and you cannot open it using the “Tools” menu of the Google Sheet.
Below, I explain how to create a new Standalone Google Apps Script. Alternatively, you can copy the Google Apps Script project in this example with the complete code from the following link. Then you can skip this section and jump to Step 03.
To create a new Standalone Google Apps Script project,
- Go to Google Drive
- Click the “New” button on the top left corner and go to “More” and click “Google Apps Script“.
Then copy and paste the following code snippets.
Important !: 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 enable 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.
Code Snippets
Code.gs
This file includes the server-side functions.
To work the following code for your Google Sheet, you need to change the variables “spreadSheetId” and “dataRange” in lines 9 and 10 to match your Google Sheet. You can identify your Google Sheets ID as explained here.
In the above Code.gs file, the function doGet() tells the script how to serve the page and returns the HtmlOutput object. You must include this function in your script to create a web app with the HTML service.
The function getData()
returns the data range as an array.
The function include()
, use to include the JavaScript.html
file inside the Index.html
file. Using this function, I have included the codes in the JavaScript.html file in the Index.html file. You can learn more about this in HTML Service: Best Practices in Google Apps Script guide.
JavaScript.html
I have separated the client-side JavaScript codes into this JavaScript.html file. It is then included in the Index.html file using the include()
function in the Code.gs file.
The following JavaScript function calls the getData()
function in the Code.gs file during the page load. Then, the returned data array and passed it to the showData() function. Then it generates the data table and inserts it into the page.
Index.html
This file includes the HTML and JavaScript required to generate the HTML page. The JavaScript file mentioned above is included in the header section using include()
function as shown below in line 12.
The data table is printed inside the table tag in line 19.
Step 03: Deploy as a Web App
To create the HTML page, you need to deploy your project as a Web app.
To deploy the project as a web app,
- Click the blue color “Deploy” button
- Click “New deployment“
- In the New deployment pop-up window,
- For Description, give a meaningful name so that you can later identify this version.
- Select your email for “Execute as” and then the web app will not require users to run the code using their account data.
- Select “Anyone” for the “Who has access” section so that anyone on the internet can access this link.
- Then click “Deploy“
- Then it will require you to authorize access to your data, click “Authorize access“
- In the next pop-up dialog box, click your email to sign in.
- Next, click Advanced, and click the link with your project name at the bottom of the pop-up dialog box.
- Then click the “Allow” button.
- In the remaining pop-up dialog box, you will see a link to your Web app.
- Click the link, and it will open the Web app in a new window. You can share this link with others.
Watch the video below that demonstrates the above steps.
DataTable functionalities
In this example, we used the JQuery DataTable plugin to add search, sort, pagination functions to the HTML table.
- Search data – You can use the search box to search data in any column of the data table.
- Sort Data – Click the table headers to sort data in ascending or descending order.
- Pagination – By default, this table shows 10 entries per page. Click the page numbers below the table to view the other entries.
- Change number of entries per page – Change the number of entries per page from the “Show entries” dropdown.
Display URLs as hyperlinks
If your Google Sheet has a column with URLs, the above code shows them as simple text values. In order to show the URLs as hyperlinks, you can modify the JavaScript.html file as below.
Let’s say you are adding a new column with URLs to the end of the data table (column G of the Google Sheet).
Add the following code next to line 20 of the JavaScript.html file.
(Do not forget to update the dataRange in the Code.gs file according to new changes.)
{"title":"URL",
"render": function(data, type, row, meta){
if(type === 'display'){
data = '<a href="' + data + '">' + data + '</a>';
}
return data;
}
}
Reference: https://stackoverflow.com/a/30489991/2391195
If you want to show a custom text instead of the URL you can use the following code.
{"title":"URL",
"render": function(data, type, row, meta){
if(type === 'display'){
data = '<a href="' + data + '">' + 'Your custom text' + '</a>';
}
return data;
}
}
You can see a working solution from the following links.
Did you know that it’s possible to showcase your Google Sheets data on HTML charts? Absolutely! By utilizing Google Apps Script and the Google Charts JavaScript library, you can effortlessly create visually appealing and interactive dashboards for your Google Sheets data. Read the following blog post to read more.
Wrapping Up
In this example, I showed you how to pull data from Google Sheets to an HTML table. I created the HTML table in a Web app created with Google Apps Script. Google Apps Script is completely free, and you can build many types of Web apps. I used the Bootstrap CSS library to style the HTML table. And the JQuery DataTable plugin was used to add more functionalities such as search, sort, and pagination to the HTML table.
You can share this web app with others using its URL. The visitors can get updates to the Google Sheet by refreshing the web app. Using this method, you can easily share your Google Sheets data with others without giving access to the entire spreadsheet. You can also Embed Google Apps Script Web Apps in Websites.
This is great!!! Question though, how can I lock the header row and first columns??
This is exactly what I am looking for!
Only issue is that I have a column with a url link but I don’t want to show the url.
I want it to show “Link Here”
If I set that up in Google sheets, the link doesn’t work on the site.
Any help? Thanks.
Modify the code as explained here.
https://www.bpwebs.com/pull-data-from-google-sheets-to-html-table/#display-urls-as-hyperlinks
That is what I did. If I leave the link as it is, it works. But I want it to show “Link Here” instead. If I do that, it does not work.
Any update to this? This is still not working for me. Thanks.
Hi Andy,
You can replace the variable “data” with your custom text. Please check the above-mentioned section in the post, I have updated it.
THANK YOU!!!! This is much better and finally working for me! Let me ask one more thing. Updating variable “data” to my “Link Here” changes all of the link names in that column to “Link Here” is there any way to pull the text from that (or an adjacent) cell to show that info? For example all my links go to folders and I want the folder name as the hyperlink.
Thanks so much again for helping!
Great tutorial! thanks a lot!!!
I did everything step-by-step and I received this error after I try to access the deployed URL.
Sorry, unable to open the file at this time.
Please check the address and try again.
Pls, check the URL using an Incognito window also.
Thanks for this nice app
how to make this script work via android mobile phones as I experience zooming issue when I open it
Hello,
I need your help :
Erreur
ReferenceError: Sheets is not defined
getData @ funcs.gs:32
You have to enable Google Sheets API Service for your project.
To enable 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.
it works well
thank you verry much
Could you help me, to know how to add a checkbox to the rows to be able to send the selected rows by mail?
Thanks for this great solution.
It works for me = but only if I either use the sample data sheet or paste my own data into the sample ( or a copy of the sample). However, if I link to my own source data sheet, the script runs OK but there is no data displayed. I have checked an rechecked the sheet ID. I notice that, in the sample data sheet, only the first 6 columns are visible – perhaps this is the issue?
right.
I have the same type of problem.
the code run well but didn’t display a table
NOW it worked for me
I were missed these steps
To enable 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.
But Still, I have a Question
How to manage the width of the table?
how to make more decorative and attractive ?
Don’t Forget to change the code in the row under sheetid. The row that shows “data!A2…” You need to change “data” to the name of your table.
Hi all,
I have the same problem as Sean: the AppScript works well if linked to the sample google sheet file, but not if I link it to my own (comes up blank).
Thank you so much for the tutorial.
It’s the best one of all the the net.
I would like to ask you if there is a way to pull data from a google sheet to create an html table like this one in the below:
https://datatables.net/release-datatables/examples/api/row_details.html
The table in the link has the green (+) symbol in the left colum that hiding extra informations.
I have no idea about coding and html/css/java etc, but with your tutorial I managed to do a webapp.
Once again, thank you for your great effort.
Kind regards,
Viktor
My display URLs as hyperlinks script doesn’t work.. it doesn’t show additional column or url
Omg it works
It seems like my comments aren’t going through… but I have followed these instructions to the letter. Each of my cells in the referenced Google Sheet contains a formula or text. Every time I deploy this script I get two errors:
DataTables warning: table id=data-table – Requested unknown parameter ‘3’ for row 13, column 3. For more information about this error, please see http://datatables.net/tn/4
DataTables warning: table id=data-table – Requested unknown parameter ‘2’ for row 14, column 2. For more information about this error, please see http://datatables.net/tn/4
Don’t know how to fix. Please help!
Wow, this prepares me too much, I enjoyed it a lot! Thank you, just seeing the entire table created gave me an emotion
Good Morning, Thank you very very much for sharing this lines of code. I am just starting with this. Big hug from Buenos Aires, Argentina. Atilio.
google sheet data have date format is nothing to show, any idea to fix ??
Hi,
it’s possibile to update data from the html to Google Sheets?
Thanks
How would you implement the function updateData for the HTML table from Google Sheets? The only changes that show on the website are when edits are made to the sheet. My formulas (for example: a countdown duration) do not update on the HTML table.
Thank you very much,
Please let me know about the limitation (Number of Rows & Column) of data that display in HTML.
Please guide for data range in Code.gs file for all the rows of data with specific column.
Regards,
S.Barik
Thank you for this tutorial! It works great!
I had a problem for data displayed in HTML Table
was not line-spaced like the google sheet.
Example:
Sheet Cell:
“row1
row2″
→HTML Table:”row1 row2”
Please help.
I had the problem that the data displayed on the HTML table was not line-spaced like the google sheet.
Example:
+Google sheet cell:
“row1
row2
row3”
→dislpayed in HTML Table: “row1 row2 row3”
Please help.