How to Import API Data into Google Sheets with Apps Script

Quick answer

UrlFetchApp.fetch(url) retrieves data from any API endpoint, JSON.parse(response.getContentText()) turns the response into a usable object, and sheet.getRange(...).setValues(...) writes it into the sheet. No add-on, no third-party connector — the whole pipeline is built into Apps Script.

Below is a complete example pulling data from a public API and writing it as rows, plus a refresh trigger so the sheet stays current on its own.

Step 1 — Write the fetch-and-parse function

function importApiData() {
  const url = 'https://api.example.com/products'; // replace with your real endpoint
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());

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

  // Assumes data is an array of objects with the same keys, e.g.
  // [{ "name": "Widget", "price": 9.99 }, { "name": "Gadget", "price": 19.99 }]
  const headers = Object.keys(data[0]);
  sheet.appendRow(headers);

  const rows = data.map(item => headers.map(h => item[h]));
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

What each piece actually does, per the official Apps Script reference:

• UrlFetchApp.fetch(url) — the basic form takes just a URL; there's also fetch(url, params) for an advanced form that accepts headers, method (GET/POST/etc.), and a payload as a second argument
• UrlFetchApp.fetchAll(requests) — if you need data from several endpoints, this fires them in parallel and returns an array of responses, instead of calling fetch() in a loop one at a time
• response.getContentText() — returns the raw response body as a string; JSON.parse() on that string is what turns it into an object/array you can actually work with

Step 2 — Handle an API that needs an API key

Most real APIs aren't fully public — they expect a key. Pass it as a header, not baked into the URL where it can end up in logs (see what an API key actually is for why that matters):

const response = UrlFetchApp.fetch(url, {
  headers: {
    'Authorization': 'Bearer ' + apiKey
  }
});

Store the key itself in Project Settings → Script Properties rather than hardcoding it in the function — that keeps it out of the script body if you ever share or export the project.

Step 3 — Test manually, then automate with a trigger

Run importApiData once manually from the Apps Script editor (▶ Run) to confirm the sheet populates correctly and to grant the authorization Apps Script asks for the first time. Once that works, add a time-driven trigger (clock icon → Add Trigger → pick importApiData → Time-driven → hourly or daily, depending on how fresh the data needs to be) so the sheet refreshes on its own — the same trigger mechanism used for scheduling an automatic mail merge from Google Sheets, if you're chaining this into an email step afterward.

Quotas that matter for this specific use case

Limit	Consumer account	Google Workspace
URL Fetch calls per day	20,000	100,000
Max response size / POST payload	50 MB per call	50 MB per call
Max URL length	2 KB	2 KB
Max headers	100 per call, 8 KB total	100 per call, 8 KB total

For a sheet that refreshes hourly, 20,000/day is effectively no constraint at all — the practical limit is almost always the third-party API's own rate limit, not Apps Script's.

Common mistakes

Not checking the response code before parsing. UrlFetchApp.fetch() doesn't throw on a 4xx/5xx response by default — it still returns an HTTPResponse object, and JSON.parse() on an error page's HTML will throw a confusing "Unexpected token" error that has nothing to do with your actual problem. Check response.getResponseCode() before parsing:

if (response.getResponseCode() !== 200) {
  throw new Error('API returned ' + response.getResponseCode() + ': ' + response.getContentText());
}

Assuming the API response is a flat array at the top level. Many APIs wrap the actual data one or two levels deep — { "results": [...] } or { "data": { "items": [...] } } — rather than returning a bare array. If data[0] throws or Object.keys(data[0]) looks wrong, log JSON.stringify(data) first and check the real shape before writing the mapping logic.

Hitting the third-party API's own rate limit, not Apps Script's. A trigger set to run every minute against an API that allows 60 requests/hour will fail long before Apps Script's own 20,000/day quota becomes relevant. Match the trigger frequency to what the API's own documentation actually allows.

Writing one row at a time instead of batching with setValues(). Calling sheet.appendRow() inside a loop for every item is dramatically slower than building the full 2D array first and writing it in one setValues() call — for a few hundred rows the difference is the gap between a script that finishes instantly and one that times out.

When this outgrows Apps Script

This pattern is a good fit for pulling data into a sheet on a schedule. It stops being the right tool once you need the data to trigger downstream actions (send an alert when a value crosses a threshold, push it into a CRM, branch logic based on multiple conditions) — at that point a dedicated automation tool with built-in conditional logic and native app connectors, like n8n, Zapier, or Make, replaces a growing pile of custom UrlFetchApp calls with prebuilt nodes. See the n8n vs Zapier vs Make comparison for how they differ.

FAQ

Can I POST data to an API instead of just fetching it?

Yes — pass method: 'post' and a payload in the params object: UrlFetchApp.fetch(url, { method: 'post', payload: JSON.stringify(data), contentType: 'application/json' }). The rest of the flow (checking the response code, parsing the result) works the same way.

Why does my script work when I run it manually but fail on the trigger?

The most common cause is an authorization scope that was granted during the manual run but that the trigger context doesn't have yet — re-run manually once after adding a new API call to a script that already has a trigger, so Apps Script can prompt for the additional scope (script.external_request for UrlFetchApp).

Can I import data from an API that requires OAuth instead of a simple API key?

Yes, but it's more involved — you'd typically use the OAuth2 library for Apps Script (a well-known open-source add-on library) rather than hand-rolling the token exchange yourself, since OAuth flows involve steps (authorization redirects, token refresh) that a plain API key doesn't need.

How do I import data that's paginated across multiple API calls?

Loop, following whatever pagination method the API uses (a next_page URL in the response, or an incrementing page parameter), calling UrlFetchApp.fetch() each time and appending results, until the API signals there's no more data. There's no built-in Apps Script helper for this — it's manual loop logic based on that specific API's pagination contract.

---

Last fact-checked: August 18, 2026. Method signatures verified against the official Apps Script URL Fetch Service reference and quotas documentation.