How to Get Data From a Website in Google Sheets

How to Get Data From a Website in Google Sheets

Paste =IMPORTHTML("https://example.com", "table", 1) into an empty cell. That pulls the first HTML table on the page straight into your sheet, spilling across as many rows and columns as it needs.

If nothing appears, the data is probably not inside a table element. Google Sheets has four native import functions, and picking the right one is most of the job.

All four take a URL as their first argument and refresh roughly hourly on their own. None of them run JavaScript, which is the single most common reason an import comes back empty.

How to Import Data From a Website Into Google Sheets With IMPORTHTML and IMPORTXML

For a plain HTML table or list, IMPORTHTML is the simplest option. Point it at a URL and pick the table number.

For a single value buried in the page, you need IMPORTXML and an XPath query.

For a hosted CSV, IMPORTDATA grabs the whole file.

For an RSS or Atom feed, IMPORTFEED is purpose built and needs no XPath at all.

These work, but they are brittle. Rename a CSS class and your XPath returns #N/A. Writing a path like //div[@id='content']/div[2]/ul/li[1] means digging through page source by hand. And when a table imports with merged headers, junk columns, and stray symbols, you still have to clean it.

How to Scrape a Website With Google Sheets

Scraping with Sheets is five steps, and the first one saves you the most time. Do it before you write a formula.

1. Confirm the value is in the page source. Open the page and press Ctrl+U, then search for a value you want with Ctrl+F. If it is not there, no formula will find it, and you can stop now.
2. Put the URL in a cell. Paste the address into A2 instead of hardcoding it. This costs nothing now and lets you fill the same formula down a whole column of URLs later.
3. Try IMPORTHTML first. Enter =IMPORTHTML(A2, "table", 1) and raise the index until the right table appears.
4. Fall back to IMPORTXML. If the data is not in a table or list, test the connection with a deliberately broad query, then narrow it.
5. Clean the result. Imported tables arrive with merged headers, footnote symbols, and currency strings. Wrap the import in QUERY, or describe the cleanup in plain English.

Step 4 is worth dwelling on. If //h1 returns the page heading, your fetch is working and any later failure is your selector, not the site. If it returns #N/A, the problem is the connection or the rendering, and no amount of XPath tuning will fix it.

How to Find the Right Table Number and XPath for Your Import

The third argument in IMPORTHTML is an index. It counts only elements of the type you asked for, starting at 1, in the order they appear in the page source. So "table", 3 means the third table element on the page, which is not necessarily the third table you can see. Layout tables and hidden tables still count.

The practical approach is to start at 1 and increment until the right data appears. Lists are counted separately, so "list", 1 is the first ul or ol element regardless of how many tables precede it.

For IMPORTXML, you do not have to write XPath by hand. In Chrome, right click the element you want, choose Inspect, then right click the highlighted line in the elements panel and choose Copy and then Copy XPath.

Paste that into your formula, but expect to simplify it. A copied XPath is absolute and extremely fragile, since it breaks the moment the page structure shifts by one element. A query targeting a class or id survives redesigns far better.

Putting the URL in a cell rather than hardcoding it is worth doing from the start. You can then fill the formula down a column of URLs and pull the same element from every page.

How to Import Only the Columns You Need With QUERY

An imported table rarely has the shape you want. Rather than importing everything and deleting columns by hand, wrap the import in QUERY and select the columns in the formula.

Because the import is not a real sheet range, you refer to columns as Col1 and Col2 rather than A and B. The trailing 1 tells QUERY that the first imported row is a header.

One caveat matters here. QUERY infers a single data type per column, and imported columns are almost always mixed, since a stray footnote symbol turns a numeric column into text. QUERY then silently blanks the minority type. Our QUERY function help covers that failure in detail.

How to Pull Data From Multiple URLs at Once

Once the URL lives in a cell, a column of URLs is a column of imports. Put your addresses in A2 down, then fill this across the rows.

The blank guard matters more than it looks. Without it, every empty row below your data fires its own live request, and those count against the same budget as the real ones.

ARRAYFORMULA does not help you here, because the IMPORT functions do not vectorize over a range of URLs. One formula means one request. Google's own guidance is to keep external import formulas under roughly 50 per spreadsheet, and past that you will start seeing this: "Loading data may take a while because of the large number of requests. Try to reduce the amount of IMPORTHTML, IMPORTDATA, IMPORTFEED, or IMPORTXML functions across spreadsheets you've created." The fix is to consolidate into fewer, larger imports, or to convert finished results to static values with Paste special and Values only.

Why Your Import Returns #N/A or an Empty Result

This is the failure that stops most people, and the cause is usually not a mistake in your formula.

The page renders with JavaScript. The IMPORT functions fetch the raw HTML the server returns and parse it. They do not run a browser and they do not execute JavaScript. On a site built with React, Vue, or Angular, the data you see in your browser is injected after page load, so it is simply not in the HTML that Sheets receives. You can confirm this yourself: open the page, press Ctrl+U to view source, and search for the value. If it is not in the source, no XPath will ever find it.

No formula fixes this, so it needs its own answer. The four workable routes are set out in the JavaScript section below.

The site blocks Google's servers. Requests come from Google datacenter IP ranges, which many sites rate limit or block outright. Extracting Zillow data walks through a concrete example of this.

Your XPath no longer matches. Site redesigns silently break class-based queries. Test with a very broad query such as //h1 first. If that returns something, your connection is fine and the problem is your selector.

The result is loading indefinitely. Large imports can take a while. If it never resolves, delete the formula, wait a moment, and re-enter it.

You hit the import limit. A single spreadsheet has a cap on concurrent IMPORT calls. Dozens of live import formulas in one file will cause some to fail with no useful error. Consolidate into fewer, larger imports.

How to Import Data From a Website That Renders With JavaScript

You cannot, not with a native formula. This is the wall most people hit, so it is worth being blunt about what does and does not work.

The IMPORT functions read the raw HTML the server returns. Apps Script UrlFetchApp reads exactly the same thing. Neither runs a browser engine, so neither sees content a framework injects after page load. Spoofing a browser user agent changes nothing, because the obstacle is not detection, it is that no code is executing the page.

There are four real options, in the order worth trying them.

1. Find the official API. Most sites that render with JavaScript are fetching their own JSON endpoint. Open your browser devtools, go to the Network tab, filter to Fetch/XHR, and reload. The endpoint feeding the page is often public and returns clean structured data.
2. Look for a file export. Plenty of sites publish a CSV or an RSS feed that nobody links prominently. IMPORTDATA and IMPORTFEED handle both with no XPath at all.
3. Use a rendering add-on. Marketplace add-ons such as ImportFromWeb run a headless browser on their own servers and return the rendered result to a custom formula. That moves the work off Google's infrastructure, so it also sidesteps the import caps.
4. Give up on live data. If you need the numbers once rather than hourly, copying the rendered table out of your browser takes a minute, and cleaning it is a solved problem.

Option 1 is underrated. A JSON endpoint you found in the Network tab is more stable than any XPath, because it is a contract the site's own front end depends on.

How to Pull Data From a Website With Apps Script

When the data is behind a JSON API rather than in the page HTML, a custom function is the cleanest route. Open Extensions, then Apps Script, and paste this.

Call it from the grid like any other formula, with =FETCHJSON(A2, "price"). Custom functions are allowed to call external URLs, so no extra authorization prompt appears for UrlFetchApp.

Two limits will bite you. A custom function must finish within 30 seconds, which rules out slow endpoints and any loop over many URLs inside one call. And Sheets memoizes custom function results against their arguments, so if the arguments do not change, the cached value is returned and your script never re-runs.

That caching is why a price tracker built this way appears frozen. The workaround is the same trick the native functions need: pass an extra argument you can change, such as =FETCHJSON(A2, "price", $B$1), and edit B1 to force a re-fetch. If you need this on a timer instead, a scheduled script writing values into cells is the better shape, and our guide to automating Google Sheets without a Zapier subscription covers the trigger side.

How Often Google Sheets Refreshes Imported Web Data

All four IMPORT functions re-fetch on their own roughly every hour. They are not live, so a price tracker built this way is showing you data that could be an hour old.

To force an immediate refresh, delete the formula and retype it. A cleaner trick is to append a cell reference you can change, which breaks the cached result without altering the request.

Change the value in B1 and the import re-runs. Note that this only works on sites that ignore unknown query parameters.

How to Pull Data From Another Google Sheet With IMPORTRANGE

Not every import is from a website. Pulling a range out of a different spreadsheet uses IMPORTRANGE, which takes the source file and the range as two separate text arguments.

The first time you reference a new source file, the formula returns a #REF! error rather than data. Hover the cell and an Allow access button appears. Click it once and the pair of files stays connected, so this is a first-run step and not a recurring failure.

Both arguments are strings, which trips people up when they want the range to be dynamic. Build it with concatenation rather than trying to pass a range reference.

IMPORTRANGE pulls the range whole, so filter on arrival rather than importing several overlapping slices. Wrapping it in QUERY keeps one connection doing the work of five.

How to Pull Data From Google Sheets to a Website

Traffic runs the other way too, and the answer is to expose a tab as CSV over HTTP. With the file's link sharing set to anyone with the link can view, this URL returns a tab as a CSV download.

The file ID is the long string in the spreadsheet URL between /d/ and /edit. The gid is the number after #gid= when the tab you want is selected.

A second endpoint returns the same data and accepts a query, which is useful when the consumer only needs a few columns.

Both of those require loosening the document's own sharing settings. File, then Share, then Publish to web is the alternative, and it is the better choice for public data. It mints a separate long URL ending in /pub?output=csv, so the underlying workbook stays private and its ID is never exposed. Remember that anything published this way is readable by anyone who has the link.

Is Scraping a Website Into Google Sheets Allowed?

Technical feasibility is not permission. Many sites prohibit automated collection in their terms of service, and some data carries copyright or privacy restrictions regardless of how you obtained it.

Check the site's terms of service and its robots.txt file before building anything that runs on a schedule. Where an official API or a licensed export exists, use it. It will also be more stable than an XPath.

How to Clean Imported Web Data With One =FITS() Formula

FITS does not replace the import step. IMPORTHTML still fetches the page. What FITS removes is the parsing and cleanup, which is where most people get stuck.

Import the raw table into a tab, then describe the columns you actually want.

Prices came in as "$1,299.00 USD" and you want plain numbers? Say that instead of nesting SUBSTITUTE and REGEXEXTRACT.

You skip the XPath surgery on the messy half. The import brings the data in. FITS shapes it into something usable.

Which Import Method Should You Use

Use IMPORTHTML or IMPORTXML to fetch the page, since only native functions pull live URLs. Then reach for =FITS() to parse and clean what came back. For the cleanup side, see our messy data extraction guide, the definitive guide to AI data cleaning, or more AI formulas for content marketers. Scraped data is the messiest data there is. Our guide to automating Google Sheets tasks you used to need regex for shows the same pattern applied to every kind of cleanup. Imported tables are also where QUERY starts dropping rows on mixed data types, so keep our QUERY function help nearby. Scraped cells arrive with the links wrapped in prose, which is the exact case covered in extracting a URL from text. And once the import runs to thousands of rows, read how to format 8000 rows at once before you reach for ARRAYFORMULA. Some sites block the fetch outright rather than returning messy markup, which is a different problem with a different answer. Extracting Zillow data into Sheets works through that case, including why a spoofed user agent in Apps Script does not help. Imports also arrive as CSV more often than as HTML, so see cleaning up CSV data in Google Sheets for the damage that happens at import time.

Frequently Asked Questions