Bird
Raised Fist0
Google Sheetsspreadsheet~8 mins

API calls from Apps Script in Google Sheets - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Dashboard Mode - API calls from Apps Script
Goal

See how to fetch live data from a web API into Google Sheets using Apps Script and analyze it in a dashboard.

Sample Data
CityTemperature (°C)Humidity (%)Condition
New York2260Clear
London1875Cloudy
Tokyo2570Rain
Sydney2065Clear
Mumbai3080Sunny
Dashboard Components
  • KPI Card: Average Temperature
    Formula: =AVERAGE(B2:B6)
    Shows the average temperature of all cities.
  • KPI Card: Highest Humidity
    Formula: =MAX(C2:C6)
    Shows the highest humidity value.
  • Table: Weather Data
    Shows live data fetched from API and placed in cells A2:D6.
  • Apps Script Function: fetchWeatherData()
    Calls a weather API, parses JSON, and writes city, temperature, humidity, and condition to the sheet.
Dashboard Layout
+----------------------+----------------------+
| Average Temperature   | Highest Humidity     |
|       (KPI)           |       (KPI)          |
+----------------------+----------------------+
|                                              |
|            Weather Data Table                  |
|                                              |
+----------------------------------------------+
Interactivity

User clicks a custom menu button in Google Sheets to run the fetchWeatherData() script. This updates the weather data table with fresh API data. The KPI cards automatically recalculate based on the updated data.

Self Check

If you add a filter to show only cities with temperature above 20°C, which components update?

  • The Weather Data Table will show only cities with temperature > 20°C.
  • The Average Temperature KPI recalculates based on filtered data.
  • The Highest Humidity KPI recalculates based on filtered data.
Key Result
Dashboard shows live weather data fetched via Apps Script API calls with KPIs for average temperature and highest humidity.

Practice

(1/5)
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