Creating a Paginated Web App with Google Sheets and Google Apps Script

by

Ever needed to display data from Google Sheets on a webpage in a manageable and organized way? Whether for a dashboard, a report, or any interactive project, paginating data on a web app can vastly improve readability and user experience. In this post, we’ll walk you through building a simple web app that fetches data from Google Sheets and displays it in a paginated table format.

Step 1: Prepare Your Google Sheet

Let’s start with the data. For our example, we’re using a Google Sheet that contains basic information as shown below:

NameAgeEmail
Alice28alice@example.com
Bob25bob@example.com
Carol32carol@example.com
David24david@example.com
Eve29eve@example.com

You can adjust the columns and data as needed for your project.

Step 2: Write the Google Apps Script

Google Apps Script will act as the server-side code, fetching and serving data to our web app. Here’s how to set it up:

  1. Open your Google Sheet.
  2. Click on Extensions > Apps Script.
  3. Delete any code in the script editor and replace it with the following:
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index');
}

function getSheetData(page, limit) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const data = sheet.getDataRange().getValues();
  const headers = data.shift(); // Remove the header row

  const startIndex = (page - 1) * limit;
  const endIndex = startIndex + limit;

  const paginatedItems = data.slice(startIndex, endIndex);
  return {
    headers: headers,
    rows: paginatedItems,
    total: data.length
  };
}

This script provides a function to fetch data in pages, ensuring that the web app can request specific chunks of data at a time.

Step 3: Create the Web App Interface

Now let’s create the client-side HTML and JavaScript:

  1. In the Apps Script editor, click on File > New > HTML file.
  2. Name the file Index.
  3. Enter the following HTML and JavaScript code:
<!DOCTYPE html>
<html>
<head>
  <title>Paginated Table</title>
  <style>
    table, th, td {
      border: 1px solid black;
      border-collapse: collapse;
    }
    th, td {
      padding: 10px;
    }
  </style>
</head>
<body>
  <h1>Data from Google Sheet</h1>
  <table id="dataTable">
    <thead></thead>
    <tbody></tbody>
  </table>
  <button onclick="previousPage()">Previous</button>
  <button onclick="nextPage()">Next</button>
  <script>
    let currentPage = 1;
    const limit = 2;

    function fetchData(page) {
      google.script.run.withSuccessHandler(renderTable).getSheetData(page, limit);
    }

    function renderTable(data) {
      const tableHead = document.getElementById('dataTable').tHead;
      const tableBody = document.getElementById('dataTable').tBodies[0];

      tableHead.innerHTML = '';
      tableBody.innerHTML = '';

      let headerRow = '<tr>';
      data.headers.forEach(function(header) {
        headerRow += '<th>' + header + '</th>';
      });
      headerRow += '</tr>';
      tableHead.innerHTML = headerRow;

      data.rows.forEach(function(row) {
        let rowHtml = '<tr>';
        row.forEach(function(cell) {
          rowHtml += '<td>' + cell + '</td>';
        });
        rowHtml += '</tr>';
        tableBody.innerHTML += rowHtml;
      });
    }

    function nextPage() {
      currentPage++;
      fetchData(currentPage);
    }

    function previousPage() {
      if (currentPage > 1) {
        currentPage--;
        fetchData(currentPage);
      }
    }

    // Initial fetch
    fetchData(currentPage);
  </script>
</body>
</html>

Step 4: Deploy Your Web App

Finally, it’s time to deploy your web app:

  1. In the Apps Script editor, click on Deploy > New deployment.
  2. Click Select type and choose Web app.
  3. Fill out the details, set who has access, and then deploy.

Once deployed, you’ll get a URL to access your web app. You can now see your Google Sheets data displayed in a neatly organized paginated table.

This simple web app example can be the foundation for more sophisticated applications, allowing you to harness the power of Google Apps Script and Google Sheets efficiently.