IMPORTRANGE vs IMPORTHTML vs IMPORTDATA
Google Sheets ships with a family of IMPORT functions that pull data from external sources — but three of them cover most jobs. IMPORTRANGE pulls from other Google Sheets. IMPORTHTML pulls tables and lists from web pages. IMPORTDATA pulls CSV or TSV files from URLs. This guide clarifies which one to reach for based on where your data actually lives.
⚡ Quick Answer
Use IMPORTRANGE to pull data from another Google Sheet. Use IMPORTHTML to extract a specific table or list from a web page. Use IMPORTDATA when the URL directly serves a CSV or TSV file. They cover completely different source types — none substitutes for another.
📋 What's in this comparison
IMPORTRANGE — Sheet to Sheet
=IMPORTRANGE(spreadsheet_url, range_string)
Pulls a range from another Google Sheet. Requires one-time authorization when first used — you'll see an "Allow access" popup. Once granted, the data flows live.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/[ID]/",
"Sales!A1:F1000")
See the IMPORTRANGE complete guide for the full syntax and gotchas.
When IMPORTRANGE fits
- Consolidating data from multiple team-owned Sheets into one central workbook
- Building a central "source of truth" Sheet that other Sheets pull from
- Distributing summary results to different teams' workbooks
- Any case where the data already lives in Google Sheets
IMPORTHTML — web tables and lists
=IMPORTHTML(url, query, index)
Pulls tables or lists from a public HTML page. The query argument is either "table" or "list". The index tells IMPORTHTML which one to pull (1 for the first table on the page, 2 for the second, etc.).
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_GDP",
"table", 2)
Wikipedia pages are the classic use case — many pages have well-structured tables of country data, historical stats, or reference information.
When IMPORTHTML fits
- Pulling reference data from Wikipedia and other structured public pages
- Tracking a specific table on a webpage that updates over time
- Aggregating list data (bulleted or numbered) from a public page
- Data that exists as an actual HTML
<table>element
When IMPORTHTML doesn't fit
- JavaScript-rendered pages (React, Vue, Angular apps)
- Pages behind login walls
- Sites with anti-bot protection (Cloudflare challenges, CAPTCHAs)
- Pages that use divs and CSS to fake tables instead of actual
<table>tags
IMPORTDATA — CSV/TSV URLs
=IMPORTDATA(url, [delimiter], [locale])
Fetches a CSV or TSV file at a URL and imports it directly into your Sheet. Commas or tabs become column separators; newlines become rows.
=IMPORTDATA("https://data.example.gov/dataset/sales.csv")
When IMPORTDATA fits
- Government open data portals that publish CSVs at stable URLs
- APIs that return CSV/TSV instead of JSON
- Static data feeds from partner systems
- Any URL that directly serves a delimited data file
When IMPORTDATA doesn't fit
- JSON APIs (there's no built-in IMPORTJSON; you'd need Apps Script)
- URLs behind authentication (no API key or token support)
- Data behind sign-in pages or paywalls
- Files larger than a few MB (performance degrades)
Side-by-side comparison
| Aspect | IMPORTRANGE | IMPORTHTML | IMPORTDATA |
|---|---|---|---|
| Source type | Google Sheet | Web page (HTML) | CSV/TSV URL |
| Authorization | One-time popup | None (public pages only) | None (public URLs only) |
| Handles JavaScript rendering | N/A | No | N/A |
| Handles authentication | Via Google account | No | No |
| Data structure | Cells → cells | HTML table/list → cells | Delimited text → cells |
| Refresh cadence | ~hourly + on-source-change | ~hourly | ~hourly |
| Best for | Sheet consolidation | Wikipedia, reference tables | Open data, CSV feeds |
| Speed | Fast (Google infra) | Depends on source site | Depends on source site |
| Row limit | ~10k-50k before slowdown | Depends on payload | Depends on payload |
| Failure mode | #REF! if access denied | Empty if table not found | #N/A if URL fails |
Decision framework — which to pick
Ask one question: where does the data live right now?
| Data source | Use |
|---|---|
| Another Google Sheet you have access to | IMPORTRANGE |
| A specific table on Wikipedia or a public website | IMPORTHTML with "table" |
| A bulleted or numbered list on a webpage | IMPORTHTML with "list" |
| A URL that directly returns .csv content | IMPORTDATA |
| A URL that returns .tsv content | IMPORTDATA |
| A government open data portal | Usually IMPORTDATA (they typically publish CSVs) |
| A JSON API | None — use Apps Script with UrlFetchApp |
| Data behind login | None — use Apps Script with auth headers |
| An Excel file at a URL | None natively — download and import manually, or use Apps Script |
Common gotchas across all three
1. Refresh behavior is fuzzy
None of these functions refresh in true real-time. They cache results and refetch approximately hourly. To force a refresh: edit the formula (add and remove a space, or change any argument), which triggers immediate re-fetching.
2. External sites can change without warning
Wikipedia rearranges table indexes. Government data portals change URLs. A page you've been pulling from for months can suddenly break because the third table became the fourth. Build in monitoring — check occasionally that the imports still return sensible data.
3. IMPORTHTML depends on the source's HTML structure
If Wikipedia adds a new sidebar table above your target, the index shifts and your import breaks. Not usually catastrophic, but requires attention. Consider linking to a specific archived version if you need stability.
4. JavaScript-rendered content is invisible
Modern web apps render most content via JavaScript AFTER the initial HTML loads. IMPORTHTML sees only the raw HTML — it can't execute JS. If a page's tables appear after client-side rendering, IMPORTHTML returns empty. Test by viewing "page source" in your browser — if the table isn't there, IMPORTHTML can't see it either.
5. Rate limits exist but aren't documented
Google throttles heavy IMPORT usage silently. Sheets with dozens of IMPORT formulas or very large payloads may see slower refresh cycles or intermittent failures. Consolidate where possible — one IMPORTRANGE pulling a wide range beats 10 IMPORTRANGEs pulling smaller ones.
IMPORTHTML and IMPORTDATA make outbound HTTP requests from Google's servers. The target site sees the request but doesn't know it's coming from a specific user — they see traffic from Google. IMPORTRANGE uses your Google credentials. Consider whether you're comfortable with these access patterns for sensitive URLs.
The verdict
🎯 Bottom line
These functions aren't alternatives — they're complements. Each targets a different source type. IMPORTRANGE for Sheets, IMPORTHTML for HTML pages, IMPORTDATA for CSV/TSV URLs. Pick based on where your data actually lives.
Test refresh behavior before relying on any of them. All three cache aggressively and can lag. If your dashboard depends on real-time updates, IMPORT functions may not be reliable enough — consider Apps Script, an API integration, or Connected Sheets instead.
When none of the three fits, reach for Apps Script. For JSON APIs, authenticated endpoints, or complex transformations, custom code in Apps Script gives you full control over fetching and parsing.
Frequently asked questions
What's the difference between IMPORTRANGE, IMPORTHTML, and IMPORTDATA?
IMPORTRANGE pulls a range from another Google Sheet. IMPORTHTML pulls tables or lists from a public web page. IMPORTDATA pulls CSV or TSV data from a URL. Each targets a different source type — Sheets, HTML, or delimited files — and none can substitute for the others.
Which IMPORT function is fastest?
IMPORTRANGE is typically fastest because it pulls from another Google Sheet (already in Google's infrastructure). IMPORTHTML and IMPORTDATA require external HTTP requests to third-party servers, so their speed depends on the external site's response time. All three can lag if the source is slow or the payload is large.
How often do IMPORT functions refresh in Google Sheets?
IMPORTRANGE refreshes automatically about every hour, or when the source Sheet changes. IMPORTHTML and IMPORTDATA cache the result and refresh approximately every hour. To force a refresh, edit the formula (add and remove a space) or change one of its arguments — that triggers immediate re-fetching.
Can IMPORTHTML scrape any website?
No. IMPORTHTML only pulls <table> and <ul>/<ol> elements from HTML. Sites that render content via JavaScript won't work — IMPORTHTML sees the initial HTML source before JS runs. Sites with anti-bot protection (Cloudflare, reCAPTCHA challenges) also often block it. Static pages with proper HTML tables usually work fine.
What's the difference between IMPORTDATA and IMPORTHTML?
IMPORTDATA reads CSV or TSV formatted data at a URL — treating every comma or tab as a column delimiter. IMPORTHTML reads HTML pages and extracts specific <table> or list elements. Use IMPORTDATA when the URL directly serves a CSV/TSV file. Use IMPORTHTML when the URL is a regular web page and you need a specific table or list from it.