📋 Publish Google Spreadsheets as JSON with Apps Script

1. Overview

\[ \begin{array}{l} \textbf{Google Apps Script can turn your spreadsheet into a live JSON API --} \\ \text{by creating a web app that outputs spreadsheet data as JSON.} \\ \textbf{This enables you to use Sheets as a backend data source --} \\ \text{for web apps, mobile apps, and no-code platforms.} \end{array} \]

2. Use Cases

            
                - Use spreadsheet data in external websites or frontend apps
                - Build dashboards with dynamic data sources
                - Enable low-code/no-code tools to fetch data via REST
                - Create mock APIs for frontend prototyping
            
        

This method provides real-time, serverless access to your data — no database required.

3. Basic Apps Script Code to Publish as JSON

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

                  for (let i = 1; i < data.length; i++) {
                    const row = {};
                    for (let 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);
                }
            
        

This code reads the sheet, builds a JSON array of row objects using headers, and serves it.

4. Deployment Instructions

            
                1. Open your Google Sheet → Extensions → Apps Script
                2. Paste the doGet() function
                3. Click “Deploy” → “Manage Deployments”
                4. Create a new deployment as a “Web App”
                5. Set access to “Anyone” or “Anyone with the link”
                6. Click Deploy and copy the Web App URL
            
        

Visiting this URL returns the spreadsheet data as JSON.

5. Sample Output

            
                [
                  {
                    "Name": "Alice",
                    "Email": "[email protected]",
                    "Score": 85
                  },
                  {
                    "Name": "Bob",
                    "Email": "[email protected]",
                    "Score": 92
                  }
                ]
            
        

Perfect for consumption in JavaScript apps, Airtable, Retool, or mobile APIs.

6. Advanced Tip: Add Query Filtering

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

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

                  if (e.parameter.name) {
                    return ContentService
                      .createTextOutput(JSON.stringify(
                        json.filter(r => r["Name"] === e.parameter.name)
                      ))
                      .setMimeType(ContentService.MimeType.JSON);
                  }

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

Now you can call `?name=Alice` in the Web App URL to filter results.

7. Conclusion

            
                Publishing a Google Sheet as a live JSON API is a powerful, flexible alternative 
                to traditional backend databases. It’s perfect for quick prototypes, integrations, 
                or educational apps.
            
        

With zero server setup and full control over the data, you can build faster and smarter.