📋 Save Google Sheet as JSON

1. Overview

\[ \begin{array}{l} \textbf{You can export Google Sheets data to JSON format --} \\ \text{using Google Apps Script to convert rows and columns into structured JSON.} \\ \textbf{This is useful for APIs, web apps, or headless CMS integrations --} \\ \text{where sheet data serves as the backend or content source.} \end{array} \]

2. Use Cases

            
                - Convert sheet data into JSON for use in websites or apps
                - Generate mock data from spreadsheets
                - Build simple REST APIs backed by Google Sheets
                - Automate exports for CMS or frontend tools like Vue, React, or Next.js
            
        

JSON is a universal data exchange format and works perfectly with front-end frameworks.

3. Apps Script to Convert Sheet to JSON

            
                function sheetToJSON() {
                  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
                  var data = sheet.getDataRange().getValues(); // All rows & columns
                  
                  var headers = data[0]; // First row = column headers
                  var jsonArray = [];

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

                  Logger.log(JSON.stringify(jsonArray, null, 2));
                  return jsonArray;
                }
            
        

This script reads the entire sheet, converts each row into an object using headers, and stores it in an array. The result is a valid JSON array of objects.

4. Serve JSON via Web App URL

            
                function doGet() {
                  var json = sheetToJSON(); // Call previous function
                  return ContentService
                    .createTextOutput(JSON.stringify(json))
                    .setMimeType(ContentService.MimeType.JSON);
                }
            
        

Deploy this function as a **Web App** to publicly or privately serve your sheet’s JSON data. Go to **Deploy → Manage Deployments → New Deployment**, set access and deploy.

5. JSON Output Example

            
                [
                  {
                    "Name": "Alice",
                    "Email": "[email protected]",
                    "Age": 28
                  },
                  {
                    "Name": "Bob",
                    "Email": "[email protected]",
                    "Age": 32
                  }
                ]
            
        

This structure is readable by JavaScript, Python, APIs, CMS platforms, or no-code tools like Bubble, Webflow, etc.

6. Optional: Download JSON as File

            
                function saveJSONToDrive() {
                  var json = sheetToJSON();
                  var file = DriveApp.createFile("data.json", JSON.stringify(json, null, 2), MimeType.PLAIN_TEXT);
                  Logger.log("File URL: " + file.getUrl());
                }
            
        

This will create and save a JSON file into your Google Drive. You can then share or download it.

7. Conclusion

            
                Saving Google Sheet data as JSON allows seamless integration with web and mobile apps.
                You can export, serve, or download JSON with very few lines of Apps Script.
                It's a lightweight, flexible solution for developers and no-code workflows alike.
            
        

You can even schedule JSON exports using time-based triggers for automation.