How to Pull Data from Website into Google Sheets [Web Scrapping]

Today we will learn how to pull data from a website into Google Sheets. The time has gone, when you used to manually type data or organize copied data from a website into any document software. Now Google Sheets has announced kick-off functions through which you can pull even heavy data from a website into Google Sheets. Although the copy/paste option is also still effective. Today, we will take a deep overview of how to pull data from a website into Google Sheets.

Benefits of Pulling Data from a Website into Google Sheets

It’s irritating to manually type data from a website into Google Sheets and can take too much longer. Whether when you may face a disorder in the indexing of fetched data. But pulling data from a website into Google Sheets can be very effective and you can lift even any heavy data from a website into Google Sheets within a few seconds.

How to Pull Data from a Website into Google Sheets

In this tutorial, I will tell you three different methods to pull data from a website into Google Sheets.

1. Pull data from a website into Google Sheets by copy/paste
2. Pull data from a website into Google Sheets by using IMPORTHTML
3. Pull data from a website into Google Sheets

1. Pull data from a website into Google sheets by copy/paste

This is the simplest way to pull data from a website into Google Sheets by simply copying the data and pasting it into Google Sheets.

Step 1

Let’s suppose, this is a web page containing table data that we want to pull into our Google Sheets. So, first, we will select all the data that we want to pull.

Step 2

After selecting the data, press the right click of the mouse on it, and you will see a drop-down menu open where you will see a “Copy” option. Click on it to copy the data.

Step 3

After copying the data, come back into the Google Sheets and press again right click of the mouse where you want to place your data. Click on the “Paste” option from the drop-down menu to paste the copied data.

Step 4

As you click on the “Paste” option, you will see that your selected data will instantly be pulled into Google Sheets from the web as can be seen in the following picture.

Step 5

When we pull data from the web into Google Sheets it would be arranged unevenly due to a web format. But you can format this data in a good manner as I have placed in the following example.

2. Pull data from a website into Google Sheets by using IMPORTHTML

There is a built-in function to import data from a website into Google Sheets namely IMPORTHTML. In this method, we will take an overview of the function of Google Sheets.

Step 1

First, place your cursor where you want to pull data from the web then run the “IMPORTHTML” formula in the cell with an equal sign as can be seen in the following picture.

Step 2

The first argument in the “IMPORTHTML” formula is the URL of the web page from which you are importing data. Go onto the web page and copy the URL from the URL bar by right-clicking the mouse and then the “Copy” option to copy the URL.

Step 3

Then come back to the Google Sheets tab on your browser and paste the link by clicking Ctrl + V. This URL link must be in quotation marks as I have added in the following example.

Step 4

The second argument in the IMPORTHTML function is to specify the “Query” that which type of data is. Go to the web page to see which type of data you have to pull as here I have the following table in Google Sheets.

Step 5

We have to pull the table from the mentioned web page so we will write the “Table” in the syntax.

Similarly, if you have to pull any content from the web, you will write “Content” in the syntax.

Note: Be reminded that these arguments must be in quotation marks.

Step 6

The third and last parameter of the IMPORTHTML function is “Index“, which defines the indexing amount of data on the web page, in the following example, the table is at number 1 in indexing so here we will write “1” as can be seen below

Step 7

The syntax for IMPORTHTML has been completed Now just press the Enter key to get the result, as you can see the result in the following picture that data has been pulled from the web that we wanted to fetch.

3. Pull data from a website into Google Sheets by using IMPORTXML

IMPORTXML is also one of the most useful functions in Google Sheets to import or pull data from a website into Google Sheets. Let’s see how it works.

Step 1

Similarly, place your cursor where you want to pull data from a website into Google Sheets then run the “IMPORTXML” formula with an equal sign.

Step 2

There are only two arguments in an IMPORTXML formula where the first argument is URL. So first we will copy the web page URL from the URL bar as in the above method. Select the URL and press the right click of the mouse then click on the “Copy” option from the drop-down menu.

You can also simply press the shortcut key Ctrl+ C to copy the URL.

Step 3

Once you have copied the URL from the web page, return to the Google Sheets and paste the “URL” in the syntax between quotation marks by pressing CTRL + V as written below.

Step 4

The second argument of IMPORTXML is the x-path of the text. To find the x-path, select the data first that you want to pull then press the right click of the mouse on it, you will see an “Inspect” option from the drop-down menu.

Step 5

As you click on the “Inspect” option, a dialogue box will open at the right side of the window at the bottom of the browser, where you will see a reference code for the selected data as highlighted in the following picture.

Step 6

Press right click of the mouse on it, and you will see a “Copy” option from the drop-down menu, if you click on this “Copy” option another drop-down menu will open where you will find the “Copy full Xpath” option through which you can copy Xpath of the data.

Step 7

After copying the Xpath from the website, come back to the Google Sheets and paste the copied Xpath into the syntax as I have pasted in the following picture.

Note: Keep in mind while writing the syntax of the IMPORTXML function that both arguments must be in quotation marks.

Step 8

Now you have one click to go, simply press the Enter key and get the result as you can see in the following screenshot, the data we have selected have been pulled into Google Sheets.

Frequently Asked Questions

Can I Use the Slicer Function in Google Sheets to Analyze Data I Pulled from a Website?

Yes, the slicer tool for google sheets is perfect for analyzing data obtained from websites. By using this powerful feature, you can extract relevant information and easily manipulate it. The slicer tool allows you to filter and sort data effortlessly, making data analysis a breeze.

How to filter data while pulling data from a website into Google Sheets?

Let’s suppose you are pulling a table from a web page containing several columns and rows, if you need to pull specific columns or rows then use the following trick to pull filter data.

Step 1

As you see in the following example, we have sample data pulled from a website into Google Sheets. But we want to filter it only for the first three columns.

Step 2

To filter data, here, we will use the “Query” function of Google Sheets as an additional function.

Step 3

After opening the query function, give the criteria at the end that you want to filter in the following example, I have written “Select col1, col2, col3” to filter data only for column 1, column 2, and column 3.

Step 4

As I press the Enter key you can see the result below, Data has been filtered now for only the first three columns as required.

Step 5

In the same way, you may give different criteria to filter your fetched data as you can see in the following example, Select* where col4=’Zee Studios’. It will filter data and fetch only the “Zee Studios” data.

Step 6

As you can see the result in the following picture shows that Google Sheets has imported only the data that contains “Zee Studios” in the 4th column.

Conclusion

So, this is how to pull data from a website into Google Sheets, for a basic web scraper this tutorial is going to be very helpful. Thanks, and keep learning with Office Demy.