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.