htmlgoogle-sheetsgoogle-apps-scriptweb-applications

How do I show data from a spreadsheet? it shows "undefined"


I've been checked for more than half a day now. I am totally blanked right now! I don't know where am I missing something? Please help me. My .gs code (Google Script) is below

function readRecords() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  return data.slice(1).map(row => {
    return headers.reduce((record, header, idx) => ({ ...record, [header]: row[idx] }), {});
  });
}

My code with HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <title>Show Records</title>
  </head>
  <body>
    <div id="recordsContainer"></div>

    <script>
      function displayRecords(records) {
        const container = document.getElementById('recordsContainer');
        container.innerHTML = '';
        if (records.length === 0) {
          container.innerHTML = '<p>No records found.</p>';
          return;
        }

        const table = document.createElement('table');
        table.classList.add('table');
        const thead = table.createTHead();
        const row = thead.insertRow();
        Object.keys(records[0]).forEach(header => {
          const th = document.createElement('th');
          th.textContent = header;
          row.appendChild(th);
        });

        const tbody = table.createTBody();
        records.forEach(record => {
          const row = tbody.insertRow();
          Object.values(record).forEach(value => {
            const td = row.insertCell();
            td.textContent = value;
          });
        });

        container.appendChild(table);
      }

      function fetchRecords() {
        google.script.run.withSuccessHandler(displayRecords).readRecords();
      }

      window.onload = fetchRecords;
    </script>
  </body>
</html>

Where is my problem and what am I missing? Please help me:

The image showing the "undefined" i add some picture that show "undefined"image

The console

enter image description here

Uncaught TypeError: container is null


Solution

  • You can fetch google spreadsheet data like this, need to add your spreadsheetId, range and apiKey.

    your spreadsheetId is the long number that appears after "d/" in the URL. range is like A3:D or sheetname!A1:A3. And you can get apiLey from https://console.cloud.google.com/apis/dashboard.

     async function fetchSheetData() {
     const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}?       key=${apiKey}`;
     
       try {
            const response = await fetch(url);
              if (!response.ok) {
              throw new Error(`HTTP error! status: ${response.status}`);
            }
             const data = await response.json();
             
             } catch (error) {
            console.error("Error fetching data:", error);
          }
        }