Working with JSON Data in Google Apps Script

JSON is the lingua franca of APIs and data exchange. Apps Script handles it natively — no libraries needed. Here's everything you need to know to work with JSON effectively.

Parse a JSON string

function parseJson() {
  const jsonString = '{"name":"Alice","age":30,"active":true}';
  const obj = JSON.parse(jsonString);

  Logger.log(obj.name);   // Alice
  Logger.log(obj.age);    // 30
  Logger.log(obj.active); // true
}

Stringify an object to JSON

function stringifyObject() {
  const data = {
    report: 'Q1 Sales',
    total: 24500,
    generated: new Date().toISOString(),
  };

  const jsonString = JSON.stringify(data);
  Logger.log(jsonString);

  // Pretty-print with indentation
  Logger.log(JSON.stringify(data, null, 2));
}

Parse JSON from an API response

function fetchAndParseApi() {
  const url = 'https://api.exchangerate-api.com/v4/latest/USD';
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());

  Logger.log('Base currency: ' + data.base);
  Logger.log('EUR rate: ' + data.rates.EUR);
  Logger.log('GBP rate: ' + data.rates.GBP);
}

Access nested JSON data

function nestedJson() {
  const json = `{
    "user": {
      "id": 42,
      "name": "Bob",
      "address": {
        "city": "London",
        "postcode": "EC1A 1BB"
      },
      "tags": ["admin", "editor"]
    }
  }`;

  const data = JSON.parse(json);

  Logger.log(data.user.name);           // Bob
  Logger.log(data.user.address.city);   // London
  Logger.log(data.user.tags[0]);        // admin
}

Flatten JSON array to a Google Sheet

function jsonArrayToSheet() {
  const json = `[
    {"name":"Alice","role":"Engineer","salary":90000},
    {"name":"Bob","role":"Designer","salary":80000},
    {"name":"Carol","role":"Manager","salary":110000}
  ]`;

  const records = JSON.parse(json);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();

  if (records.length === 0) return;

  // Write headers from object keys
  const headers = Object.keys(records[0]);
  sheet.appendRow(headers);

  // Write data rows
  records.forEach(record => {
    sheet.appendRow(headers.map(h => record[h] ?? ''));
  });
}

Handle nested arrays from an API

function flattenNestedApi() {
  const url = 'https://jsonplaceholder.typicode.com/posts';
  const response = UrlFetchApp.fetch(url);
  const posts = JSON.parse(response.getContentText());

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();
  sheet.appendRow(['ID', 'User ID', 'Title', 'Body']);

  posts.slice(0, 20).forEach(post => {
    sheet.appendRow([post.id, post.userId, post.title, post.body]);
  });
}

Store and retrieve JSON in PropertiesService

PropertiesService only stores strings, so use JSON.stringify / JSON.parse when storing objects:

function storeJsonConfig() {
  const config = {
    apiEndpoint: 'https://api.example.com',
    maxRetries: 3,
    features: ['sync', 'alerts'],
  };

  PropertiesService.getScriptProperties()
    .setProperty('CONFIG', JSON.stringify(config));
}

function readJsonConfig() {
  const raw = PropertiesService.getScriptProperties().getProperty('CONFIG');
  if (!raw) return null;

  const config = JSON.parse(raw);
  Logger.log(config.apiEndpoint);     // https://api.example.com
  Logger.log(config.features[0]);     // sync
  return config;
}

Handle parse errors safely

function safeJsonParse(text) {
  try {
    return JSON.parse(text);
  } catch (e) {
    Logger.log('JSON parse error: ' + e.message);
    return null;
  }
}

Tips

JSON.parse() throws a SyntaxError on invalid JSON — always wrap in try/catch when parsing external data.
JSON.stringify() ignores undefined values and functions. Use null instead of undefined for missing values.
Large JSON responses from APIs can be slow to parse. If you only need a few fields, consider whether you can filter at the API level with query parameters.
When writing JSON to a Sheet cell, stringify it first: sheet.getRange('A1').setValue(JSON.stringify(obj)).