Get Google Spreadsheets Data as JSON in your Website

Translate

Get Google Spreadsheets Data as JSON in your Website

Get Google Spreadsheets Data as JSON in Your Website – A Complete Guide

Google Sheets is a popular tool for storing and managing tabular data in the cloud. But did you know you can use it as a lightweight backend database for your website?

In this detailed blog, we’ll walk through how to expose your Google Spreadsheet data as JSON using Google Apps Script and fetch that data into your website using JavaScript. This method lets you use spreadsheets like an API—ideal for building live dashboards, forms, product listings, and more.

What You’ll Learn

How to publish Google Spreadsheet data as a JSON API
How to write an Apps Script web app that returns JSON
How to make the API public or private
How to fetch and display that JSON data on your HTML/JavaScript website

Why Use Google Sheets as a JSON Source?

Here are a few practical reasons:

No server setup needed
Easy for non-technical users to update data
Instantly accessible online
Works seamlessly with websites, JavaScript, or apps
Great for public datasets, content management, or prototypes

Step 1: Prepare Your Google Sheet

Create a new Google Spreadsheet or use an existing one.

Example structure:

Name	Age	City
Alice	30	New York
Bob	25	Chicago
Charlie	35	San Diego

This will be our sample data.

Step 2: Open Apps Script Editor

Open your Google Sheet.
Click on Extensions > Apps Script.
Delete any default code in Code.gs.

Step 3: Write the Apps Script to Output JSON

Paste the following code:

javascript
function doGet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data.shift();
  var jsonArray = [];

  for (var i = 0; i < data.length; i++) {
    var row = {};
    for (var j = 0; j < headers.length; j++) {
      row[headers[j]] = data[i][j];
    }
    jsonArray.push(row);
  }

  return ContentService
    .createTextOutput(JSON.stringify(jsonArray))
    .setMimeType(ContentService.MimeType.JSON);
}

Explanation:

doGet() is triggered when a browser requests the script URL.
getDataRange().getValues() fetches all the spreadsheet data.
headers are the column names (first row).
Each row is converted into a JSON object.
The result is a valid JSON array like:

json
[
  {
    "Name": "Alice",
    "Age": 30,
    "City": "New York"
  },
  {
    "Name": "Bob",
    "Age": 25,
    "City": "Chicago"
  },
  {
    "Name": "Charlie",
    "Age": 35,
    "City": "San Diego"
  }
]

Step 4: Deploy as Web App

In Apps Script, click on Deploy > Manage deployments.
Click New deployment.
Under Select type, choose Web app.
Set:
- Description: “Public JSON API”
- Execute as: Me
- Who has access: Anyone (or Anyone with the link)
Click Deploy.
Approve the required permissions.
Copy the Web App URL.

Your spreadsheet is now live as a public JSON API!

Step 5: Make Your Spreadsheet Public (If Required)

To allow everyone to read the data:

In the spreadsheet, click Share > General access.
Change to Anyone with the link > Viewer.

If you keep it private, only authorized users (you) can access the JSON endpoint.

Step 6: Fetch JSON from Your Website

Now let’s consume the JSON in a website. Create an HTML file:

html
<!DOCTYPE html>
<html>
<head>
  <title>Google Sheets JSON Example</title>
</head>
<body>
  <h2>Data from Google Spreadsheet</h2>
  <table border="1" id="data-table">
    <thead>
      <tr>
        <th>Name</th>
        <th>Age</th>
        <th>City</th>
      </tr>
    </thead>
    <tbody></tbody>
  </table>

  <script>
    const endpoint = "YOUR_WEB_APP_URL_HERE";

    fetch(endpoint)
      .then(response => response.json())
      .then(data => {
        const tbody = document.querySelector("#data-table tbody");
        data.forEach(row => {
          const tr = document.createElement("tr");
          tr.innerHTML = `
            <td>${row.Name}</td>
            <td>${row.Age}</td>
            <td>${row.City}</td>
          `;
          tbody.appendChild(tr);
        });
      })
      .catch(error => {
        console.error("Error fetching data:", error);
      });
  </script>
</body>
</html>

Replace YOUR_WEB_APP_URL_HERE with the URL you copied during deployment.

Step 7: Customize Your API

Filter by Value

You can allow query parameters like ?city=Chicago.

Update the script like this:

javascript
function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data.shift();
  var cityFilter = e.parameter.city;
  var jsonArray = [];

  for (var i = 0; i < data.length; i++) {
    var row = {};
    for (var j = 0; j < headers.length; j++) {
      row[headers[j]] = data[i][j];
    }

    if (!cityFilter || row["City"] === cityFilter) {
      jsonArray.push(row);
    }
  }

  return ContentService
    .createTextOutput(JSON.stringify(jsonArray))
    .setMimeType(ContentService.MimeType.JSON);
}

You can now call:

ruby
https://your-script-url/?city=Chicago

And get only the matching results.

Use Cases

Live Product Catalogs: Manage products in a sheet and display on a website.
Event Listings: Update schedules via spreadsheet.
CMS Lite: Manage page content or blog posts via Google Sheets.
Student Lists / Leaderboards: Auto-refresh data from shared classroom sheets.
Forms or Submissions: Show collected form data in real time.

Security Notes

Only publish the web app as “Anyone” if your data is public.
For private APIs, deploy with access only to your Google account or specific users.
Use Apps Script libraries like ScriptApp.getOAuthToken() for advanced authentication.

Conclusion

Google Sheets is more than just a spreadsheet. With Apps Script and a few lines of JavaScript, you can turn it into a dynamic, cloud-powered backend for your website or application. Whether you're creating dashboards, directories, or just displaying dynamic lists—converting spreadsheet data to JSON is an efficient and scalable solution.

This low-code method opens doors to building interactive, data-driven sites without traditional databases or servers. Once deployed, all your users need is a browser—and you control everything from the sheet itself.

So the next time you're thinking of a fast, editable, cloud-based backend—remember: Google Sheets can be your API.