API calls from Apps Script in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this Apps Script API call?

Consider this Apps Script code snippet that calls a public API and writes the result to a Google Sheet cell:

function fetchJoke() {
  var response = UrlFetchApp.fetch('https://official-joke-api.appspot.com/random_joke');
  var joke = JSON.parse(response.getContentText());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue(joke.setup + ' - ' + joke.punchline);
}

What will be the content of cell A1 after running fetchJoke()?

Google Sheets

function fetchJoke() {
  var response = UrlFetchApp.fetch('https://official-joke-api.appspot.com/random_joke');
  var joke = JSON.parse(response.getContentText());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue(joke.setup + ' - ' + joke.punchline);
}

A Cell A1 remains empty because setValue is missing

B The raw JSON string from the API response in cell A1

C A random joke's setup and punchline combined as a single string in cell A1

D An error message because UrlFetchApp is not defined

💡 Hint

Think about what JSON.parse does and how setValue works.

The code fetches a random joke JSON, parses it, then writes the combined setup and punchline as a string into cell A1.

❓ Function Choice

intermediate

Which Apps Script function correctly retrieves JSON data from an API?

You want to get JSON data from a public API in Google Apps Script. Which function call correctly fetches and parses the JSON?

A var response = UrlFetchApp.fetch(url); var data = JSON.parse(response.getContentText());

B var data = UrlFetchApp.getJSON(url);

C var response = UrlFetchApp.get(url); var data = response.json();

D var data = fetch(url).json();

💡 Hint

Check the official Apps Script methods for fetching URLs and parsing JSON.

Only UrlFetchApp.fetch returns a response object with getContentText(), which can be parsed by JSON.parse.

🎯 Scenario

advanced

You want to update multiple cells with API data efficiently

You have an API that returns an array of user names. You want to write these names into cells A1:A5 in your Google Sheet using Apps Script. Which approach is best for performance?

A Write names one by one using sheet.appendRow(name)

B Call UrlFetchApp.fetch multiple times, once per name

C Call sheet.getRange('A1').setValue(name) inside a loop for each name

D Build a 2D array of names and call sheet.getRange('A1:A5').setValues(array) once

💡 Hint

Think about minimizing calls to the spreadsheet service.

Calling setValues once with a 2D array is much faster than multiple setValue calls inside a loop.

📊 Formula Result

advanced

What error occurs with this Apps Script API call?

What error will this Apps Script code produce?

function getData() {
  var response = UrlFetchApp.fetch('https://api.example.com/data');
  var data = JSON.parse(response);
  Logger.log(data);
}

Google Sheets

function getData() {
  var response = UrlFetchApp.fetch('https://api.example.com/data');
  var data = JSON.parse(response);
  Logger.log(data);
}

A TypeError because JSON.parse expects a string but gets an HTTPResponse object

B SyntaxError due to invalid JSON format

C No error, logs parsed JSON data

D ReferenceError because UrlFetchApp is undefined

💡 Hint

Check what UrlFetchApp.fetch returns and what JSON.parse expects.

UrlFetchApp.fetch returns an HTTPResponse object, not a string. JSON.parse needs a string, so passing the object causes a TypeError.

❓ data_analysis

expert

How many items are in the array returned by this API call?

This Apps Script code fetches data from an API and parses it:

function countItems() {
  var response = UrlFetchApp.fetch('https://jsonplaceholder.typicode.com/posts');
  var posts = JSON.parse(response.getContentText());
  return posts.length;
}

What is the value returned by countItems()?

Google Sheets

function countItems() {
  var response = UrlFetchApp.fetch('https://jsonplaceholder.typicode.com/posts');
  var posts = JSON.parse(response.getContentText());
  return posts.length;
}

A 200

B 100

C 50

D Undefined because posts is not an array

💡 Hint

Check the API documentation or test the URL in a browser.

The API returns an array of 100 post objects, so posts.length is 100.