How do I scrape data from a website into a spreadsheet?
For one simple table, Google Sheets =IMPORTHTML(url, "table", 1) or Excel's Data, From Web works. For lists across many pages, behind a login or loaded by JavaScript, you need a browser that clicks through: an extension, a script, or an agent working in your own logged-in browser. Check the site's terms before you collect data.
How to do it
- One table. Google Sheets: =IMPORTHTML("url", "table", 1). Excel: Data, From Web, pick the table.
- Specific elements. Google Sheets =IMPORTXML(url, xpath) pulls items by XPath, for example every product name.
- Many pages or a login. Formulas cannot log in or click Next. Use a browser-based tool that follows pagination.
- Clean it. Remove duplicates, split combined fields, convert prices and dates to numbers.
- Respect the site. Read the terms and robots.txt, go slowly, and do not collect personal data you have no reason to hold.
Your options
| Option | Effort | Catch |
|---|---|---|
| IMPORTHTML / Power Query | One formula or query | No logins or click-through pagination |
| Scraper extensions | Build a selector recipe | Breaks when the page changes |
| Python script | Write and maintain code | Needs a developer |
| TODO for AI | Describe what you want | Slower than a script on huge sites |
Watch the agent do it
A real run on sample files. Yours starts from here.
I scraped all 50 pages of books.toscrape.com, which gave 1,000 books. They're in ~/work/scrape/books.xlsx on the cloud computer, with columns for title, price (£), star rating (1 to 5), stock status and a link to each book. The file is also shown above. The scraper that made it is scrape.py in the same folder.
Every one of the 1,000 books is listed as "In stock", so the stock column doesn't tell any books apart.
The 10 cheapest 5-star books (196 books have 5 stars):
| # | Price | Title |
|---|---|---|
| 1 | £10.00 | An Abundance of Katherines |
| 2 | £10.23 | Greek Mythic History |
| 3 | £11.05 | The Power Greens Cookbook: 140 Delicious Superfood Recipes |
| 4 | £11.21 | Dear Mr. Knightley |
| 5 | £11.33 | The Darkest Corners |
| 6 | £11.38 | Naturally Lean: 125 Nourishing Gluten-Free, Plant-Based Recipes--All Under 300 Calories |
| 7 | £11.64 | Fruits Basket, Vol. 2 (Fruits Basket #2) |
| 8 | £11.83 | Old School (Diary of a Wimpy Kid #10) |
| 9 | £11.89 | Superman Vol. 1: Before Truth (Superman by Gene Luen Yang #1) |
| 10 | £12.16 | Every Heart a Doorway (Every Heart A Doorway #1) |