API calls from Apps Script in Google Sheets - Step-by-Step Guide

Introduction

This feature helps you get data from other websites or services directly into your Google Sheets using Apps Script. It solves the problem of manually copying data by automating data fetching with simple scripts.

When you want to get weather updates automatically into your sheet every day.

When you need to pull stock prices from a finance website into your spreadsheet.

When you want to send data from your sheet to another service like a CRM.

When you want to update your sheet with the latest news headlines from an online source.

When you want to connect your sheet to a custom web service for your business.

Steps

Step 1: Open

- Google Sheets file

Your spreadsheet is ready for editing

Step 2: Click

- Extensions menu > Apps Script

The Apps Script editor opens in a new tab

Step 3: Delete any code in the script editor and type

- Apps Script editor

You have a blank script ready for your API call

💡 Start with a simple function named fetchData

Step 4: Type the following code

- Apps Script editor

Your script will fetch data from an API and put it in the sheet

💡 Replace 'https://api.example.com/data' with your actual API URL

Step 5: Click

- Save icon in Apps Script editor

Your script is saved

Step 6: Click

- Run menu > fetchData

The script runs and data from the API appears in cell A1 of your sheet

💡 The first time you run, you will need to authorize the script

Step 7: Return

- Google Sheets tab

You see the API data in your spreadsheet

Before vs After

Before

Cell A1 is empty in the Google Sheet

After

Cell A1 contains the text or data fetched from the API

Settings Reference

API URL

📍 Inside the Apps Script code in the UrlFetchApp.fetch() function

Specifies the web address where the data is fetched from

Default: None

Authorization

📍 Apps Script authorization prompt when running the script

Grants permission for the script to access external services

Default: Deny

Target cell

📍 Apps Script code in sheet.getRange(row, column)

Defines where the fetched data will be placed in the sheet

Default: A1

Common Mistakes

Not replacing the example API URL with a real one

The script tries to fetch from a non-existent address and fails

Use a valid API URL that returns data you want

Running the script without authorizing permissions

The script cannot access external services without permission

Authorize the script when prompted to allow it to run

Trying to put complex JSON data directly into one cell

The cell will show raw JSON text which is hard to read

Parse the JSON in the script and write values into multiple cells

Summary

Apps Script lets you fetch data from the web directly into Google Sheets.

You write a simple script using UrlFetchApp to call an API and put data in cells.

Remember to use a real API URL and authorize the script to run.

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