How to Pull API Data Into Google Sheets With IMPORTDATA and Apps Script

Quick answer: use =IMPORTDATA("your-csv-link") if the data is a public .csv or .tsv file. Use Apps Script if the API returns JSON or needs an API key. Free? Yes, both. Time: about 2 minutes for IMPORTDATA, about 15 minutes for Apps Script.

Google's own help page says IMPORTDATA imports data "in .csv (comma-separated value) or .tsv (tab-separated value) format." Most APIs return JSON, so for them you need a short script. Both methods are below, with copy-paste code.

What do you need before you start?

You need a Google account and a Google Sheet you can edit. For Method 2 you also need the API's address (the "endpoint") and, if the service requires one, an API key from the service's own dashboard.

Check two things in the API's documentation first:

• What format does it return? CSV or TSV means Method 1 works. JSON means use Method 2.
• Does it need a key or header? If yes, use Method 2.

How do you pull API data into Google Sheets with IMPORTDATA? (Method 1: easiest and free)

IMPORTDATA fetches a CSV or TSV file from a link and spreads it across cells. It takes one argument: the URL. Google's page documents no other arguments.

1. Open your Google Sheet.
2. Click an empty cell, such as A1.
3. Type =IMPORTDATA("https://example.com/data.csv") and press Enter. Replace the link with your own.
4. Wait a moment. The rows fill down and to the right of that cell.

Google's page gives this sample, which loads a public file from the US Census Bureau:

=IMPORTDATA("https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-popchg2010_2019.csv")

You can also keep the link in a cell. If the link is in A2, type =IMPORTDATA(A2). Google says the URL must include the protocol (for example https://) and must be in quotation marks or be a cell reference.

Leave room below and to the right of the formula cell. If other cells in that area already hold data, Sheets shows an error instead of overwriting them.

Google's page does not say how often IMPORTDATA refreshes, so do not rely on it for live prices or alerts. For a controlled schedule, use Method 2.

How do you import JSON or authenticated API data into Google Sheets? (Method 2: Apps Script)

Apps Script is the code editor built into Google Sheets. Its UrlFetchApp.fetch(url, params) call can send headers, choose a method such as GET or POST, and read the response. Google's reference lists these options, including headers, method, payload and muteHttpExceptions.

Step 1: Store your API key safely

1. Open your Google Sheet.
2. Click Extensions > Apps Script. Verify this step: the menu may differ.
3. At the left, click Project Settings.
4. Under Script Properties, click Add script property.
5. In Property, type API_KEY. In Value, paste your key.
6. Click Save script properties.

Google's properties guide says script properties are shared by all users of the script and are stored as strings. Anyone who can edit the script can read them, so share edit access carefully. Never paste a real key into code you share.

Step 2: Add the script

1. Go back to Editor (the code icon at the left).
2. Delete the sample code.
3. Paste the script below.
4. Replace the ENDPOINT address and the header name with the ones your API documents.
5. Click Save project.

// Pulls a JSON list from an API and writes it to a sheet called "API Data".
// Assumes the API returns an array of flat objects, e.g. [{"id":1,"name":"A"}]
const ENDPOINT = 'https://api.example.com/v1/items'; // replace with your API address

function importApiData() {
  const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
  if (!apiKey) {
    throw new Error('Add a script property named API_KEY first.');
  }

  const response = UrlFetchApp.fetch(ENDPOINT, {
    method: 'get',
    headers: { Authorization: 'Bearer ' + apiKey }, // use the header your API documents
    muteHttpExceptions: true
  });

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

  const items = JSON.parse(response.getContentText());
  if (!Array.isArray(items) || items.length === 0) {
    throw new Error('The API returned no rows.');
  }

  const headers = Object.keys(items[0]);
  const rows = items.map(function (item) {
    return headers.map(function (key) {
      const value = item[key];
      return (value !== null && typeof value === 'object') ? JSON.stringify(value) : value;
    });
  });

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('API Data') || ss.insertSheet('API Data');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

Step 3: Run it

1. In the function drop-down at the top, choose importApiData.
2. Click Run.
3. Google asks you to authorize the script. Review the permissions and approve them. Fetching a URL needs the external request permission.
4. Open the API Data sheet. Your rows should be there.

Setting muteHttpExceptions: true makes failed requests return a response instead of throwing, so the script can show you the status code and the start of the error message.

How do you refresh the data automatically?

Use a time-driven trigger. Google's installable triggers guide documents this pattern, which runs a function every six hours:

function createRefreshTrigger() {
  ScriptApp.newTrigger('importApiData')
    .timeBased()
    .everyHours(6)
    .create();
}

Run createRefreshTrigger once. Do not run it twice, or you will create two triggers.

You can also use the interface. Verify this step: the menu may differ. Open the Triggers page in the Apps Script left bar, click Add Trigger, pick importApiData, choose a time-driven event source, set the interval and click Save.

Google says time-driven triggers can run as often as every minute or as infrequently as once per month, and that Apps Script may vary the exact run time within the window. Installable triggers run under the account of the person who created them.

Can you use a custom function like =GETAPI() instead?

Yes, but with limits. Google's custom functions guide lists URL Fetch among the services custom functions can call. Two rules matter:

• A custom function call must return within 30 seconds. If not, the cell shows #ERROR!.
• Arguments must be deterministic. Functions such as NOW() and RAND() are not allowed, and a custom function cannot edit arbitrary cells.

Each custom function call also makes a separate call to the Apps Script server, so Google advises batching work. Custom functions suit a small lookup in one cell. For a full table with an API key, the trigger method above is more reliable.

Which method should you choose?

What's free and what costs money?

IMPORTDATA and Apps Script do not carry a separate charge inside Google Sheets. The API you call may. Check its pricing and rate limits.

Apps Script quotas, as of Google's page updated 3 September 2026, from the quotas page:

Google states that quotas can change. Check the page before building something that depends on them.

What can go wrong, and how do you fix it?

• IMPORTDATA shows an error or "Could not fetch URL". Confirm the link opens a .csv or .tsv file in your browser without logging in. IMPORTDATA only supports those formats.
• The script says "Add a script property named API_KEY first." The property name must match exactly, including capitals. Save script properties after adding it.
• The script throws "API returned 401" or "403". The key is wrong, expired, or sent in the wrong header. Check your API's documentation for the header name and format.
• "Exceeded maximum execution time" in a custom function. The API took more than 30 seconds. Switch to the trigger method.
• Data disappears or looks stale. The script clears the API Data sheet each run. Do not type your own notes in that tab. Use another tab and reference it.
• Quota errors. Exceeding a quota stops the script. Lower the refresh rate or fetch less often.

Is this allowed?

Pulling data through an official API with your own key is what the API is for. Follow the provider's terms and rate limits. Do not share your key in a public sheet or script, and do not fetch pages in ways their terms forbid. If a site offers no API or CSV, check its terms before collecting data from it.

For the next step after importing data, see how to send emails from Google Sheets automatically, how to get Google Sheets alerts on WhatsApp, or how to log every email into a sheet with Apps Script.

What's still unclear?

• IMPORTDATA refresh timing. Google's help page does not state how often it recalculates, and it does not document a limit on the number of IMPORTDATA formulas. Treat it as occasional, not live.
• Extra arguments. Some third-party guides list delimiter or locale arguments. Google's page documents only the URL, so this post uses only that.
• Quota changes. Google says quotas can change without notice, and the figures here are a snapshot.
• Weakest point. The Apps Script method assumes the API returns a flat list of objects. Nested or paged responses need extra code that depends on the specific API.

Last checked: 13 October 2026.