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.

Practice

1. What does the UrlFetchApp.fetch() function do in Google Apps Script?

easy

A. It deletes data from a sheet.

B. It creates a new Google Sheet.

C. It formats cells in a spreadsheet.

D. It sends a request to an external API and gets a response.

Solution

1. Step 1: Understand the purpose of UrlFetchApp.fetch()

   This function is used to send HTTP requests to external services or APIs.

2. Step 2: Identify what it returns

   It returns the response from the API, which can be text, JSON, or other data formats.

3. Final Answer:

   It sends a request to an external API and gets a response. -> Option D

4. Quick Check:

   UrlFetchApp.fetch() = Sends API request [OK]

Hint: Remember: fetch means get data from outside [OK]

Common Mistakes:

• Thinking it modifies spreadsheet data directly
• Confusing it with sheet creation functions
• Assuming it formats cells

2. Which of the following is the correct way to parse a JSON response from an API call in Apps Script?

easy

A. var data = response.toString();

B. var data = response.getJson();

C. var data = JSON.parse(response.getContentText());

D. var data = JSON.stringify(response);

Solution

1. Step 1: Identify how to get text from response

   Use response.getContentText() to get the response as a string.

2. Step 2: Parse JSON string to object

   Use JSON.parse() to convert the string into a usable JavaScript object.

3. Final Answer:

   var data = JSON.parse(response.getContentText()); -> Option C

4. Quick Check:

   Parse JSON with JSON.parse(getContentText()) [OK]

Hint: Use JSON.parse on getContentText() result [OK]

Common Mistakes:

• Using JSON.stringify instead of JSON.parse
• Trying to call getJson() which doesn't exist
• Not converting response to text first

3. Given this Apps Script code snippet, what will be logged?

var response = UrlFetchApp.fetch('https://api.example.com/data');
var json = JSON.parse(response.getContentText());
Logger.log(json.status);

Assuming the API returns {"status":"success","count":10}.

medium

A. 10

B. success

C. {"status":"success","count":10}

D. undefined

Solution

1. Step 1: Parse the JSON response

   The response text is parsed into an object: {status: "success", count: 10}.

2. Step 2: Access the 'status' property

   Logging json.status outputs the string "success".

3. Final Answer:

   success -> Option B

4. Quick Check:

   json.status = "success" [OK]

Hint: Access JSON properties after parsing response [OK]

Common Mistakes:

• Logging the whole JSON string instead of property
• Confusing count with status
• Not parsing JSON before accessing properties

4. What is wrong with this Apps Script code snippet?

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

medium

A. You must parse response.getContentText(), not response directly.

B. Logger.log cannot print JSON data.

C. UrlFetchApp.fetch() requires a second parameter.

D. JSON.parse cannot be used in Apps Script.

Solution

1. Step 1: Check what is passed to JSON.parse()

   The code passes the whole response object, but JSON.parse expects a string.

2. Step 2: Correct usage

   Use response.getContentText() to get the response as a string before parsing.

3. Final Answer:

   You must parse response.getContentText(), not response directly. -> Option A

4. Quick Check:

   Parse string, not response object [OK]

Hint: Always parse response.getContentText() [OK]

Common Mistakes:

• Passing response object directly to JSON.parse
• Thinking Logger.log can't print objects
• Assuming fetch needs extra parameters always

5. You want to fetch weather data from an API and write the temperature into cell A1 of your Google Sheet. Which Apps Script code correctly does this?

hard

A. var response = UrlFetchApp.fetch('https://api.weather.com/temp');
var data = JSON.parse(response.getContentText());
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(data.temperature);

B. var response = UrlFetchApp.fetch('https://api.weather.com/temp');
var data = response.getContentText();
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(data.temperature);

C. var response = UrlFetchApp.fetch('https://api.weather.com/temp');
var data = JSON.stringify(response);
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(data.temperature);

D. var response = UrlFetchApp.fetch('https://api.weather.com/temp');
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(response);

Solution

1. Step 1: Fetch and parse the API response

   Use UrlFetchApp.fetch() to get the response, then parse it with JSON.parse(response.getContentText()) to get the data object.

2. Step 2: Write the temperature value to cell A1

   Use SpreadsheetApp.getActiveSheet().getRange('A1').setValue(data.temperature) to set the cell value.

3. Final Answer:

   var response = UrlFetchApp.fetch('https://api.weather.com/temp'); var data = JSON.parse(response.getContentText()); SpreadsheetApp.getActiveSheet().getRange('A1').setValue(data.temperature); -> Option A

4. Quick Check:

   Fetch, parse JSON, write value to sheet [OK]

Hint: Parse JSON then set cell value with setValue() [OK]

Common Mistakes:

• Not parsing JSON before accessing temperature
• Trying to set raw response object to cell
• Using JSON.stringify instead of parse