Convert Google Sheets to JSON

To convert Google Sheets to JSON, you have several methods depending on your needs—manual, formula-based, script-based, or via API. Here’s how you can do each:

Method 1: Publish to Web (Simple Public JSON)

1. Open Google Sheets.

2. Click File > Share > Publish to web.

3. Select the sheet and publish it.

4. Copy the Sheet ID from the URL (https://docs.google.com/spreadsheets/d/SHEET_ID/edit).

5. Use the following URL format to get JSON (using a public API by Google Visualization):

   bash
   CopyEdit
   https://docs.google.com/spreadsheets/d/SHEET_ID/gviz/tq?tqx=out:json
   

✅ Easy but only works for public sheets and returns a nested JSON format that needs to be cleaned.

Method 2: Use Google Apps Script (Custom JSON Format)

1. Go to your Google Sheet.

2. Click Extensions > Apps Script.

3. Paste the following script:

javascript
CopyEdit
function doGet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var data = sheet.getDataRange().getValues();
  var headers = data.shift();
  var jsonArray = data.map(row => {
    let obj = {};
    row.forEach((cell, i) => obj[headers[i]] = cell);
    return obj;
  });
  return ContentService.createTextOutput(JSON.stringify(jsonArray))
    .setMimeType(ContentService.MimeType.JSON);
}

4. Click Deploy > Test deployments or Deploy > New deployment, and enable web access.

5. You’ll get a URL that returns your sheet data as JSON.

✅ Great for private sheets and custom formatting.
🔐 You can set permissions.

Method 3: Export as CSV, Then Convert to JSON

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

2. Convert CSV to JSON using an online tool or a script.

Example using JavaScript:

javascript
CopyEdit
const csvToJson = csv => {
  const [headerLine, ...lines] = csv.trim().split('n');
  const headers = headerLine.split(',');
  return lines.map(line => {
    const values = line.split(',');
    return headers.reduce((obj, key, i) => (obj[key] = values[i], obj), {});
  });
};

Method 4: Use Third-party Tools or Add-ons

• Tools: SheetDB, Blockspring, or Zapier.

• Add-ons: Search for “JSON Export” add-ons in Google Sheets.

Best Practice Tips

• Use headers in the first row to define keys in your JSON.

• Clean your data (no merged cells, hidden rows).

• For API-like use, the Apps Script method is most reliable.

Let me know if you want the JSON formatted in a specific way (nested, flat, etc.).