API calls from Apps Script in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This sheet lists User IDs in column A. We want to fetch User Names from an API using Apps Script.

Cell	Value
A1	User ID
B1	User Name
A2	123
A3	456
A4	789

Formula Trace

fetchUserName(123)

Step 1: fetchUserName(123)

Step 2: UrlFetchApp.fetch('https://api.example.com/users/123')

Step 3: JSON.parse('{"id":123,"name":"Alice"}')

Step 4: return parsedResponse.name

Cell Reference Map

User ID

↓

123

↓

456

↓

789

Column A contains User IDs. The Apps Script function fetchUserName uses these IDs to get names from the API and fills column B.

Result

   A       B
1 User ID  User Name
2 123      Alice
3 456      Bob
4 789      Carol

After running the Apps Script, column B shows user names fetched from the API for each User ID in column A.

Sheet Trace Quiz - 3 Questions

What does the function fetchUserName(123) return?

A "123"

B An error

C "Alice"

D "User Name"

Key Result

Custom Apps Script functions can call APIs using UrlFetchApp.fetch and return parsed JSON data to cells.

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