Connect REST APIs to Google Sheets Using Apps Script (Full Step-by-Step Tutorial)

Introduction

Ever wished you could automatically pull data from your favorite tool — like Stripe, Notion, Airtable, or even your company’s internal API — straight into Google Sheets?

You can.
And you don’t need fancy connectors or expensive automation platforms to do it.

With Google Apps Script, you can connect to any REST API, fetch live data, and refresh it automatically — all from inside your spreadsheet. It’s simple, free, and surprisingly powerful once you know the basics.

In this hands-on tutorial, we’ll walk through everything you need to connect any REST API to Google Sheets. You’ll learn how to:

• Make API calls with Apps Script
• Handle authentication (API keys or tokens)
• Parse and display JSON data
• Schedule automatic updates
• Avoid common pitfalls

By the end, you’ll have your own API-driven spreadsheet that updates itself — perfect for dashboards, reports, or lightweight data integrations.

---

1. What Is a REST API (and Why Should You Care)?

A REST API (short for Representational State Transfer) is a simple way for software to exchange data using HTTP requests — the same technology your browser uses to load web pages.

When you send a request to an API endpoint, it sends structured data back — usually in JSON format.
For example, here’s what a sample response might look like:

[
  {"name": "Alice", "age": 29},
  {"name": "Bob", "age": 34}
]

That’s just text data, and with Google Apps Script, you can grab it, transform it, and place it directly into a spreadsheet.

Once you connect an API, Google Sheets becomes much more than a calculator — it turns into a live data platform.

---

2. What Is Google Apps Script?

Google Apps Script (GAS) is a cloud-based scripting language built into Google Workspace.
It’s essentially JavaScript with special functions for Google tools like Sheets, Gmail, and Drive.

With Apps Script, you can:

• Fetch and process data from APIs
• Add custom menus or buttons
• Schedule automatic refreshes
• Send automated reports

Everything runs on Google’s servers, so you don’t need to install or host anything.
It’s the easiest way to connect Google Sheets to the outside world.

---

3. Setting Up Your Sheet and Script

Let’s get started with a simple example.

1. Open a new or existing Google Sheet.
2. Go to Extensions → Apps Script.
3. Delete any code inside and paste this:

function getApiData() {
  const url = 'https://api.publicapis.org/entries';
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());
  Logger.log(data);
}

4. Click Run ▶️ and authorize the script when prompted.
5. Open View → Logs to see the JSON output.

Congratulations — you just made your first API call directly from Google Sheets!

---

4. Displaying API Data in Google Sheets

Now let’s show that data in the sheet.

The example API returns a list of public APIs, which is a perfect dataset to display.
Replace your previous code with this:

function writeApiDataToSheet() {
  const url = 'https://api.publicapis.org/entries';
  const response = UrlFetchApp.fetch(url);
  const json = JSON.parse(response.getContentText());
  const entries = json.entries;

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();

  const headers = ['API', 'Description', 'Category'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  const rows = entries.map(e => [e.API, e.Description, e.Category]);
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

Hit Run, and your Google Sheet will instantly fill with live API data — name, description, and category neatly aligned in columns.

Once you’ve done this once, you’ll realize how flexible it is. You can connect weather data, CRM data, e-commerce data, or internal APIs the same way.

---

5. Working with API Keys or Tokens

Most APIs require authentication for security. They’ll give you a unique API key or Bearer token that must be included in your request.

Here’s how you add headers in Apps Script:

function getPrivateApiData() {
  const token = 'YOUR_API_KEY'; // Replace with your key
  const url = 'https://api.example.com/data';
  const options = {
    headers: { 'Authorization': `Bearer ${token}` }
  };

  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());
  Logger.log(data);
}

Pro Tip: Never hard-code sensitive data.
Store it securely using PropertiesService:

const token = PropertiesService.getScriptProperties().getProperty('API_TOKEN');

To set it:

• In Apps Script, go to Project Settings → Script Properties → Add Property.
• Add API_TOKEN and paste your key there.

Now your keys are safe even if someone copies your sheet.

---

6. Writing JSON Data Dynamically

APIs often return complex data structures.
Here’s a reusable version that adapts automatically to the data it receives:

function writeDynamicData() {
  const url = 'https://jsonplaceholder.typicode.com/users';
  const response = UrlFetchApp.fetch(url);
  const json = JSON.parse(response.getContentText());

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();

  const headers = Object.keys(json[0]);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  const rows = json.map(obj => Object.values(obj));
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

Whatever JSON the API returns, this function automatically converts it into a readable table.
It’s perfect for quickly exploring or debugging API responses.

---

7. Adding a Custom Menu in Google Sheets

You can make it even easier for users by adding a custom menu button that runs your script.

Add this snippet to your code:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('API Tools')
    .addItem('Refresh Data', 'writeApiDataToSheet')
    .addToUi();
}

Now, every time you open the sheet, you’ll see a new menu: API Tools → Refresh Data.
Click it, and your data refreshes instantly — no need to open the script editor.

---

8. Scheduling Automatic Updates

Manual updates are fine for testing, but automation is where the magic happens.

Apps Script lets you create time-based triggers so your API calls run automatically.

To set one up:

1. In the Script Editor, click the clock icon (Triggers).
2. Click + Add Trigger.
3. Choose the function you want to run (e.g., writeApiDataToSheet).
4. Under “Select type of time-based trigger,” choose your frequency — hourly, daily, etc.
5. Save.

Your Google Sheet will now update automatically — perfect for dashboards or reports that always need the latest data.

---

9. Sending Data to an API (POST Requests)

Sometimes you’ll want to send data to an API — for example, submitting form data or updating a database.

Here’s how to make a POST request:

function sendData() {
  const url = 'https://httpbin.org/post';
  const payload = { name: 'Alex', role: 'Engineer' };

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload)
  };

  const response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

This example just echoes your data, but the same approach works for CRMs, forms, or project management APIs.

---

10. Debugging Common Problems

APIs can be tricky at first. Here are a few quick solutions to common issues:

Error	Cause	Fix
401 Unauthorized	Missing or invalid key	Double-check your token and headers
403 Forbidden	Permission denied	Confirm your account’s API access level
429 Too Many Requests	Rate limit hit	Add Utilities.sleep(1000) between calls
500 Server Error	API issue	Retry after a few seconds
Empty Data	Wrong JSON path	Use Logger.log() to inspect response

To debug efficiently:

Logger.log(response.getResponseCode());
Logger.log(response.getContentText());

You’ll quickly see whether the issue is on your end or the API’s.

---

11. Example: Fetching Data from GitHub’s API

Here’s a practical example that lists a user’s public repositories from GitHub.

function getGitHubRepos() {
  const username = 'octocat';
  const url = `https://api.github.com/users/${username}/repos`;
  const response = UrlFetchApp.fetch(url);
  const repos = JSON.parse(response.getContentText());

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();

  const headers = ['Name', 'Stars', 'Language', 'URL'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  const rows = repos.map(r => [r.name, r.stargazers_count, r.language, r.html_url]);
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

You’ll instantly get a table of repositories, complete with star counts and links.
Swap the URL, and you can adapt this to almost any API you work with.

---

12. Tips for Reliable Automation

A few best practices to make your setup solid:

• Store secrets safely with PropertiesService.
• Paginate large datasets if your API returns limited results.
• Cache results or throttle requests to avoid rate limits.
• Use clear variable names for maintainability.
• Add error handling: try { // your code } catch (e) { Logger.log('Error: ' + e.message); }
• Test manually first before enabling triggers.
• Document your setup so teammates can use it too.

These small details make your integration stable for months (or years) without touching it.

---

13. Bonus: Turn Google Sheets into an API

Want to go one step further? You can make your own sheet act like an API.

function doGet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

Then go to Deploy → New Deployment → Web app → “Anyone with the link”.
You’ll get a URL that returns your sheet data in JSON format — perfect for dashboards or lightweight apps.

---

14. Conclusion

You did it!
You’ve learned how to connect REST APIs to Google Sheets using Google Apps Script — including authentication, automation, and data transformation.

You can now build live dashboards, automate reports, or integrate company data sources — all using a single spreadsheet.

But if you’d rather skip the scripting and go straight to results, there’s an even faster way.

---

💡 Try API Parser — The No-Code Way to Connect APIs to Google Sheets

If writing code isn’t your thing — or you just want to save time — check out API Parser.

API Parser is a Google Sheets add-on that lets you connect any REST API in just a few clicks.
No coding, no scripts — simply paste your API URL, set your parameters, and load live data directly into your sheet.

With API Parser, you can:

• 🔗 Connect any public or private API instantly
• 🕒 Schedule automatic data refreshes (hourly, daily, or custom)
• 📊 Turn complex JSON into clean tables
• 🧩 Combine multiple APIs in one place
• 🔒 Keep your credentials safe and secure

It’s the fastest, most reliable way to bring API data into Google Sheets — perfect for marketers, analysts, and developers alike.

👉 Try it now at APIParser.com and see how effortless connecting APIs to Google Sheets can be.