Fetch Google Sheets Data to HTML Table

Use Google Sheets as a free live database

Display real-time data in HTML using JavaScript

Step 1: Prepare Google Sheets

Google Sheets JSON URL

              
TEXT

          Use this endpoint
        
https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/gviz/tq?tqx=out:json

Step 2: Create HTML Table

              
            
HTML

          Basic table structure
        
<table id="dataTable">
  <thead></thead>
  <tbody></tbody>
</table>

Step 3: Fetch Data Using JavaScript

              
            
JavaScript

          Fetch and render data
        
async function loadData() {
  const url = "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/gviz/tq?tqx=out:json";

  const res = await fetch(url);
  const text = await res.text();

  const json = JSON.parse(text.substr(47).slice(0, -2));

  const tableHead = document.querySelector('#dataTable thead');
  const tableBody = document.querySelector('#dataTable tbody');

  // Headers
  let headers = json.table.cols.map(col => col.label);
  let headerRow = "<tr>" + headers.map(h => `<th>${h}</th>`).join("") + "</tr>";
  tableHead.innerHTML = headerRow;

  // Rows
  json.table.rows.forEach(row => {
    let rowData = row.c.map(cell => cell ? cell.v : "");
    let tr = "<tr>" + rowData.map(d => `<td>${d}</td>`).join("") + "</tr>";
    tableBody.innerHTML += tr;
  });
}

loadData();

Example Output Table

              
            
TEXT

          Sample data
        
Name    | Age | City
--------|-----|------
Arun    | 25  | Chennai
Priya   | 22  | Coimbatore
Rahul   | 28  | Bangalore

How It Works

Google Sheets publishes data as JSON
Fetch API retrieves the data
JSON is parsed into usable format
JavaScript loops and builds table

Performance Tips

Keep dataset under 10K rows
Use pagination for large data
Cache responses for speed
Minimize DOM updates

Common Errors

Sheet not public → Publish to web
JSON parse error → Use correct substring
Empty cells → Handle null values
Slow loading → Reduce data size

Pro Tips

Use CSS for better table design
Add search and sorting
Convert to reusable component
Use Google Sheets API for production

Conclusion

Google Sheets can act as a powerful live backend for your website.

With JavaScript, you can easily convert sheet data into dynamic HTML tables.

This method is simple, free, and perfect for modern web apps.