How to Import Data From Website to Google Sheets

Sometimes, you might need to pull data directly from websites into Google Sheets. Google provides a few built-in formulas to do that. Importing data from websites into Google Sheets can be both simple and tricky at the same time. It depends on the method you use.

In this article, I’ll show you five methods to import data from websites to Google Sheets. Notably, the final method is the easiest one.

In the first method, we’ll use the IMPORTXML function to import data from a website. This method is suitable for pulling HTML content like product names, titles, prices, etc., into Google Sheets. In this part, we’ll explore both HTML content extraction and table import using the IMPORTXML function.

This function will not work unless the web page meets two following key requirements:

• The website from which you want to fetch data must use HTML. If the website uses JavaScript to load content, then IMPORTXML won’t work.
• The data must be publicly accessible.

If a web page meets these two requirements, you can easily pull data using the function syntax: =IMPORTXML(“URL”, “XPath”)

• Replace “URL” with the link to your web page.
• Replace “XPath” with Inspect the element by Right-click >> Inspect in browser and note the tag/class.
• XPath can be challenging to find if you’re unfamiliar with HTML. Also, remember that XPath isn’t the same across web pages or data. It changes depending on the web page structure and the specific data you want to retrieve. I’ll show you how to build XPath in detail in a second.

Here’s how it works in a real-life example:

Steps:

➤ First, go to your desired web page and locate the exact data you want to import (e.g., a product name, price, title). For example, I will use the Wikipedia web page below to import a table into my Google Sheet.
➤ Select any part of the desired table, right-click on your mouse, and choose “Inspect.”

➤ After clicking on Inspect, the webpage’s source code appears on the right side of your screen. Here, you can find the class and tag, which ultimately helps you create the XPath.
➤ As you hover your mouse over the marked section of the source code, the corresponding table on the web page is highlighted. This indicates that this section contains your desired table.

➤ Now, let’s find the tag and class. For instance, the tag here is <table>, and the class is “wikitable.” With just these two, you can create your XPath like this:   (//table[contains(@class,’wikitable’)])
➤ This XPath might work with the IMPORTXML function, but rows may appear messy, or it might return the entire table block as a single cell.
➤ This is why we need to go deeper inside that table into the <tbody> element, which holds the actual rows.

➤ To grab each row, we need the that <tr>, which stands for “table row.”
➤ Now, the XPath will look like this:
“//table[contains(@class,’wikitable’)]/tbody/tr”)
➤ Once you’ve got the XPath, your hard work is done. Now copy the URL of the web page and enclose it with quotation marks. Do the same with your XPath, just like I showed you earlier. For a reminder, here is the function again:

➤ The final IMPORTXML function to fetch the Wikipedia table is:

➤ Open your Google Sheets worksheet, select cell A1 (or any cell you prefer), and apply the function.
➤ Hit Enter.

If your page contains multiple tables and you only want to import a specific one, find out the XPath like this. To import only table number 1, follow the function below:

The [1] means it will only select the first matching table, not the second or third. Simply change [1] to another number if you want a different table.

Earlier, I showed you the difficult part of using IMPORTXML first, so now you can easily understand this simpler method. In this section, I will demonstrate another way to use IMPORTXML, which is comparatively easier than the previous method. This formula is for importing specific data like headlines or prices.

Steps:

➤ Let’s say we want to import all the Heading 2 (h2) or Heading 3 (h3) elements from the same Wikipedia page.
➤ Then the function will be

➤ After applying the function, just press Enter

One more thing, do you remember how we identified the <tbody> and <tr> that represent the table element and table row?

You can also directly place the <tbody> and <tr> into XPath position, for example:

and get the table, but the imported data might appear messy.

IMPORTHTML is the most suitable function for importing tables and lists from a website. This method is very easy and allows you to pull data into your Google Sheets directly. Let’s see how to import data from a website using IMPORTHTML, but before that, here is the syntax of the function:

• Replace “URL” with the full link to the web page
• Use “table” or “list” depending on what you’re importing
• “1” refers to the first table or list on the page

You can also change “table” to “list” if your page contains a list instead of a table. And you can change the number 1 to any other number. For example, if your web page has two tables, changing it to 2 will import the second table. But using 3 will show an error if there’s no third table on the page.

Steps:

➤ Open your Google Sheet. I am using a random web page from Wikipedia, but you can use any web page as long as it contains HTML content.
➤ In any cell, enter the formula I just showed you. In my case, the formula would be:

➤ After entering the formula, just press Enter

With IMPORTDATA function, you can import any CSV or TSV file into your Google Sheets. But the file must end in .csv or .tsv and be publicly accessible. Here is the syntax of the IMPORTDATA function:

Simply replace “URL” with the direct link to your .csv or .tsv file, and it will import structured data from that public file into your sheet.

Steps:

➤ Get the direct link to the .csv or .tsv file
➤ In any of your Google Sheets, apply the function using your specific link
➤ In my case, the formula would be:

➤ Simply press Enter.

We are all familiar with copy and paste. With copy and paste, you don’t need any formulas or functions. This is the easiest method to manually import any data from a website.

But this method has some limitations. You can only pull tables and lists using copy and paste, and they’ll usually stay structured in a clean way. However, if you try to pull any other kind of data, it likely won’t be in a structured format.

Steps:

➤ Go to your desired web page that contains a table or list. I’m using the web page below to show an example.
➤ Simply copy the entire table you want. First, select the table or list that you want to import, then press CTRL+C or right-click on the mouse and select Copy

➤ Go back to your Google Sheet and paste it in a cell.

➤ That’s all, and here’s the following output:

Frequently Asked Questions

How to Import Live Data to Google Sheets?

You can use the IMPORTHTML or IMPORTXML functions to fetch live data from websites. That means when any information changes on the website, it will automatically update in your Google Sheets.

How to Import Files in Google Sheets?

To import files in Google Sheets, go to File >> Import >> Upload, or choose your desired file and click on Import data.

Concluding Words

We are at the end of the article. I’ve demonstrated all the methods, including a very simple one, such as copy and paste. Now, not all functions might be suitable for you. It depends on your needs.

If you only need to extract a table or list, you can go for IMPORTHTML or even copy and paste. But if you need more specific parts, like just the titles, use IMPORTXML.

Try practicing these functions in your own Google Sheets and stick with the one you find most comfortable. If you follow my instructions carefully, you should have no trouble at all.