Publish Google Spreadsheets as JSON with Apps Script: A Complete Guide

Google Sheets is a powerful tool not just for spreadsheets but also for lightweight data storage, especially in web applications and prototypes. But what if you want to access your spreadsheet data in JSON format for use in a web or mobile app?

In this blog, we’ll walk you through the process of publishing a Google Spreadsheet as a JSON API endpoint using Google Apps Script. This method lets you pull spreadsheet data in a structured format that’s easy to use in JavaScript, React, Vue, Flutter, or any platform that consumes JSON.

Why Publish a Spreadsheet as JSON?

Here are some real-world use cases:

• Use Google Sheets as a lightweight CMS or backend for your app

• Display spreadsheet content dynamically on a website

• Fetch data into frontend frameworks like React or Vue

• Integrate with tools like Google Data Studio, Power BI, or Zapier

• Create public datasets that others can use programmatically

Step 1: Prepare Your Google Spreadsheet

Create or open a Google Spreadsheet with a well-structured format.

Example:

A            | B           | C
-------------|-------------|-------------
ID           | Name        | Email
101          | Alice       | alice@example.com
102          | Bob         | bob@example.com
103          | Charlie     | charlie@example.com

Make sure:

• The first row contains headers

• There are no empty columns or rows in the dataset

Step 2: Open Google Apps Script

1. In your spreadsheet, click Extensions > Apps Script.

2. Delete any default code in Code.gs and paste the following script:

function doGet(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  const headers = data.shift(); // first row as headers
  const jsonData = data.map(row => {
    let obj = {};
    headers.forEach((key, i) => {
      obj[key] = row[i];
    });
    return obj;
  });

  const response = ContentService.createTextOutput(JSON.stringify(jsonData));
  response.setMimeType(ContentService.MimeType.JSON);
  return response;
}

Step 3: Deploy as a Web App

To make your script accessible from a public or restricted URL:

1. Click Deploy > Manage Deployments.

2. Click New deployment.

3. Choose "Select type" > Web app.

4. Provide a name and description.

5. Under Execute as, select Me.

6. Under Who has access, choose Anyone or Anyone with the link, depending on your preference.

7. Click Deploy and Authorize the script when prompted.

8. Copy the Web App URL — this is your JSON endpoint.

Step 4: Test Your JSON API

Paste the web app URL into your browser. You should see raw JSON output:

[
  {
    "ID": 101,
    "Name": "Alice",
    "Email": "alice@example.com"
  },
  {
    "ID": 102,
    "Name": "Bob",
    "Email": "bob@example.com"
  },
  {
    "ID": 103,
    "Name": "Charlie",
    "Email": "charlie@example.com"
  }
]

You can now use this URL in your application to fetch spreadsheet data using fetch(), axios, or any HTTP client.

Step 5: Optional - Accept Query Parameters

You can modify the doGet() function to filter results based on URL parameters.

Example:

function doGet(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data.shift();
  const jsonData = data.map(row => {
    let obj = {};
    headers.forEach((key, i) => {
      obj[key] = row[i];
    });
    return obj;
  });

  // Filter by ID if passed in URL
  if (e.parameter.id) {
    const filtered = jsonData.filter(item => String(item.ID) === e.parameter.id);
    return ContentService.createTextOutput(JSON.stringify(filtered))
      .setMimeType(ContentService.MimeType.JSON);
  }

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

Now you can call:

https://script.google.com/macros/s/your-script-id/exec?id=102

And get just one matching record:

[
  {
    "ID": 102,
    "Name": "Bob",
    "Email": "bob@example.com"
  }
]

Step 6: Tips for Advanced Usage

• CORS headers: If you want to access your endpoint from a browser-based app, add:

response.setHeader("Access-Control-Allow-Origin", "*");

• Multiple Sheets: Use getSheetByName("Sheet2") to select specific sheets.

• Authentication: For sensitive data, use ScriptApp.getOAuthToken() and check user permissions.

• Pagination: Add support for limit and offset query parameters.

Troubleshooting

• If you see a permissions error, make sure the deployment allows access to "Anyone" or "Anyone with the link."

• If JSON is not formatted properly, ensure the first row has no empty cells.

• Avoid merged cells or formulas with errors in the spreadsheet.

Conclusion

Publishing your Google Spreadsheet as a JSON endpoint using Apps Script opens up a world of integration possibilities. Whether you're building a lightweight CMS, feeding a JavaScript chart with live data, or creating a mobile app backend, this method is simple, flexible, and free.

No database? No problem. With Google Sheets and Apps Script, your data can be structured, served, and consumed—all in the cloud.