How to Get All Existing Results from a Google Sheet and Display Them in a Web App HTML Table using Google Apps Script

by

steps to retrieve all existing results from a Google Sheet and display them back into a web app using an HTML table. Google Apps Script makes it easy to connect your Google Sheets data with a custom web interface.

Step 1: Set Up Your Google Sheet

First, make sure you have a Google Sheet with some data. For this example, let’s assume you have a sheet with the following columns: Name, Email, and Score.

Step 2: Create a New Google Apps Script Project

  1. Open Google Drive and create a new Google Apps Script project.
  2. Rename your project to something meaningful, like “Sheet Data Web App”.

Step 3: Write the Script to Retrieve Data

In the script editor, we’ll write a function to get all the data from the sheet.

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

function getSheetData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // Replace 'Sheet1' with your sheet name
  var data = sheet.getDataRange().getValues();
  return data;
}

Step 4: Create the HTML File

Next, create a new HTML file in your script project. This will be the front-end of your web app.

  1. Click on the “+” icon next to Files and select HTML.
  2. Name it Index.

Inside the Index.html file, add the following code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      table {
        width: 100%;
        border-collapse: collapse;
      }
      th, td {
        border: 1px solid black;
        padding: 8px;
        text-align: left;
      }
      th {
        background-color: #f2f2f2;
      }
    </style>
  </head>
  <body>
    <h1>Sheet Data</h1>
    <table id="data-table">
      <thead>
        <tr>
          <th>Name</th>
          <th>Email</th>
          <th>Score</th>
        </tr>
      </thead>
      <tbody>
        <!-- Data will be inserted here -->
      </tbody>
    </table>

    <script>
      function populateTable(data) {
        var tableBody = document.getElementById('data-table').getElementsByTagName('tbody')[0];
        data.forEach(function(row) {
          var newRow = tableBody.insertRow();
          row.forEach(function(cell) {
            var newCell = newRow.insertCell();
            newCell.textContent = cell;
          });
        });
      }

      google.script.run.withSuccessHandler(populateTable).getSheetData();
    </script>
  </body>
</html>

Step 5: Deploy the Web App

  1. Click on the Deploy button (the rocket icon) in the top right corner of the script editor.
  2. Select New deployment.
  3. Choose Web app.
  4. Set the Execute as option to Me and Who has access to Anyone.
  5. Click Deploy.

Step 6: Test Your Web App

After deployment, you will receive a URL. Open this URL in your browser to see the data from your Google Sheet displayed in an HTML table.

Conclusion

You have now successfully created a Google Apps Script web app that retrieves data from a Google Sheet and displays it in an HTML table. This is a powerful way to combine the functionality of Google Sheets with a custom web interface. You can expand on this basic example by adding more features like data filtering, sorting, and editing.

About the Author

Laurence is an experienced developer and author specializing in Google Apps Script and educational content creation. He has written several books, including “Google Apps Script Sheets Custom Functions: Over 150 Apps Script Code Examples for Sheets” and “FrontEnd Code Playground: HTML, CSS, and JavaScript Exercises to Develop Your Skills”.