JavaScript for Sheets Updated November 2026

JSON.parse and JSON.stringify

Q: How do I handle a nested object in a sheet?

Flatten the fields you need into columns (o.customer.name, o.customer.email), or store the whole JSON string in one column and parse it when needed.

Q: What about fields that are missing in some records?

Use optional chaining and a default: o.customer?.email ?? "" so the row still has the right number of columns.

JSON.parse(text) turns a JSON string (from UrlFetchApp, a cell or PropertiesService) into JavaScript objects and arrays; JSON.stringify(value) does the reverse, and JSON.stringify(value, null, 2) indents it for reading. Nested fields are then plain property access: data.customer.email, and arrays map straight to rows for setValues.

Syntax

JSON.parse(text) | JSON.stringify(value) | JSON.stringify(value, null, 2)

Examples

const text = '{"orders":[{"id":1,"total":30.5},{"id":2,"total":12}]}';
const data = JSON.parse(text);
Logger.log(data.orders[1].total);

Result: 12

const rows = data.orders.map(o => [o.id, o.total]);
SpreadsheetApp.getActiveSheet().getRange(2, 1, rows.length, 2).setValues(rows);

Result: A2:B3 hold 1, 30.5 and 2, 12

const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
let body;
try {
  body = JSON.parse(res.getContentText());
} catch (err) {
  throw new Error("Not JSON (" + res.getResponseCode() + "): " + res.getContentText().slice(0, 200));
}

Result: Either parsed data or a clear error showing the status and the start of the response

Common mistakes

Unexpected token u in JSON at position 0: you parsed undefined; the property or cell is empty, so check it exists first.
Unexpected token < in JSON: the text is HTML (error page or login); log it and check the URL and credentials.
Writing an object to a cell shows [object Object]: setValue needs a string, so stringify it, or write its fields into separate columns.
Dates become strings after a stringify and parse round trip: convert them back with new Date(value) when reading.

Practice JSON.parse and JSON.stringify

These drills check your answer on live data, free, no account needed.

Flatten a nested objectFlatten nested plain objects into one level with dot-separated keys: `{ a: { b: 1 } }` becomes `{ "a.b": 1 }`. Only plain objects are opened up; arrays, dates, `null` and other values are kept as they are. An empty nested object adds no keys.
Read a nested value by pathFollow a dot path like `"user.address.city"` or `"items.0.id"` (numbers index arrays) into `obj` and return the value there. If a step along the way is missing, `null` or `undefined`, or the final value is `undefined`, return `fallback`. Real values such as `0`, `false`, `""` and a final `null` are returned as they are. An empty path returns `obj`.
Deep equalityReturn `true` when `a` and `b` hold the same data. Primitives compare with `===`, except that `NaN` equals `NaN`. Arrays are equal when they have the same length and equal items in order. Plain objects are equal when they have the same keys (in any order) with equal values; a key set to `undefined` is not the same as a missing key. Dates are equal when they hold the same time. An array never equals a plain object.

Questions

How do I handle a nested object in a sheet?