Web Scraping with Google Sheets: 3 Ways to Get Website Data into a Sheet

Google Sheets has web import built in, and for the right kind of page it is the simplest tool there is: one formula, and the table appears and keeps itself up to date.

The catch is the kind of page. This guide covers the two formulas, what stops them, and what to do when the page is a card list, sits behind a login or loads as you scroll.

What you need

• A Google account and a blank sheet
• The address of the page with the data
• For method 3: Google Chrome and the Easy Scraper AI extension

Steps

1. 1

   Step 1: Method 1: IMPORTHTML for a table or a list

   Type the formula into an empty cell with the page address, the word "table" or "list", and a number that says which table or list on the page you want, starting at 1. The formulas are listed just below the steps.

   Tables and lists are numbered separately, so a page can have both a table 1 and a list 1. If the wrong one comes back, raise the number until you get the right one.

2. 2

   Step 2: Method 2: IMPORTXML for anything else

   IMPORTXML takes the page address and an XPath expression that describes which elements to fetch. "//h2" returns every second-level heading; "//a/@href" returns every link address. It is more flexible than IMPORTHTML and correspondingly fiddlier: you need to look at the page's code to write the expression.

3. 3

   Step 3: Method 3: scrape in the browser and paste

   Open the page in Chrome, click the Easy Scraper AI icon and let it detect the list. Press Start, click "Open results window", then Copy. Paste into cell A1 of an empty sheet: every column lands in its own column.

   For bigger tables, download the CSV instead and use File → Import in Google Sheets.

   

=IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4)

=IMPORTXML("https://example.com/","//a/@href")

=VALUE(SUBSTITUTE(B2,"£",""))