How to pull data from Google Sheets to HTML table

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.

  1. Google Apps Script
  2. Bootstrap
  3. DataTables jQuery Plugin

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.

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 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 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.

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 7 and 8 to match your Google Sheet. You can identify your Google Sheets ID as explained here.

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}

//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
function getData(){
  var spreadSheetId = "1tMODRuz4T5MYVOGtdLV5j5EqX1MKoz4F_RySpr0YLdE"; //CHANGE
  var dataRange     = "Data!A2:F"; //CHANGE

  var range   = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
  var values  = range.values;

  return values;
}

//INCLUDE JAVASCRIPT AND CSS FILES
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

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.

<script>
  /*
  *THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE, 
  *AND PASS RETURNED DATA TO showData() FUNCTION
  */
  google.script.run.withSuccessHandler(showData).getData();

  //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table').DataTable({
        data: dataArray,
        //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {"title":"Rating"},
          {"title":"Reviews"},
          {"title":"Book title"},
          {"title":"Number of Pages"},
          {"title":"Type"},
          {"title":"Price"}
        ]
      });
    });
  }
</script>

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 line 12.

The data table is printed inside the table tag in the line 19.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">

    <?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE-->
  </head>

  <body>
    <div class="container">
      <br>
      <div class="row">
        <table id="data-table" class="table table-striped table-sm table-hover table-bordered">
          <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
        </table>
      </div>
    </div>  
  </body>
</html>

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” 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 “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 video below that demostrates 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.

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.

3.8 8 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

25 Comments
Inline Feedbacks
View all comments
Garth D Williams

I followed your instructions to the letter and the deployed web app shows a blank page with only the little terms of service blurb at the top. In browser code view everything seems to be there.

Naruto

Same.. I don’t know where the error is..

Admin

I checked it again, it works.

To work it using the same spreadsheet I have shared,

  1. Simply copy the Apps Script file to your Google Drive from the given link.
  2. Then deploy it as a web app (no need to change anything).

It should work.

Jordan

Solved…
Tks

JORDAN DE JESUS FELIPE

Same here.
What is wrong?

Itachi

I followed your step and successful. Thanks!
but how if I want to add another filter other than search?
for example I want to filter rating and price value range.

BÙI CÔNG HIẾU

column 1-6 done… start column 7 error. it is sad :(. admin fix now pls

Kri

How can I change the table style like remove the search part n the bottom where it says showing the results

Jul Cro

So… is there a way to display this within a webpage? Seems like if I embed the code from the index HTML it may work? Hmmm….

This worked awesome. I have used your tutorials for a lot of different things so THANK YOU!!!

Admin

You can embed this web app in Google Sites.

ANTONIO MARCOS DA SILVA SANTOS

Hello, well, first of all, I would like to thank this fantastic application, Get data from Google sheet in this spectacular HTML form.
I wonder if you could make a crud on top of that table?

pslim

Thanks for this brilliant post.
Is there any way to auto refresh this datatable?
Without refreshing the entire page.

Plugged this in with the data entry post and deployed as a web-app, can’t seem to figure out how to autorefresh the data table.

Chapin

Did You find out how to autorefresh just the table?

Gustav

Thank you for this tutorial! It works great!
There are some links embedded in some cells in my own google sheet. Is there a way to keep those links active after i have pulled them as html?

Chapin

Did you find out how to keep the links active?

DTL

Hey, it is not working for my google sheet, even i made changes pls help

raffaele

Hello,
a great job. congratulations.
If you could customize it or tell us how (select only some columns or omit some columns, choose number of entries to show, localize the language for constants, etc.) it would be great.
Anyway congratulations because it is a very useful thing. Good boy

Internet

Hi, thanks for the code. Though it works fine, there are some customization that I would like to do with this code in terms of appearance. Because I have more than 20 columns to display, the table looks not so interesting post publishing. Also, the search bar goes to the extreme right which is again not very user friendly as people will have to scroll horizontally to the extreme right to search for the search bar. Can you please guide me on how to fix this? For instance, table resize/ column resize/ font resize and so on? Especially the search bar relocation part

Internet

Would appreciate a quick help her on the search bar relocation and the column/ table resize thing.

salinda

How to add User Name and Password to this form?

Ajaysing Raghunath Patil

Not working Used all methods but didn’t get success. Kindly solve my problem. I need it too much.

Admin

Hi Ajaysing,
Please watch the following video and try.
https://youtu.be/uqmeYQ7JsrU

Farid

Thanks for the tutorial. In case I want to create html page outside the app script, would you please show me how to change the code.gs script.

Secondly, how to refer the web app link from the external html page.

Thank you..

A S

I have followed the steps as informed above but its not working.Please help

Rodrigo Ortega

If a column has hyperlinks how can we make this show them too?

shares
%d bloggers like this: