Using REST APIs in Google Sheets with Apps Script

In this tutorial, we demonstrate how to call a REST API from Google Sheets using a custom =FUNC() formula. We use the free JSONPlaceholder API for demonstration.

🔧 Step-by-Step Guide

Step 1: Open the API Response

We use a public test API:
https://jsonplaceholder.typicode.com/users/2

{
  "id": 2,
  "name": "Ervin Howell",
  "username": "Antonette",
  ...
}

Step 2: Create the Sheet

Set up your Google Sheet with an Id column where you’ll pass user IDs.
We’ll extract username and name.

Step 3: Open Apps Script

Go to Extensions → Apps Script from your Sheet.

Step 4: Add Script

Replace the code with:

function FUNC(id) {
  var url = "https://jsonplaceholder.typicode.com/users/" + encodeURIComponent(id);
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());

  return [[data.username, data.name]];
}