Can IMPORTDATA handle a URL that requires an API key?

No. IMPORTDATA sends a plain GET with no way to attach headers. Any endpoint requiring Authorization, X-Api-Key, or similar will return a 4xx and the cell will show an error. Use UrlFetchApp for any credentialed endpoint.

How do I force IMPORTDATA to refresh immediately?

You can't trigger it on demand. The workaround is to delete the formula and re-enter it, which resets the fetch. Some people append a dummy query parameter tied to a cell value (like =IMPORTDATA("https://example.com/data?t="&NOW())) and force-recalculate, but this is fragile. If you need reliable on-demand refresh, UrlFetchApp with a trigger or a manual Run is the correct tool.

UrlFetchApp is hitting a 6-minute script timeout on large responses — how do I fix it?

The Apps Script maximum execution time is 6 minutes for consumer accounts and 30 minutes for Workspace accounts. For large payloads, fetch only the data you need by using API filters or pagination rather than pulling the full dataset in one call. If pagination is unavoidable, split the work across multiple trigger runs using CacheService or PropertiesService to track your page cursor between executions.

Does UrlFetchApp count against any quota I should know about?

Yes. The free (consumer) tier allows 20,000 URL fetch calls per day; Google Workspace accounts get 100,000. Each call to UrlFetchApp.fetch() counts as one, regardless of response size. You can check current consumption at any time by running Session.getActiveUser() and reviewing the quota dashboard at script.google.com under your project's settings.

// Sheets · Apps Script

UrlFetchApp vs IMPORTDATA for external data in Google Sheets.

IMPORTDATA refreshes on its own schedule but can't send auth headers and fails silently. UrlFetchApp gives you full control over credentials, error handling, and timing — at the cost of writing a trigger. Here is how to pick one and wire it up correctly.

“I want to pull data from an external URL into Google Sheets and I'm not sure whether to use the built-in IMPORTDATA formula or write an Apps Script fetch.”

The script

copy · paste · trigger

fetchToSheet.gs

Apps Script

// Fetch JSON from an authenticated endpoint and write to Sheet1
// Triggered on a time-based schedule (e.g. every 30 minutes)
function fetchToSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var url = 'https://api.example.com/data';
  var options = {
    method: 'get',
    headers: {
      'Authorization': 'Bearer ' + PropertiesService.getScriptProperties().getProperty('API_KEY')
    },
    muteHttpExceptions: true
  };
  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() !== 200) {
    Logger.log('Fetch failed: ' + response.getResponseCode());
    return;
  }
  var data = JSON.parse(response.getContentText());
  sheet.getRange(1, 1).setValue(data.value);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

What IMPORTDATA actually does (and where it stops)

IMPORTDATA is a spreadsheet formula: you put =IMPORTDATA("https://example.com/file.csv") in a cell and Sheets fetches the URL, parses the response as comma- or tab-separated text, and tiles the result into adjacent cells. It refreshes automatically on a schedule Google controls, roughly every hour, though in practice the interval is inconsistent and you cannot force it on demand without deleting and re-entering the formula.

The hard limits: no request headers, so any endpoint requiring an Authorization or X-Api-Key header simply returns a 401 and the cell shows a loading error with no explanation. No error handling — a 4xx, a timeout, or a malformed response all produce the same opaque #ERROR! in the cell. And the data must be parseable as flat CSV or TSV; JSON responses are not supported at all.

For a truly public CSV that updates infrequently and where silent staleness is acceptable, IMPORTDATA is fine. The first time I used it against an internal endpoint I spent 20 minutes wondering why the formula kept erroring before remembering headers aren't a thing it can do.

When UrlFetchApp is the only real option

UrlFetchApp.fetch() runs inside an Apps Script function, which means it executes with the full HTTP client: arbitrary headers, POST bodies, query params, custom timeouts, and muteHttpExceptions to capture error responses rather than throwing. You read the response code explicitly, branch on failure, log details, and write whatever you want to the sheet.

The credential pattern worth memorizing: store your API key with PropertiesService.getScriptProperties().setProperty('API_KEY', 'your-key-here'), then read it back at runtime as shown in the snippet above. The key never appears in formula cells or sheet contents — it sits in the script's encrypted property store. Hardcoding it in the source is the one mistake that gets you when you share the spreadsheet.

UrlFetchApp also handles JSON natively: getContentText() gives you a string, JSON.parse() gives you an object, and you write individual fields to specific cells or use setValues() to write a 2D array in one shot. That last pattern — building a rows array and calling sheet.getRange(1,1,rows.length,cols).setValues(rows) — is dramatically faster than writing cell by cell when you have more than a few dozen values.

Wiring the trigger so it actually runs on a schedule

Without a trigger, your Apps Script function only runs when you click Run in the editor. To make it behave like IMPORTDATA's automatic refresh (but on your schedule), go to the Apps Script editor, open Triggers (the clock icon in the left sidebar), and add a time-driven trigger pointing at fetchToSheet. Every 30 minutes is a common choice for near-real-time data; every hour matches IMPORTDATA's natural cadence but with reliable timing.

One gotcha: triggers run as the user who created them, under that user's quota. The free tier allows 20,000 UrlFetchApp calls per day. At a 30-minute interval that is 48 calls per day per spreadsheet — well inside the limit unless you have many sheets running the same script. The quota resets at midnight Pacific time.

If the trigger fails (network error, script timeout, unhandled exception), Apps Script sends a failure email to the script owner after a few consecutive failures. This is your error alerting for free. muteHttpExceptions: true in the options object is important here: without it, a non-2xx response throws an exception that terminates the function before your logging or fallback logic runs.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Can IMPORTDATA handle a URL that requires an API key?: No. IMPORTDATA sends a plain GET with no way to attach headers. Any endpoint requiring Authorization, X-Api-Key, or similar will return a 4xx and the cell will show an error. Use UrlFetchApp for any credentialed endpoint.
How do I force IMPORTDATA to refresh immediately?: You can't trigger it on demand. The workaround is to delete the formula and re-enter it, which resets the fetch. Some people append a dummy query parameter tied to a cell value (like =IMPORTDATA("https://example.com/data?t="&NOW())) and force-recalculate, but this is fragile. If you need reliable on-demand refresh, UrlFetchApp with a trigger or a manual Run is the correct tool.
UrlFetchApp is hitting a 6-minute script timeout on large responses — how do I fix it?: The Apps Script maximum execution time is 6 minutes for consumer accounts and 30 minutes for Workspace accounts. For large payloads, fetch only the data you need by using API filters or pagination rather than pulling the full dataset in one call. If pagination is unavoidable, split the work across multiple trigger runs using CacheService or PropertiesService to track your page cursor between executions.
Does UrlFetchApp count against any quota I should know about?: Yes. The free (consumer) tier allows 20,000 URL fetch calls per day; Google Workspace accounts get 100,000. Each call to UrlFetchApp.fetch() counts as one, regardless of response size. You can check current consumption at any time by running Session.getActiveUser() and reviewing the quota dashboard at script.google.com under your project's settings.

// one good script a week