DEV Community

Gio Rich
Gio Rich

Posted on

How to pull data from a public website into Google Sheets (and when you need a real scraper)

You found a public page with exactly the data you need: a directory of members, a list of open roles, a table of prices, a page of event listings. Now you need it in a spreadsheet. Before you copy and paste 300 rows by hand, try this. For a lot of simple pages, Google Sheets can pull the data in by itself, and it takes about ten minutes to set up.

This guide covers three built-in functions, IMPORTHTML, IMPORTXML and IMPORTDATA, plus how to tell when a page is too complex for them.

Step 1: Check what kind of page it is

Open the page and look at how the data is laid out.

  • It sits in a real table (rows and columns with borders, like a Wikipedia table). Use IMPORTHTML. This is the easiest case.
  • It's a repeated list or set of cards (each item is a block with a name, a price, a link). Use IMPORTXML.
  • The page links to a .csv or .tsv file. Use IMPORTDATA on that file link.

One quick test before you start: right-click the page and choose View Page Source, then press Ctrl+F and search for one of the values you want, such as a company name. If you find it in the source, Sheets can probably read it. If you can't find it, the page loads its data with JavaScript after it opens, and the built-in functions will come back empty. Skip to Step 5.

Step 2: Pull a table with IMPORTHTML

In an empty cell, type:

=IMPORTHTML("https://example.com/page", "table", 1)
Enter fullscreen mode Exit fullscreen mode
  • The first part is the page URL, in quotes.
  • "table" tells Sheets to look for HTML tables. Use "list" for bulleted or numbered lists.
  • The 1 means the first table on the page. If you get the wrong table, change it to 2, 3 and so on until the right one shows up.

Press Enter. After a few seconds the whole table fills in below and to the right of the cell.

Step 3: Pull repeated items with IMPORTXML

IMPORTXML reads any part of a page using an XPath, a short path that points at the elements you want. You don't need to learn XPath from scratch. The browser can give it to you.

  1. Right-click one of the values you want (say, the first job title) and choose Inspect.
  2. The developer panel opens with that element highlighted. Look at its tag and class, for example <h3 class="job-title">.
  3. Write a path that matches every element like it:
=IMPORTXML("https://example.com/jobs", "//h3[@class='job-title']")
Enter fullscreen mode Exit fullscreen mode

That returns every job title on the page, one per row. Do the same in the next column for the second field:

=IMPORTXML("https://example.com/jobs", "//span[@class='location']")
Enter fullscreen mode Exit fullscreen mode

To grab links instead of text, point at the href attribute:

=IMPORTXML("https://example.com/jobs", "//h3[@class='job-title']/a/@href")
Enter fullscreen mode Exit fullscreen mode

A few patterns that cover most pages:

  • //a/@href gets every link on the page.
  • //div[@class='card']//h2 gets every h2 inside a card.
  • //*[contains(@class,'price')] matches any element whose class includes "price", which helps when classes look like price price--sale.

Check that each column has the same number of rows. If one field is missing on some items, the columns will drift out of line. When that happens, pull a wrapper element for each item instead, or clean it up in Step 6.

Step 4: Handle more than one page

Many lists are split across pages with URLs like ?page=2. Put the base URL in one cell, the page numbers in a column, and build each URL with &:

=IMPORTXML("https://example.com/jobs?page=" & A2, "//h3[@class='job-title']")
Enter fullscreen mode Exit fullscreen mode

Fill that down for each page number and stack the results. Keep it to a few dozen pages. Sheets limits how many import calls a sheet can run, and large sheets get slow.

Step 5: Know when the built-in functions won't work

You'll get #N/A, "Imported content is empty" or "Could not fetch URL" when:

  • The data loads with JavaScript after the page opens (the Step 1 source test fails).
  • The site needs a login, or blocks automated requests.
  • You need thousands of rows or many pages.
  • You want it refreshed on a fixed schedule and stored, not just recalculated whenever Sheets feels like it.

For JavaScript pages, check the browser's developer tools first: open the Network tab, reload the page, and filter by Fetch/XHR. Often the page pulls its data from a JSON address you can see there. Some of those work directly with a short Apps Script, and some don't. Never try to get around a login or a site's terms. Stick to public data.

Step 6: Clean the result

Imported data usually needs a little tidying:

  • =TRIM() removes extra spaces.
  • =UNIQUE() drops duplicate rows.
  • =SPLIT(A2, ",") breaks "City, State" into two columns.
  • Data > Data cleanup > Remove duplicates does the same without a formula.

When the data looks right, copy the range and use Paste special > Values only so it stops re-fetching and your copy stays fixed.

Wrap-up

For one clean table or a simple list, IMPORTHTML and IMPORTXML will save you hours of copying. For JavaScript-heavy sites, big lists, or a sheet that refreshes on a schedule, a real scraper is the better tool. If you'd rather not build one, I do these at fiverr.com/rel8ble.

Written with AI assistance.

Top comments (0)