Most SaaS APIs return nested JSON. Apps Script should JSON.parse once, then map fields into rectangular sheet rows.
This pullTickets example reads API_TOKEN from Script Properties, GETs open tickets, and supports either data or items arrays.
Nested priority.name and assignee.email are flattened; missing nodes become empty strings instead of throwing.
muteHttpExceptions lets you include response bodies in error messages for faster debugging.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Value | Purpose |
|---|---|---|
| Script property | API_TOKEN | Bearer token |
| API_URL | https://api.example.com/v1/tickets | Endpoint |
| Tickets | A–F | Flattened ticket fields |
| Query | status=open | Server-side filter |
What this script does
pullTickets authenticates, parses JSON, flattens each ticket, and appends rows under a header.
Prerequisites
API token stored in Script Properties; Tickets sheet; UrlFetch permission.
- Replace API_URL with a real endpoint
- Know the JSON envelope key
- Authorize UrlFetchApp
Walkthrough
Set API_TOKEN, run pullTickets, inspect Tickets columns for nested field mapping.
Edge cases
If the API returns a single object, wrap it in an array before mapping.
- Large payloads may hit response size limits
- Dates often arrive as ISO strings — leave as text or coerce
How to test
Mock with a known payload using a temporary function that JSON.parses a fixture string.
Hardening for production
Dedupe by ticket id before append; store etag/cursor in Properties.
Variations
Write raw JSON to Drive for audit, then parse; or use XmlService for XML APIs.
Full code: pullTickets()
Set script property API_TOKEN, point API_URL at your API, then run pullTickets().
/**
* Fetch a JSON API, parse nested fields, and append flattened rows.
*/
const API_URL = "https://api.example.com/v1/tickets";
const DEST = "Tickets";
function pullTickets() {
const token = PropertiesService.getScriptProperties().getProperty("API_TOKEN");
if (!token) throw new Error("Set script property API_TOKEN");
const resp = UrlFetchApp.fetch(API_URL + "?status=open", {
method: "get",
headers: {
Authorization: "Bearer " + token,
Accept: "application/json",
},
muteHttpExceptions: true,
});
const code = resp.getResponseCode();
const body = resp.getContentText();
if (code >= 300) throw new Error("API " + code + ": " + body.slice(0, 500));
const json = JSON.parse(body);
const items = json.data || json.items || [];
if (!Array.isArray(items)) throw new Error("Unexpected JSON shape");
const sheet = SpreadsheetApp.getActive().getSheetByName(DEST);
if (sheet.getLastRow() === 0) {
sheet.appendRow(["id", "subject", "priority", "assignee", "updatedAt", "tag0"]);
}
const rows = items.map(function (t) {
return [
t.id,
t.subject || t.title || "",
(t.priority && t.priority.name) || t.priority || "",
(t.assignee && t.assignee.email) || "",
t.updated_at || t.updatedAt || "",
(t.tags && t.tags[0]) || "",
];
});
if (rows.length) {
sheet.getRange(sheet.getLastRow() + 1, 1, sheet.getLastRow() + rows.length, 6).setValues(rows);
}
Logger.log("Appended %s tickets", rows.length);
}- Line 8: Secret stored in Script Properties, not in code.
- Line 11: Performs the HTTP GET.
- Line 24: Converts response text to objects.
- Line 25: Supports two common list envelopes.
- Line 38: Safe nested access without optional chaining.
- Line 45: Batch-appends flattened rows.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: JSON parse
- 1API_TOKEN set in Project Settings → Script properties
- 2API_URL updated to a real host
- 3Tickets sheet exists
- 4Confirm JSON path for the list array
- 5Handle non-2xx with muteHttpExceptions
- 6Avoid logging full tokens
Frequently asked questions
The body is not JSON — HTML login pages or empty bodies are common. Log body.slice(0,200) on failure.
Modern V8 does, but this sample uses && guards for clarity and older runtimes.
JSON.stringify the payload and set contentType to application/json.
PropertiesService (script) or Secret Manager via service account — never hardcode.
Yes with JSON.stringify(obj, null, 2), but prefer flattened columns for analysis.
Join with commas, take [0], or explode to child rows on another sheet.
Number(cell) when APIs return numeric strings and you need SUM later.