Working With JSON & Writing API Data Into Google Sheets

by

📘 Lesson 2

Blog Post #2 in the Apps Script + APIs + Gemini Series

One of the biggest milestones in learning Google Apps Script is moving from simply calling an API… to actually using its data inside Google Workspace.

In this lesson, you’ll learn:

  • How to parse JSON using JSON.parse()
  • How to extract fields from an API response
  • How to write API data into a Google Sheet
  • How to validate data safely
  • How to begin building real automations powered by APIs

By the end, you’ll know how to turn any JSON API into usable, structured data right inside Google Sheets.

Let’s dive in.

🎯 What You’ll Build Today

You will:

  1. Fetch a random joke from a public API
  2. Parse the JSON response
  3. Write the setup + punchline into Google Sheets
  4. Add simple error handling and data validation

This is a foundational workflow used across tens of thousands of real Apps Script projects — including AI integrations.

📍 Step 1 — Fetch the API Response

We’ll start with the same joke API:

https://official-joke-api.appspot.com/jokes/random

This returns JSON like:

{
  "type": "general",
  "setup": "Why did the chicken cross the road?",
  "punchline": "To get to the other side."
}

🧠 Step 2 — Parse JSON and Write to a Sheet

Copy and paste this complete function into your Apps Script editor:

/**
 * Lesson 2: Fetch a joke from an API and write it into Sheets.
 */
function writeJokeToSheet() {
  var url = 'https://official-joke-api.appspot.com/jokes/random';
  var options = {
    method: 'get',
    muteHttpExceptions: true
  };

  // 1. Fetch response
  var response = UrlFetchApp.fetch(url, options);
  var status = response.getResponseCode();

  // 2. Handle API errors
  if (status !== 200) {
    Logger.log('API Error. Status: ' + status);
    return;
  }

  // 3. Parse JSON
  var bodyText = response.getContentText();
  var data = JSON.parse(bodyText);

  // 4. Extract fields safely
  var setup = data.setup || 'N/A';
  var punchline = data.punchline || 'N/A';

  // 5. Get active sheet and write data
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([new Date(), setup, punchline]);

  Logger.log('Joke saved to sheet!');
}

🔍 Code Breakdown (Line by Line)

1. Fetch the API

var response = UrlFetchApp.fetch(url, options);

Returns the HTTP result including status code and text body.

2. Check the Status Code

if (status !== 200) { … }

This is essential — you never want your script to assume success.

3. Parse the JSON

var data = JSON.parse(bodyText);

Converts JSON string → JavaScript object.

4. Safe Access of Fields

data.setup || 'N/A'

If the API returns null or undefined, your sheet still gets a meaningful fallback value.

5. Write a Row to the Sheet

sheet.appendRow([new Date(), setup, punchline]);

Adds a timestamp, setup, and punchline in the next row.

✨ Bonus: Write Data Into a Google Doc Instead

Here’s an alternate way to use the same API — useful for reports, daily digests, newsletters, or AI workflows.

function writeJokeToDoc() {
  var url = 'https://official-joke-api.appspot.com/jokes/random';
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());

  var doc = DocumentApp.create('Daily Joke');
  var body = doc.getBody();

  body.appendParagraph('Joke of the Day').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph(data.setup);
  body.appendParagraph(data.punchline);

  Logger.log('Doc created: ' + doc.getUrl());
}

🧪 Exercises to Build Skill

Exercise 1 — Save More Fields

Modify the script to also save:

  • The joke type
  • A unique joke ID

These exist in the API response.

Exercise 2 — Pretty Print JSON in Logs

Log the entire API response clearly:

Logger.log(JSON.stringify(data, null, 2));

Exercise 3 — Insert a Header Row Automatically

Before writing any jokes, create headers:

sheet.getRange(1, 1, 1, 3).setValues([['Timestamp', 'Setup', 'Punchline']]);

Make your script check if the header exists first.

Exercise 4 — Add Error Recovery

If a value is missing, display:

  • “Setup missing — retrying…”

This sets you up for AI workflows where API responses vary.

💡 Pro Tips for Working With JSON in Apps Script

Tip 1 — Always Inspect API Structure

Different APIs nest data differently. Always log and examine the shape.

Tip 2 — Avoid Hard-Coding Sheet Names

Instead, use project-level configuration:

var SHEET_NAME = 'Jokes';

Tip 3 — Prepare for AI APIs Now

Gemini responses are also JSON-based.
Learning JSON parsing now makes Gemini integrations extremely easy later.