Scrape Websites With Google Sheets: IMPORTXML and IMPORTHTML Explained
Google Sheets can pull web data without code when the target page is simple and its data is present in the HTML. Two formulas cover most beginner tasks: IMPORTXML for targeted fields and IMPORTHTML for whole tables. This guide shows how to set up a sheet, find selectors, build the formulas, and recognise when Sheets is not enough.
Why use Google Sheets for scraping?
Web scraping means collecting structured values from web pages for research, monitoring, aggregation or reporting. Programming is common, but for small, static pages a spreadsheet formula can be faster to set up and easier for non-developers to maintain.
Google Sheets is mainly a spreadsheet tool, yet its import functions can fetch page content directly. That makes it useful for quick checks, small catalogues and one-off tables, provided the page does not require interaction, login, dynamic rendering or anti-bot handling.
Use this guide with a practice sandbox such as toscrape.com, which provides separate Books and Quotes pages for testing. The same workflow applies to other simple pages: create a sheet, inspect the element, copy its XPath, paste it into a formula, then generalise the query.
Which Sheets function should you use?
IMPORTXML: best for a specific field. You supply a URL and an XPath query, and Sheets returns only the matching text or attribute. It suits prices, titles, availability and ratings.
IMPORTHTML: best for an entire table or list. You supply a URL, a selector type and an index, and Sheets imports the matching block. It suits copying a ready-made table quickly.
IMPORTFEED: best for RSS or Atom feed items rather than ordinary page fields.
IMPORTDATA: best for importing values from an external data file such as CSV or TSV.
Decision checklist: choose IMPORTXML when you need one column or a cleaned field; choose IMPORTHTML when the table structure already matches your sheet; choose IMPORTFEED or IMPORTDATA when the source is a feed or file; move to a dedicated scraper when pages are dynamic, paginated at scale, interactive or protected.
Set up the sheet and find a selector
Create a blank spreadsheet through the Sheets home page or a new-sheet shortcut. Add clear column headings for each value you plan to collect, for example Title, Price, Availability and Rating for a book catalogue.
Open the target page in a browser and inspect the element you need. In Chrome, open developer tools, use the element picker, select the element, then copy its XPath from the context menu. Keep the copied query as a starting point rather than a finished formula.
Before pasting, replace double quotes inside the XPath with single quotes, because the formula itself uses double quotes around the URL and query. Allow a few seconds to a couple of minutes for the first result, depending on the site and network.
Scrape book fields with IMPORTXML
Start with the first book title only. The initial XPath is recorded in protected module code0001, and the first working formula is recorded in protected module code0002. Place that formula in the first data cell under the Title heading.
If the returned title looks shortened, inspect the page element again. The visible heading may be truncated while a wrapping link carries the full text in its title attribute. Point the query at that attribute instead, as recorded in protected module code0003.
To collect every title on the page, remove the positional index from the list-item part of the query so it matches all items rather than only the first. The generalised formula is recorded in protected module code0004. Sheets then fills the column with every matching title from that page.
Repeat the inspect-and-copy process for Price, Availability and Rating, placing each formula under its own heading. The resulting table should contain one row per book on the page, with rating values that may still be raw class text such as star-rating Three rather than a clean number.
Copy a table with IMPORTHTML
For a page whose quotes are already arranged as a table, IMPORTHTML is simpler than writing XPath for each cell. Create a new sheet tab, then use the formula recorded in protected module code0005 in the top-left cell. It imports the first table from the quotes page into the sheet.
Check the imported block before using it. Navigation or pagination controls may be part of the same table and therefore appear as extra rows. Because IMPORTHTML cannot filter rows, remove those unwanted rows manually. If the data you need were in a separate table, you could instead change the table index argument.
Limits to check before you rely on Sheets
Sheets formulas are a good fit for simple, static, single-page extraction. They are a poor fit when the page needs JavaScript rendering, clicks, scrolling, expanded sections, CAPTCHAs, rotating proxies or large-scale pagination.
Pagination also needs manual handling: add another formula for each additional page URL, such as a page-2 address, rather than expecting one formula to crawl the whole site. Hidden content behind accordions or collapsible sections cannot be reached at all.
Treat raw values as unclean until checked. Ratings may arrive as class names, titles may be truncated, and imported tables may include controls. Plan a cleaning pass in the sheet, and consider a dedicated scraping service when complexity, scale or blocking becomes the main problem.
| Function | Best use | Main limitation |
|---|---|---|
| IMPORTXML | One specific field matched by XPath | Needs a correct selector; no interaction or multi-page crawl in one formula |
| IMPORTHTML | Whole table or list block | Cannot filter unwanted rows inside the same table |
| IMPORTFEED | RSS or Atom feed entries | Not suited to arbitrary page fields |
| IMPORTDATA | External CSV or TSV data | Not suited to arbitrary page fields |
Based on the functions described in the source guide; exact formula syntax is in protected modules code0001-code0005.
Frequently asked questions
When should I use IMPORTXML instead of IMPORTHTML?
IMPORTXML is better when you need one specific field, such as a title, price, availability or rating, and you can describe it with an XPath query. IMPORTHTML is better when a whole table already contains the rows you want and you can accept importing that table as a block, then cleaning it in the sheet.
Why is a title truncated after IMPORTXML?
A truncated title usually means the visible text is shortened while the full value is stored elsewhere, for example in a title attribute on a wrapping link. Adjust the XPath to point at that attribute instead of the visible text node.
How do I extract every item on a page instead of just the first?
Remove a positional index such as li[1] from the XPath so the query matches every matching list item. One formula in the top cell can then spill results for all matching elements on that single page.
Can IMPORTHTML exclude unwanted rows?
IMPORTHTML has no row filter. If unwanted rows share the same table as the wanted data, you must delete or hide them after import. Choosing a different table index only helps when the wanted data is in a separate table.
Can Google Sheets handle pagination and interactive pages?
No. Sheets formulas do not click, scroll, expand accordions or wait for dynamic rendering, and they cannot fetch several paginated pages in one formula. You add a separate formula per page, and for interactive or heavily rendered pages a dedicated scraping tool is more suitable.