How to use IMPORTHTML in Google Sheets [2024]
In this article, you'll learn how to use IMPORTHTML in Google Sheets in a simple step-by-step process. In addition, you'll get access to best practices to execute when executing IMPORTHTML.
Understanding IMPORTHTML in Google Sheets
The IMPORTHTML function is a powerful tool in Google Sheets for directly importing structured data from web pages into spreadsheets.
IMPORTHTML Syntax and Parameters
The Syntax is :
=IMPORTHTML(url, query, index)
Where the parameters are;
url - The URL of the page to examine, including protocol (e.g. http://).
The value for the URL must either be enclosed in quotation marks or reference a cell containing the appropriate text.
query - Either "list" or "table," depending on what type of structure contains the desired data.
index - Starting at 1, the index identifies which table or list should be returned as defined in the HTML source.
The list and table indices are maintained separately, so if both types of elements exist on the HTML page, there may be a list and a table with one or more indexes.
So, if a web page has multiple tables, you can scrap a table by using the index number. You'll learn how to find the index number in the step by step guide below. But before that, let's see the data types supported by IMPORTHTML.
Supported Data Types and Structures
There are various structures data types for IMPORTHTML such as:
HTML tables: Grid-like data with rows and columns, and
HTML lists: Sequential items (ordered or unordered)
When using IMPORTHTML:
The function will attempt to preserve the structure of tables by importing data into cells that correspond to the original table layout.
For lists, each list item will typically be imported into a single cell, with list items arranged vertically.
The choice between "table" and "list" in the query parameter of IMPORTHTML depends on how the data is structured on the source webpage. Understanding these data types helps correctly specify the query parameter and interpret the imported data in your Google Sheet.
How to use IMPORTHTML in Google Sheets to import a range table or list data?
Step 1: Open your Spreadsheet
Select the cell where you want the imported data to begin.
Step 2: Locate the web page URL
Open the webpage containing the table or list you want to import
Copy the full URL from the browser's address bar.
For this guide, I'm using this tabular data from Wikipedia:
https://en.wikipedia.org/wiki/List_of_circulating_currencies
Step 3: Enter the Google Sheets IMPORTHTML Formula.
Type the IMPORTHTML syntax as follows in a blank cell in a specified spreadsheet.
=IMPORTHTML(“URL,” “query,” index)
The index number for the table to be scrapped is 2 because it's the second table on the web page.
But you can't just assume the table's index number. If you do, it'll be a trial-and-error process.
So, to find the index number, follow these steps for this example:
Open the web page containing the data you want to import. We are using the screenshot of the webpage above — en.wikipedia.org/wiki/List_of_circulating_currencies
Right-click on the page and select “Inspect page" to open the browser's developer.
In the Elements tab, use the search function (usually `Ctrl+F` or `Cmd+F`) to search for `<table>` tags. This will highlight all the tables on the page.
Count the `<table>` tags from the top to identify which one contains your needed data. For the "List of circulating currencies" page, the main table listing the currencies is likely the first significant table, so it will usually be index `1`.
Once done, press the “enter tab” on your PC and wait for a while.
While waiting, you might encounter specific errors like #REF! Click on it to know the exact error. For this example, I need to click “allow access” so that Google Sheets can receive data from an external party, which
After loading the data, you'll see elements in the table in your spreadsheet.
What are the Best Practices for IMPORTHTML?
Ensure you Use Specific URLs:
Ensure your URL in the `IMPORTHTML` function is specific and stable. Avoid dynamic or session-based URLs, which may change and break your data import.
Check HTML Structure:
Verify the structure of the HTML on the webpage. Ensure the tables or lists are correctly formatted, as irregularities can cause import errors or incomplete data extraction.
Always import from publicly available web pages
If you attempt to use IMPORTHTML for non-public data, you will encounter errors that can lead to restrictions because most sites have terms of service.
Optimize IMPORTHTML for large datasets.
Techniques like data aggregation can help you manage the import process. The goal is to ensure the data is clean and error-free.
Pros and cons of using IMPORTHTML in Google Sheets
Pros ✅
It can recognize tables and lists.
Scrapping of data happens directly in the grid.
Cons❌
Too many parameters to set up, and it can be frustrating, especially when you want to get an index number .
Syntax is prone to errors, and it fails often.
CTA: How to use IMPORTDATA in Google Sheets.
How to import tables and lists in Rows without using any formula?
Rows is a comprehensive spreadsheet for modern teams that offers better UX for data ingestion and has native AI capabilities (AI analyst, AI-generated subtitles, native AI functions).
At Rows, we offer a Chrome extension called RowsX for scraping data from websites. It lets you scrape tables and lists from any website and copy or import them directly into a spreadsheet.
Follow the steps below to import a table or list in Rows without using any syntax whatsoever:
Step 1: Download the RowsX extension for Chrome and pin it in your browser bar.
Step 2: Go to g2 and look for the product you are searching reviews of, i.e. smartsheet
- Land on the product page
- Sort reviews by “lowest rated”
Step 3: Click on the Rows Extension icon and hit “Open in Rows”
- You will land on a fully working spreadsheet populated with your reviews
Below is the video showing how to carry out these steps in RowsX. Note that scrapping this table isn't possible in Google Sheets.
RowsX top features for importing tables and lists
As showcased above, with just a click on the RowsX icon, you can extract data from diverse web pages, such as Wikipedia articles or LinkedIn search results, and seamlessly integrate this information into your workflow. On top of this, RowsX comes with the following features:
Automated Detection: While browsing a web page, you can automatically identify all tables and lists on the page by clicking on the RowsX extension icon.
Preview and Choose: You get a preview of each detected data set, allowing you to select precisely what you want to extract.
Seamless Data Export: You can copy the data to your clipboard or open it directly in Rows. Open in Rows will open a new spreadsheet in your default workspace and automatically paste the data to the table.
RowsX supports other websites, such as Wikipedia, LinkedIn and YouTube's search results pages, ProductHunt, Our World in Data, ChatGPT, GitHub, Y Combinator, Yelp, Zillow, Yahoo Finance, G2, Google Maps, NASDAQ and Yellow Pages.
Other ways to ingest data into Rows
With RowsX, we showcased a quick, formula-free way to scrape and import data on a spreadsheet from virtually any website.
Chances are, you would do so also from other tools you use on a daily basis, ending up on a spreadsheet every time you have some data to transform or analyze.
With built-in data integrations, Rows has an even more convenient way to ingest live data from third-party sources and feed your recurring reports at your preferred schedule.
Rows comes with 50+ built-in data integrations in various domains:
Marketing: GA4, GSC, Facebook, Instagram, TikTok e.t.c
Productivity software: OpenAI, Notion, Slack, Email, Translate
Data Warehouse: MySQL, BigQuery, PostgreSQL, Snowflake, Amazon Redshift
And many more.
💡 Pro tip: With Rows you have the possibility of creating custom connections via our HTTP functions (GET, PUT, POST, PATCH) making the catalog of integrations virtually infinite.
In the following use case, we use Rows’ Google Search Console integration to fetch data on our website page-query performance on Google’s SERP.
You can choose one of the recommended report, such as Query-Page Performance (last 90 days):
Or alternatively build a custom one, like this one from GA4 which breaks down landing page conversions data by event type
On top of that, Rows offers you the power of AI to ask questions about data and automate data summarization and pivoting.
With the AI Analyst, you can ask AI to analyze, summarize, transform, and enrich your analysis. Click on the ✨ icon, at the top right corner of any table.
A chat interface will open on the right: you can ask a broad range of questions, from basic spreadsheet commands - plotting a chart or adding or formatting columns - to more complex tasks, such as slicing, pivoting, or computing metrics about your data.
For example, given a dataset with daily revenue and costs of various marketing campaigns, you can ask the Analyst to add a column with the profit margin. Watch the video below:
In addition, our AI Analyst is instructed to use our native OpenAI functions to perform data enrichment or extraction tasks. For example, you can ask the AI analyst to run a sentiment analysis on a column with product reviews, or add a column that categorizes addresses into regions, see below:
Want to know more about how our Analyst works? Check out our guide or watch our demo.
The spreadsheet where data comes to life
Aggregate business data from 50+ sources, including web analytics, ads platform and databases.
Try Rows for FreeGet Started with Rows
While Rows doesn't have the IMPORTHTML function built in, it can go the extra mile compared to Google Sheets with its Chrome extension.
Importing tables and lists into a spreadsheet in Google Sheets can be frustrating and annoying, especially when the formula leads to an error code. But with Rows, you just need a few clicks to scrap the data into your sheets. What's more? With our AI analyst, you can even scrap insights from every table or list in split seconds.
Ready to get started with Rows.com? Start using the product right away for free.
