If you find a valuable table of data on Wikipedia, a financial website, or a sports statistics page, manually copying and pasting that data into Google Sheets is a frustrating process. The formatting often breaks, the columns misalign, and worst of all, if the website updates its data tomorrow, your spreadsheet remains entirely static.
To solve this, Google Sheets offers a powerful built-in web scraper: the IMPORTHTML function. In this guide, you will learn how to use this formula to instantly pull live tables and lists from public websites directly into your spreadsheet.
Understanding the IMPORTHTML Syntax
The IMPORTHTML function requires three specific arguments to locate and extract the data.
=IMPORTHTML("url", "query_type", index)- url: The exact web address of the page containing the data. This must be enclosed in quotation marks.
- query_type: You must tell Google Sheets what HTML element to look for. This will almost always be either
"table"or"list". It must be enclosed in quotation marks. - index: Websites often have multiple tables (e.g., a sidebar menu might technically be a table). The index is a number (1, 2, 3…) that tells Google Sheets which specific table on the page you want to pull.
Use Case 1: Importing a Simple Wikipedia Table
Suppose you want to import a list of countries by population from a Wikipedia page. Copy the URL of the page.
Click on an empty cell (e.g., A1) in your Google Sheet and type:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population", "table", 1)When you press Return, the cell will briefly display a “Loading…” message. Google Sheets is actively reaching out to the Wikipedia server. Within seconds, the entire table will spill out into your spreadsheet, perfectly formatted into columns and rows.
Use Case 2: Finding the Correct Index Number
The most common issue with IMPORTHTML is pulling the wrong data because you guessed the incorrect index number. If you use "table", 1 and get a weird navigation menu instead of your data, you simply need to change the index number to 2, 3, or 4 until you find the correct one.
To avoid guessing, you can quickly inspect the website’s code:
- Open the target website in Google Chrome.
- Right-click exactly on the data table you want and select Inspect.
- The Developer Tools panel will open. Press Ctrl + F (or Cmd + F on a Mac) to open the search bar inside the code panel.
- Type
<table. Chrome will highlight every table on the page and tell you how many there are (e.g., “3 of 5”). This gives you a massive clue as to what index number you should use in your formula.
Use Case 3: Combining with Other Functions
Because IMPORTHTML pulls the data live, you can wrap it in other functions to clean the data before it even hits your spreadsheet.
If you import a table of financial data but only want to see the top 5 rows, you can wrap it in the QUERY or SORTN function.
If you just want to extract a specific column from the imported table without seeing the rest of the garbage data, wrap it in an INDEX function.
Warning: IMPORTHTML only works on public websites. It cannot scrape data behind a login screen, nor can it execute complex JavaScript. If the table on the website requires you to click a button to load the data, IMPORTHTML will not be able to see it.