API calls from Apps Script in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new HTTP GET request using UrlFetchApp.

Google Sheets

var response = UrlFetchApp.[1]('https://api.example.com/data');

Drag options to blanks, or click blank then click option'

Arequest

Bget

Cfetch

Dcall

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'get' or 'request' which are not valid methods in UrlFetchApp.

Trying to call a method that does not exist.

The correct method to make an HTTP request in Apps Script is fetch.

2fill in blank

medium

Complete the code to parse the JSON response text into an object.

Google Sheets

var data = JSON.[1](response.getContentText());

Drag options to blanks, or click blank then click option'

Aparse

Bstringify

Cconvert

DtoObject

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'stringify' which converts objects to JSON text.

Using non-existent methods like 'convert' or 'toObject'.

Use JSON.parse to convert JSON text into a JavaScript object.

3fill in blank

hard

Fix the error in the code to set the HTTP method to POST in the options object.

Google Sheets

var options = {method: '[1]', contentType: 'application/json'};

Drag options to blanks, or click blank then click option'

AGet

BPOST

Cpost

DPut

Attempts:

3 left

💡 Hint

Common Mistakes

Using lowercase 'post' which may not work.

Using wrong method names like 'Get' or 'Put'.

The HTTP method must be uppercase 'POST' to be recognized correctly.

4fill in blank

hard

Fill both blanks to create a POST request with JSON payload.

Google Sheets

var options = {method: '[1]', payload: JSON.[2](data)};

Drag options to blanks, or click blank then click option'

APOST

Bparse

Cstringify

DGET

Attempts:

3 left

💡 Hint

Common Mistakes

Using GET method when sending data.

Using JSON.parse instead of stringify for payload.

Use POST as method and JSON.stringify to convert data to JSON text for payload.

5fill in blank

hard

Fill all three blanks to extract a value from the JSON response and set it in a sheet cell.

Google Sheets

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('[1]');
sheet.getRange([2], [3]).setValue(data.result);

Drag options to blanks, or click blank then click option'

AData

B1

C2

DResults

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong sheet names.

Using zero or invalid row/column numbers.

Use the sheet named 'Data', set value at row 1, column 2 to the result from data.

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