HTML table not populating

Question

I have created a web app using Google script. it currently works as I want with ne exception. On the web app I am trying to create a table that populates with the data from the google sheet and edit/update row if and when needed. When I run console loggers it shows me Data received: null. I have tried and tried for the past three or four days to figure out what i am not doing and cannot seem to get it to populate.

Below is my index.html.

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');
}
function getRecords() {
  try {
    var sheet = SpreadsheetApp.openById('16b_cRhhtLSqZqGqZ3bwigHleOLNYQGhGB0FGkc8j7nk').getSheetByName('Data'); // Change 'Jobs' to your actual sheet name

    if (!sheet) {
      Logger.log('Sheet not found');
      return [];
    }

    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();

    // Log the entire values array
    Logger.log('Values:', values);

    // Check if there are headers in the first row
    if (values.length < 1) {
      Logger.log('No headers found.');
      return [];
    }

    // Extract headers from the first row
    var headers = values[0];
    
    // Log the headers array
    Logger.log('Headers:', headers);

    // Convert 2D array to a 1D array of objects (assuming the first row contains headers)
    var records = [];
    for (var i = 1; i < values.length; i++) {
      var record = {};
      for (var j = 0; j < headers.length; j++) {
        // Check if the value is present before assigning to the record
        record[headers[j]] = values[i][j] !== undefined ? values[i][j] : null;
      }
      records.push(record);
    }

    if (records.length > 0) {
      Logger.log('Retrieved records:', records);
    } else {
      Logger.log('No records found.');
    }
    return records;
  } catch (error) {
    Logger.log('Error in getRecords:', error.message); // Log the error message
    return [];
  }
}




function addRecord(data) {
  var sheet = SpreadsheetApp.openById('16b_cRhhtLSqZqGqZ3bwigHleOLNYQGhGB0FGkc8j7nk').getSheetByName('Data');
  sheet.appendRow(data);
}

function updateRecord(row, data) {
  var sheet = SpreadsheetApp.openById('16b_cRhhtLSqZqGqZ3bwigHleOLNYQGhGB0FGkc8j7nk').getSheetByName('Data');
  var rowIndex = row + 2; // Adjusted row index for header
  sheet.getRange(rowIndex, 1, 1, 13).setValues([data]);
}

function deleteRecord(row) {
  var sheet = SpreadsheetApp.openById('16b_cRhhtLSqZqGqZ3bwigHleOLNYQGhGB0FGkc8j7nk').getSheetByName('Data');
  sheet.deleteRow(row + 2); // Adjusted row index for header
}

<!DOCTYPE html>
<html>
<head>
  <title>Job Management System</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
</head>
<body>

<h1>Job Management System</h1>

<!-- Form to Add or Edit Record -->
<h2>Add/Edit Record</h2>
<form id="jobForm">
  <label for="jobNumber">Job Number:</label>
  <input type="text" id="jobNumber" name="jobNumber"><br><br>

  <label for="streetAddress">Street Address:</label>
  <input type="text" id="streetAddress" name="streetAddress">
  <label for="city">City:</label>
  <input type="text" id="city" name="city"><br><br>

  <label for="statusDescription">Status Description:</label>
  <input type="text" id="statusDescription" name="statusDescription">
  
  <label for="dateReceived">Date Received:</label>
  <input type="date" id="dateReceived" name="dateReceived">
  
  <label for="dueDate">Due Date:</label>
  <input type="date" id="dueDate" name="dueDate"><br><br>

  <label for="fieldSupervisor">Field Supervisor:</label>
  <input type="text" id="fieldSupervisor" name="fieldSupervisor">
  
  <label for="customerSupervisor">Customer Supervisor:</label>
  <input type="text" id="customerSupervisor" name="customerSupervisor"><br><br>

  <label for="priority">Priority:</label>
  <input type="text" id="priority" name="priority">
  
  <label for="jobClass">Job Class:</label>
  <input type="text" id="jobClass" name="jobClass"><br><br>

  <label for="contractor">Contractor:</label>
  <input type="text" id="contractor" name="contractor">
  
  <label for="dateCompleted">Date Completed:</label>
  <input type="date" id="dateCompleted" name="dateCompleted"><br><br>

  <label for="notes">Notes:</label>
  <textarea id="notes" name="notes"></textarea><br><br>

  <button type="button" onclick="addOrUpdateRecord()">Add/Update Record</button>
</form>


<!-- Display Records Table -->
<h2>Job Records</h2>
<table border="1" id="jobTable">
  <thead>
    <tr>
      <th>Job Number</th>
      <th>Street Address</th>
      <th>City</th>
      <th>Status Description</th>
      <th>Date Received</th>
      <th>Due Date</th>
      <th>Field Supervisor</th>
      <th>Customer Supervisor</th>
      <th>Priority</th>
      <th>Job Class</th>
      <th>Contractor</th>
      <th>Date Completed</th>
      <th>Notes</th>
      <th>Action</th>
    </tr>
  </thead>
  <tbody id="jobTableBody">
    <!-- Records will be populated here -->
  </tbody>
</table>

<!-- Script Section -->
<script>
  // Function to fetch and display data on window load
  window.onload = function () {
    google.script.run.withSuccessHandler(populateTable).getRecords();
    console.log("Window loaded");
  }

  // Function to add or update a record
  function addOrUpdateRecord() {
    console.log("Add or Update Record function called");

    // Retrieve values from form fields
    var jobNumber = document.getElementById('jobNumber').value;
    var streetAddress = document.getElementById('streetAddress').value;
    var city = document.getElementById('city').value;
    var statusDescription = document.getElementById('statusDescription').value;
    var dateReceived = document.getElementById('dateReceived').value;
    var dueDate = document.getElementById('dueDate').value;
    var fieldSupervisor = document.getElementById('fieldSupervisor').value;
    var customerSupervisor = document.getElementById('customerSupervisor').value;
    var priority = document.getElementById('priority').value;
    var jobClass = document.getElementById('jobClass').value;
    var contractor = document.getElementById('contractor').value;
    var dateCompleted = document.getElementById('dateCompleted').value;
    var notes = document.getElementById('notes').value;

    // Create an array with form field values
    var data = [jobNumber,streetAddress,city,statusDescription,dateReceived,dueDate,fieldSupervisor,customerSupervisor,priority,jobClass,contractor,dateCompleted,notes];

    // Call the server-side function to add or update the record
    google.script.run.withSuccessHandler(function () {
      document.getElementById('jobForm').reset();
      fetchAndDisplayData();
    }).addRecord(data);
  }

  // Function to edit a record
  function editRecord(row, record) {
    var selectedRecord = JSON.parse(record);
    console.log("Edit button clicked. Row:", row, "Record:", selectedRecord);

    // Populate form fields with selected record data
    document.getElementById('jobNumber').value = selectedRecord[0];
    document.getElementById('streetAddress').value = selectedRecord[1];
    document.getElementById('city').value = selectedRecord[2];
    document.getElementById('statusDescription').value = selectedRecord[3];
    document.getElementById('dateReceived').value = selectedRecord[4];
    document.getElementById('dueDate').value = selectedRecord[5];
    document.getElementById('fieldSupervisor').value = selectedRecord[6];
    document.getElementById('customerSupervisor').value = selectedRecord[7];
    document.getElementById('priority').value = selectedRecord[8];
    document.getElementById('jobClass').value = selectedRecord[9];
    document.getElementById('contractor').value = selectedRecord[10];
    document.getElementById('dateCompleted').value = selectedRecord[11];
    document.getElementById('notes').value = selectedRecord[12];
  }

  // Function to delete a record
  function deleteRecord(row) {
    google.script.run.withSuccessHandler(fetchAndDisplayData).deleteRecord(row);
  }

  // Function to fetch and display data
  function fetchAndDisplayData() {
    google.script.run.withSuccessHandler(populateTable).getRecords();
  }

 // Function to populate the table with records
function populateTable(records) {
  console.log("Data received:", records);  // Log the received data
  var tableBody = document.getElementById('jobTableBody');
  tableBody.innerHTML = '';

  if (records && records.length > 0) {
    for (var i = 0; i < records.length; i++) {
      var row = records[i];
      var newRow = tableBody.insertRow(-1);
      for (var j = 0; j < row.length; j++) {
        var cell = row[j];
        var newCell = newRow.insertCell(-1);
        newCell.textContent = cell;
      }
      var actionsCell = newRow.insertCell(-1);
      actionsCell.innerHTML = '<button onclick="editRecord(' + i + ', \'' + JSON.stringify(row).replace(/'/g, "\\'") + '\')">Edit</button> <button onclick="deleteRecord(' + i + ')">Delete</button>';
    }
  } else {
    console.log("No records found.");  // Log when no records are found
    var noRecordsRow = tableBody.insertRow(-1);
    var noRecordsCell = noRecordsRow.insertCell(-1);
    noRecordsCell.colSpan = 15;  // Set the colspan to the number of columns in your table
    noRecordsCell.textContent = "No records found.";
  }
}

</script>


</body>
</html>

HTML not populating is not a question. We are not interested in debugging all of your code. That is your job. If you have specific question please feel free to return with them. — Cooper
– Cooper, Commented Jan 16, 2024 at 20:18
I have spent 4 days debugging this and cannot seem to get it to work. I'm still a lil green on HTML Tables. when I run console logs i get the following below. My sheet has over 80 lines of data. so why would it come back no record found. ? Expression not available Expression not available userCodeAppPanel:5 Window loaded 2473715070-warden_bin_i18n_warden.js:147 Net state changed from IDLE to BUSY 2473715070-warden_bin_i18n_warden.js:147 Net state changed from BUSY to IDLE userCodeAppPanel:70 Data received: null userCodeAppPanel:87 — Steven Tkachuk
– Steven Tkachuk, Commented Jan 16, 2024 at 20:59
Just a guess, but if any of the values from your spreadsheet are Date objects, you can not transmit Date objects to your html form. You need to convert to string or number. See this article. — TheWizEd
– TheWizEd, Commented Jan 16, 2024 at 21:47
I modified the code using my data and now you can get data into your table. The populateTable() function was expecting the records to be arrays of data and not objects of data or atleast not objects whose keys are not indices. There's probably still more work to do to get it running perfectly. I'd say if you worked on that four days you should probably consider some additional training in Chrome Developer Tools and or Javascript — Cooper
– Cooper, Commented Jan 16, 2024 at 22:04

Cooper · Accepted Answer · 2024-01-16 22:34:19Z

I got data into the table by modifying these two functions:

function getRecords() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var rg = sheet.getDataRange();
  var [hs, ...vs] = rg.getValues();
  return {headers: hs, records:vs};
}

function populateTable(obj) {
  console.log("Header received: %s", obj.headers)
  console.log("Data received:", obj.records);  // Log the received data
  var tableBody = document.getElementById('jobTableBody');
  tableBody.innerHTML = '';
  var records = obj.records;
  if (records && records.length > 0) {
    for (var i = 0; i < records.length; i++) {
      var row = records[i];
      console.log("Row: [%s] = %s",i,JSON.stringify(row));
      var newRow = tableBody.insertRow(-1);
      for (var j = 0; j < row.length; j++) {
        var cell = row[j];
        console.log("Cell[%s][%s] = %s",i,j,row[j])
        var newCell = newRow.insertCell(-1);
        newCell.textContent = cell;
      }
      var actionsCell = newRow.insertCell(-1);
      actionsCell.innerHTML = `<button onclick="editRecord(${i},${row[i]})">Edit</button> <button onclick="deleteRecord(${i})">Delete</button>`;
    }
  } else {
    console.log("No records found.");  // Log when no records are found
    var noRecordsRow = tableBody.insertRow(-1);
    var noRecordsCell = noRecordsRow.insertCell(-1);
    noRecordsCell.colSpan = 15;  // Set the colspan to the number of columns in your table
    noRecordsCell.textContent = "No records found.";
  }
}

Collectives™ on Stack Overflow

HTML table not populating

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related