html google-sheets google-apps-script web-applications

Spreadsheet database with html

Code.gs

function getShowData() {
 const sheetId = "1pP5hvkL81mBrFsbrqfs1iiAJjuOCuTZNVe78enWB7DQ"; // Your Spreadsheet ID
    const spreadsheet = SpreadsheetApp.openById(sheetId);
    Logger.log("Opened spreadsheet with ID: " + sheetId);

    const sheet = spreadsheet.getSheetByName("showdata");
    if (!sheet) {
        Logger.log("Error: Sheet 'showdata' not found.");
        return { data: null, header: null };
    }
 
    const dataRange = sheet.getDataRange();
    const data = dataRange.getValues();
    Logger.log("Data fetched from 'showdata': " + JSON.stringify(data));

    if (data.length === 0 || data[0].length === 0) {
        Logger.log("Error: No data found in 'showdata' sheet.");
        return { data: null, header: null };
    }

    const header = data[0]; // First row as header
    const body = data.slice(1); // Remaining rows as body
    Logger.log("Header: " + JSON.stringify(header));
    Logger.log("Body: " + JSON.stringify(body));

    return { data: body, header: header };


}

here is my .html it cannot show data from spreadsheet

 <script>
        function populateTable(header, data) {
  console.log("Header:", header); // Log the header array
  console.log("Data:", data);     // Log the data array
  
  // Clear any existing table rows
  var tableBody = document.getElementById("dataTableBody");
  tableBody.innerHTML = "";

  if (!data || data.length === 0) {
    tableBody.innerHTML = "<tr><td colspan='" + header.length + "'>No data available in the table.</td></tr>";
    return;
  }

  // Create table header row
  var headerRow = document.createElement("tr");
  header.forEach(function(colName) {
    var th = document.createElement("th");
    th.textContent = colName;
    headerRow.appendChild(th);
  });
  tableBody.appendChild(headerRow);

  // Create data rows
  data.forEach(function(row) {
    var rowElement = document.createElement("tr");
    row.forEach(function(cell) {
      var td = document.createElement("td");
      td.textContent = cell;
      rowElement.appendChild(td);
    });
    tableBody.appendChild(rowElement);
  });
}

function fetchData() {
  google.script.run.withSuccessHandler(function(response) {
    if (response.error) {
      console.error("Error:", response.error);
    } else {
      console.log("Data fetched successfully:", response);
      populateTable(response.header, response.data);
    }
  }).getShowData();
}<script>

Solution

Showing data from Google spreadsheet

One of the requirements of publishing a WebApp is adding a doGet(e) function to your code. Here's a solution that would help you show the data of your Google sheets.

Modified script

function getShowData() {
 const sheetId = "#####"; // Your Spreadsheet ID
    const spreadsheet = SpreadsheetApp.openById(sheetId);
    Logger.log("Opened spreadsheet with ID: " + sheetId);

    const sheet = spreadsheet.getSheetByName("showdata");
    if (!sheet) {
        Logger.log("Error: Sheet 'showdata' not found.");
        return { data: null, header: null };
    }
 
    const dataRange = sheet.getDataRange();
    const data = dataRange.getValues();
    Logger.log("Data fetched from 'showdata': " + JSON.stringify(data));

    if (data.length === 0 || data[0].length === 0) {
        Logger.log("Error: No data found in 'showdata' sheet.");
        return { data: null, header: null };
    }

    const header = data[0]; // First row as header
    const body = data.slice(1); // Remaining rows as body
    Logger.log("Header: " + JSON.stringify(header));
    Logger.log("Body: " + JSON.stringify(body));

    return { data: body, header: header };
}
function doGet() {
  return HtmlService.createHtmlOutputFromFile('####'); //Change it with your .html file name
}

.html

<!DOCTYPE html>
<html>
  <head>
    <title>no title</title>
  </head>
  <body>
    <table border="1">
      <thead id="dataTableHeader"></thead>
      <tbody id="dataTableBody"></tbody>
    </table>

    <script>
      function populateTable(header, data) {
        console.log("Header:", header); // Log the header array
        console.log("Data:", data);     // Log the data array

        // Clear any existing table rows
        var tableBody = document.getElementById("dataTableBody");
        tableBody.innerHTML = "";

        if (!data || data.length === 0) {
          tableBody.innerHTML = "<tr><td colspan='" + header.length + "'>No data available in the table.</td></tr>";
          return;
        }

        // Create table header row
        var headerRow = document.createElement("tr");
        header.forEach(function (colName) {
          var th = document.createElement("th");
          th.textContent = colName;
          headerRow.appendChild(th);
        });
        tableBody.appendChild(headerRow);

        // Create data rows
        data.forEach(function (row) {
          var rowElement = document.createElement("tr");
          row.forEach(function (cell) {
            var td = document.createElement("td");
            td.textContent = cell;
            rowElement.appendChild(td);
          });
          tableBody.appendChild(rowElement);
        });
      }

      function fetchData() {
        google.script.run.withSuccessHandler(function (response) {
          if (response.error) {
            console.error("Error:", response.error);
          } else {
            console.log("Data fetched successfully:", response);
            populateTable(response.header, response.data);
          }
        }).getShowData();
      }
      window.onload = fetchData;
    </script>
  </body>
</html>

Deploy your WebApp

Output:

Reference:

Web Apps