Translate

Save Google Sheet as JSON

How to Save a Google Sheet as JSON: A Step-by-Step Guide

Google Sheets is a powerful cloud-based spreadsheet tool used for managing and organizing data. Sometimes, especially in web development or app integration, you may need to convert Google Sheet data into JSON (JavaScript Object Notation). JSON is a lightweight data-interchange format that is easy for machines to parse and generate, and easy for humans to read and write.

This guide will walk you through multiple ways to save or export Google Sheets as JSON, using both built-in tools and custom Google Apps Script. No plugins required, and you can automate the process as needed.

Why Convert Google Sheets to JSON?

There are several reasons why converting Google Sheets to JSON is useful:

  • Frontend Web Development: Load live data from Google Sheets into your JavaScript web app.

  • API Emulation: Turn a Sheet into a makeshift API.

  • Mobile App Development: Use it as a lightweight backend.

  • Data Portability: JSON is a widely accepted data format.

Method 1: Publish Google Sheet to Web and Fetch as JSON

Google Sheets allows you to publish a spreadsheet to the web. You can then fetch the data using a simple GET request and convert it to JSON.

Step 1: Prepare the Sheet

  1. Go to your Google Sheet.

  2. Make sure your data is in a clean tabular format. The first row should contain column headers.

  3. Give your spreadsheet and sheet tab meaningful names.

Step 2: Publish the Sheet

  1. Click on File > Share > Publish to Web.

  2. Choose:

    • Entire Document or Specific Sheet

    • Format: Web page

  3. Click Publish.

You will get a public URL like:

https://docs.google.com/spreadsheets/d/e/2PACX-1vQxyz123abc/pubhtml

Unfortunately, this gives an HTML version, not JSON.

Method 2: Use Google Sheets API (for Developers)

The Google Sheets API v4 allows you to programmatically fetch data and convert it into JSON.

Step 1: Get API Credentials

  1. Go to Google Cloud Console.

  2. Create a new project.

  3. Enable the Google Sheets API.

  4. Create credentials (OAuth 2.0 or API key).

  5. Share your sheet with the service account email, if using OAuth.

Step 2: Use the Sheets API

Make a GET request:

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/SHEET_NAME?key=API_KEY

The response will be in JSON like:

{
  "range": "Sheet1!A1:C3",
  "majorDimension": "ROWS",
  "values": [
    ["Name", "Age", "City"],
    ["Ravi", "25", "Mumbai"],
    ["Neha", "30", "Delhi"]
  ]
}

Method 3: Convert Google Sheet to JSON Using Google Apps Script

Google Apps Script provides the simplest and most flexible method for converting a Google Sheet to JSON—especially if you don’t want to deal with APIs or developer credentials.

Step 1: Open Script Editor

  1. In your Google Sheet, click on Extensions > Apps Script.

Step 2: Paste This Code

function doGet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var data = sheet.getDataRange().getValues();
  
  var headers = data[0];
  var json = [];

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

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

Step 3: Deploy as Web App

  1. Click Deploy > Manage deployments.

  2. Click New deployment.

  3. Select Web App.

  4. Set access to Anyone.

  5. Click Deploy.

You’ll receive a public URL like:

https://script.google.com/macros/s/AKfycbxyz123/exec

Visiting this link will return your Google Sheet’s data as JSON.

Sample Output:

[
  {
    "Name": "Ravi",
    "Age": 25,
    "City": "Mumbai"
  },
  {
    "Name": "Neha",
    "Age": 30,
    "City": "Delhi"
  }
]

Method 4: Manually Export Google Sheet as JSON (Using Google Colab or Scripts)

If you only need one-time export:

Option A: Export as CSV and Convert

  1. Go to File > Download > Comma-separated values (.csv).

  2. Use any online tool or script to convert CSV to JSON.

Option B: Use Google Colab

In a Google Colab notebook:

import pandas as pd

sheet_url = "https://docs.google.com/spreadsheets/d/<SHEET_ID>/export?format=csv"
df = pd.read_csv(sheet_url)
json_data = df.to_json(orient='records')
print(json_data)

This pulls data directly and converts it to JSON.

Best Practices

  • Always keep the first row of your Sheet reserved for column headers.

  • Avoid blank rows and columns.

  • If using public access or Web Apps, don’t include sensitive data.

  • For dynamic data that updates frequently, prefer Apps Script-based JSON endpoints or Google Sheets API.

  • Add caching mechanisms for performance if you are hitting the API frequently.

Conclusion

Converting Google Sheets to JSON opens up a wide array of possibilities—from building real-time dashboards to mobile app integrations. Depending on your level of technical expertise and use case, you can choose from:

  • Simple Web Publishing + Script

  • Full-fledged API access

  • Custom Web App using Apps Script

  • Manual CSV + JSON conversion

Each method has its pros and cons. For most users, Google Apps Script is the fastest, easiest, and most powerful method for getting live JSON from Sheets.

Let Google Sheets become your mini-database, and use the power of JSON to bring your apps to life.