Scrape Data From a Website to Excel — 3 Ways That Work

There are three real ways to scrape data from a website to Excel: the built-in tools you already have (Excel Power Query and Google Sheets import formulas), a no-code scraper like Octoparse or Browse AI, and a short Python script that calls an API and writes the workbook for you. Each one works — for a specific kind of site and a specific kind of user. This guide walks through all three honestly, with copy-paste examples, and ends with a comparison table so you can pick in thirty seconds.

Method 1: The built-ins — Excel Power Query and Google Sheets formulas

If the page you want is a plain HTML table — a Wikipedia list, a league standings page, a government stats table — start here. It takes two minutes and costs nothing.

Excel: Power Query "From Web"

Excel 2016 and later ship with Power Query, which can pull tables straight off a URL:

1. Open Excel and go to the Data tab.
2. Click From Web (in the Get and Transform Data group) and paste the page URL.
3. In the Navigator, Excel previews every table it found on the page. Pick one.
4. Click Transform Data to clean columns and types in the Power Query editor, or Load to drop it straight into the sheet.

The result is a live query, not a one-off paste: right-click the table and hit Refresh, or set Data → Queries & Connections → Properties → Refresh every N minutes to re-pull on a timer while the workbook is open.

Google Sheets: IMPORTHTML and IMPORTXML

Google Sheets does the same job with two formulas. IMPORTHTML grabs the Nth table or list on a page:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 1)

IMPORTXML is the precision tool — it takes an XPath query, so you can pull one specific element instead of a whole table:

=IMPORTXML("https://example.com/product", "//h1")
=IMPORTXML("https://en.wikipedia.org/wiki/Moon_landing", "//a/@href")

The second formula pulls every link href on the page. Sheets re-evaluates these imports periodically (roughly hourly), which gives you a crude auto-refresh for free.

Where the built-ins stop working

Both tools share one hard limit: they read the raw HTML the server sends on first load. If the site fills in its data with JavaScript afterward — which describes Amazon, Google results, most e-commerce, most dashboards, and nearly every modern web app — Power Query sees an empty page and IMPORTXML returns #N/A or nothing. There is no browser rendering step, no login handling, and no anti-bot evasion: sites that rate-limit or block automated requests will block these too, and Google's shared IPs for Sheets imports are blocked by many sites out of the box.

The built-ins are also brittle in a quieter way: the formula points at a position in the page's markup, so a redesign silently breaks it. That is a general problem with HTML parsing — the page is a moving target — but with formulas you get no error handling at all, just #N/A in cell A1 on Monday morning.

Verdict: perfect for static tables you check occasionally. Wrong tool for dynamic sites, protected sites, or anything you need reliably on a schedule.

Method 2: No-code scraper tools (Octoparse, Browse AI, and friends)

The next step up is a visual scraper. Octoparse gives you a point-and-click workflow builder with auto-detection of data fields, preset templates for popular sites, cloud scheduling, and direct export to Excel, CSV, or Google Sheets. Browse AI trains a "robot" by watching you click the fields you want, then re-runs it on a schedule (hourly, daily) and can sync results into Google Sheets or Airtable, with change monitoring on top. Both handle JavaScript-rendered pages, pagination, and — on paid plans — proxy rotation.

That is a genuinely good fit for a non-technical user who needs one or two sites monitored: no code, a visual editor, and a working export within the hour.

The honest trade-offs:

• Per-site setup. Every website is its own robot or task. Ten sites means ten configurations to build and babysit.
• Fragility on redesigns. The robot clicks selectors it memorized. When the site ships a redesign, the robot returns wrong or empty data until you re-train it — and you often find out from the broken spreadsheet.
• Subscription cost. Free tiers are small; useful volumes, cloud scheduling, and proxies live on paid plans that typically start around the price of a mid-tier SaaS seat and climb with page volume.
• Hard to version and integrate. The scraper lives in a vendor GUI, not in your repo, so it does not fit code review, CI, or downstream pipelines the way a script does.

Verdict: the right call for a non-coder watching a handful of dynamic pages. Less right once you need many sources, custom transforms, or something your team can maintain in git.

Method 3: Python — API call, pandas, and to_excel()

If you can run a Python script — or have someone nearby who can — this is the strongest option, and it is shorter than people expect. The pattern: call an endpoint that returns clean JSON, flatten it with pandas, write the workbook with to_excel(). No selectors to maintain, because a scraping API handles the rendering, proxies, and retries on its side and returns the same normalized fields even when the site's markup changes. (For the fuller comparison of scraping pages yourself versus calling an API, see web scraping vs API; for the from-scratch route, our web scraping with Python pillar and the BeautifulSoup tutorial cover parsing raw HTML yourself.)

Here is the end-to-end version, using Crawlora's Amazon search endpoint. Install the three libraries first:

pip install requests pandas openpyxl

Then the whole script:

import requests
import pandas as pd

API_KEY = "YOUR_API_KEY"  # free tier: 2,000 credits/month, no card

resp = requests.get(
    "https://api.crawlora.net/api/v1/amazon/search",
    params={"k": "mechanical keyboard"},
    headers={"x-api-key": API_KEY},
    timeout=60,
)
resp.raise_for_status()
rows = resp.json()["data"]  # list of normalized result cards

df = pd.DataFrame(rows)[
    ["asin", "title", "price", "list_price", "rating", "review_count", "link"]
]
df.to_excel("amazon-keyboards.xlsx", index=False, sheet_name="results")
print(f"Wrote {len(df)} rows to amazon-keyboards.xlsx")

Run it and you have a real .xlsx on disk — columns typed, no copy-paste, no selectors. Swap the keyword in the k parameter, or swap the endpoint for any of the other supported platforms; the docs list every endpoint's fields, and the playground lets you test a request in the browser before writing any code. Because the API prices pay-on-success — you are charged only for successful 2xx responses — a failed fetch costs you a retry, not credits.

The Google Sheets variant (gspread)

Prefer the data landing in a shared Google Sheet instead of a file? Same script, different last step, using the gspread library with a Google service account:

• In Google Cloud Console, create a project and enable the Google Sheets API and Google Drive API.
• Create a service account, then download its JSON key file.
• Share your target spreadsheet with the service account's email address (it ends in iam.gserviceaccount.com), just like sharing with a colleague.

import gspread

gc = gspread.service_account(filename="service-account.json")
ws = gc.open("Keyboard price tracker").sheet1
ws.clear()
ws.update([df.columns.tolist()] + df.fillna("").values.tolist())

Everyone with the sheet open sees the new data on the next refresh — no file emailing.

Verdict: about fifteen minutes of setup, then the most durable option on this page: handles JS-heavy and protected sites through the API, survives redesigns, lives in version control, and scales from one keyword to a thousand with a loop.

Keep it fresh: scheduling the refresh

A spreadsheet that was accurate last month is how bad decisions get made. All three methods can refresh, but only the script refreshes unattended:

• cron (macOS/Linux): 0 6 * * * /usr/bin/python3 /home/you/refresh.py re-runs the script every morning at 06:00.
• Task Scheduler (Windows): create a Basic Task, trigger Daily, action Start a program pointing at python.exe with your script path as the argument.
• GitHub Actions — the nicest option, because it needs no machine that stays on:

name: refresh-sheet
on:
  schedule:
    - cron: "0 6 * * *"  # daily at 06:00 UTC
jobs:
  refresh:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: pip install requests pandas openpyxl gspread
      - run: python refresh.py
        env:
          CRAWLORA_API_KEY: ${{ secrets.CRAWLORA_API_KEY }}

Store the API key in the repo's Actions secrets, never in the script. If you push the refreshed .xlsx as a build artifact or write to Google Sheets via gspread, the spreadsheet updates itself every day whether your laptop is open or not. One courtesy note for any scheduled scraping: keep the cadence proportionate — a daily pull is a different animal from hammering an endpoint every minute, and rate limits exist on APIs too.

(Power Query can auto-refresh while a workbook is open, and Sheets import formulas recalculate on their own roughly hourly — fine for casual use, but neither runs when the file is closed, and neither retries a failure.)

Which method should you use?

Decision guidance, by who you are:

• Analyst with a static table and a deadline: Power Query or IMPORTHTML. Done in five minutes; do not overthink it.
• Marketer or ops person watching one or two dynamic pages, no code at all: a no-code tool earns its subscription. Budget time to re-train robots after redesigns.
• Anyone who needs many sources, protected sites, or a scheduled pipeline the team can maintain: the Python + API script. It is the only option here that is simultaneously cheap, durable, and automatable end to end.
• Junior dev deciding whether to build the scraper yourself: read web scraping with Python first — DIY is a great skill, but for production spreadsheets the API route removes the proxy, rendering, and parser maintenance that eats the time.

• Is the data in the initial HTML? If yes, Power Query / IMPORTHTML is enough.
• Is the page JavaScript-rendered or bot-protected? You need a no-code tool or an API.
• Does it need to refresh unattended? Script + cron/Actions is the reliable path.
• More than a couple of sites, or teammates who must maintain it? Prefer code over GUI robots.
• Only scrape public data, keep the cadence polite, and check the site's terms.

Turn any supported site into a spreadsheet

One GET request returns normalized JSON — pandas and to_excel() do the rest. 2,000 free credits a month, no card, and pay-on-success: you are charged only for successful 2xx responses.

Try it in the PlaygroundSee the Web Scraping API

Related reading: the web scraping with Python pillar for the full DIY toolkit, the BeautifulSoup tutorial if you want to parse HTML yourself, and web scraping vs API for when each approach wins.