How to Scrape a Website into Excel or Sheets (Formula, Power Query, CSV)

Use IMPORTHTML in Google Sheets or Data, From Web in Excel for any public page with a table. Neither tool can find data the page does not show, such as the people who work at a company. For that, export rows from an API and open the CSV.

This is the short version. It has the formula, the Power Query settings, the request and the output.

TL;DR

• Public table on a page: one formula in Sheets or one From Web query in Excel. About 5 minutes, 0 LeadOcean records.
• Pages that need JavaScript or a login will not load in either tool. Do not fight them.
• People or company rows the page lacks: size the list for free, then export a CSV. About 15 minutes, 100 records for a 100-row test. CSV export needs Solo ($97 a month) or higher.

Prerequisites

• A Google account for Sheets, or Excel with the Data ribbon (Power Query).
• A public page you are allowed to read. Check its terms and robots.txt first.
• For Steps 3 and 4: a LeadOcean key from app.leadocean.io, exported as $LEADOCEAN_API_KEY. The free plan gives 1,000 records for life, no card, 5 requests a second. Bulk CSV export is not on Free.
• curl and jq in a terminal.

Step 1: Google Sheets, one formula

IMPORTHTML(url, query, index) imports a table or list from an HTML page. query is "table" or "list". index starts at 1, and tables and lists are counted separately (Google Docs Editors Help, September 2026).

Put this in cell A1 of an empty sheet:

=IMPORTHTML("https://example.com/members", "table", 1)

If A1 stays empty or shows an error, change index to 2, 3 and so on. The formula reads the HTML the server sends. A table that a script draws after load is not in it.

Step 2: Excel, From Web

In Excel, choose Data, then From Web. Enter the URL, select OK, then pick the table in the Navigator and load it (Microsoft Learn, September 2026).

Two connector facts matter here:

• Web.Page needs Internet Explorer 10.
• Web.BrowserContents needs Microsoft Edge's WebView2 runtime.

If the Navigator shows no table, open the Web View tab or select Add table using examples and type a few sample values. If that still fails, stop. Look for a CSV download on the page instead.

Step 3: Size the rows the page does not have

A directory page gives you a name and a site. It does not give you the VPs, their titles or their email status. Ask the API instead, and count first, because counts cost nothing.

count=true goes in the query string, with limit=1. The body filters are arrays of exact strings, so read the allowed values before you guess (API reference, October 2026).

curl -s "https://api.leadocean.io/v1/people/search?count=true&limit=1" \
  -H "x-api-key: $LEADOCEAN_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"jobLevel": ["VP"], "country": ["US"], "industry": ["Computer Software"]}'

meta.total is the count and is capped at 100,000. The same filters through the free MCP tool leadocean_count_leads returned this on October 21, 2026:

{
  "total": 804,
  "totalIsExact": true,
  "totalAll": 2796,
  "distinctCompanies": 382,
  "credits": 0,
  "defaults": "Counted only addresses worth sending to (emailStatus: verified or catch_all_valid or catch_all)"
}

That is 804 US VPs at software companies with a mailable email, across 382 companies. Without the email filter the same filters match 2,796 people.

Step 4: Export the rows as a CSV

POST /v1/exports takes the same filters, a limit and a column preset. It runs async, up to 50,000 rows a request and 500,000 a day. The search preset has 27 columns and no contact points. Every row costs one record.

Start with 100 rows:

curl -s -X POST https://api.leadocean.io/v1/exports \
  -H "x-api-key: $LEADOCEAN_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "us-software-vps",
    "limit": 100,
    "preset": "search",
    "filters": {
      "jobLevel": ["VP"],
      "country": ["US"],
      "industry": ["Computer Software"]
    }
  }' | jq -r '.data.id'

Poll the export until status is done, then download it. pollAfter tells you how many seconds to wait.

EXPORT_ID=paste_the_id_here

curl -s https://api.leadocean.io/v1/exports/$EXPORT_ID \
  -H "x-api-key: $LEADOCEAN_API_KEY" | jq '.data | {status, rows, billed, pollAfter}'

curl -s https://api.leadocean.io/v1/exports/$EXPORT_ID/download \
  -H "x-api-key: $LEADOCEAN_API_KEY" -o vps.csv

data.file.expiresAt says until when the file is kept. Prefer a UI? The Exports page in app.leadocean.io has a filter builder, a column picker, the record price before you start and a Download button.

Step 5: Open the CSV in Excel or Sheets

In Sheets, choose File, Import, Upload, then pick vps.csv. In Excel, choose Data, From Text/CSV and load it. You can also keep your Step 1 or Step 2 table on its own tab and match rows on the company domain.

For a ready flow, see the n8n recipe. For the wider topic, see lead scraping. For a longer scripted version of Steps 1 and 2, see how to scrape data from any website (no code), and for paginated listings see how to scrape a directory.

What you get

The export call answers 202 with a data object. Response shape from the API reference, with placeholder values:

{
  "success": true,
  "data": {
    "id": "EXPORT_ID",
    "name": "us-software-vps",
    "status": "queued",
    "requested": 100,
    "estimatedTotal": 804,
    "perRow": 1,
    "remaining": 900
  }
}

When status is done, rows is the number written and billed is the records charged. The CSV has one row per person, deduplicated by person_id. The free count in Step 3 is the real output from October 21, 2026.

Troubleshooting

FAQ