📋Get Google Spreadsheets Data as JSON in your Website

1. Overview

\[ \begin{array}{l} \textbf{You can fetch and display Google Spreadsheet data as JSON on your website --} \\ \text{by using Google Sheets' built-in "Publish to the web" and Google Visualization API.} \\ \textbf{This enables real-time, serverless data access --} \\ \text{without building a custom backend or database.} \end{array} \]

2. Core Steps

\[ \begin{array}{ll} \mathbf{Step\ 1:} & \text{Create or open your Google Spreadsheet with the desired data.} \\ \mathbf{Step\ 2:} & \text{Go to File} \rightarrow \text{"Publish to the web"} \rightarrow \text{Entire document, Web page format.} \\ \mathbf{Step\ 3:} & \text{Copy the spreadsheet's sharing URL (make sure it is public).} \\ \mathbf{Step\ 4:} & \text{Get the Spreadsheet ID from the URL.} \\ \mathbf{Step\ 5:} & \text{Use the Google Visualization API endpoint to fetch JSON.} \\ \mathbf{Step\ 6:} & \text{Parse the returned JSON in JavaScript and render it on your site.} \\ \mathbf{Step\ 7:} & \text{(Optional) Apply filters, sorting, or formatting in your script.} \end{array} \]

3. Sample JavaScript Fetch

// Example: Fetch Google Sheets data as JSON
const sheetID = "YOUR_SPREADSHEET_ID";
const base = `https://docs.google.com/spreadsheets/d/${sheetID}/gviz/tq?tqx=out:json`;

fetch(base)
  .then(res => res.text())
  .then(text => {
    const json = JSON.parse(text.substr(47).slice(0, -2)); // Strip Google's extra characters
    console.log(json.table.rows); // Data rows
  });

4. Important Notes

\[ \begin{array}{l} \text{• The spreadsheet must be shared with "Anyone with the link" or "Public".} \\ \text{• The Google Visualization API adds extra characters, which must be stripped before parsing.} \\ \text{• Avoid exposing sensitive data in public sheets.} \\ \text{• Use HTTPS for secure data fetching.} \\ \text{• For private data, use OAuth authentication instead.} \end{array} \]

5. Conceptual Flow (in LaTeX)

The process can be described as:

\[ \text{Google Sheets (Public)} \xrightarrow{\text{Visualization API JSON}} \xrightarrow{\text{JavaScript Fetch}} \{\text{Web Page Display}\} \]

Where: \[ \text{Displayed Data} = \{\text{Row 1}, \text{Row 2}, \text{...}\} \]