Aryan Irani · Follow
7 min read · Oct 25, 2022
Welcome!
Recently I was working on a project where I wanted to get some data from a website for my college project. Normally, I would have just copy-pasted that data into my sheet. That's where I stumbled over multiple solutions to the problem, such as using Google Apps Script, using the IMPORTHTML formula in Google Sheets and more.
In this blog we will be discussing the various ways of importing webpage data into your Google Sheets.
So let’s get started.
In one of my previous YouTube shorts I had given you a glimpse of how I used the IMPORTHTML formula to import data for my college project.
In this blog we will be covering the following ways using which you can import webpage data into your Google Sheet.
- Import a table or list using the IMPORTHTML formula
- Import a table using cell reference inside the IMPORTHTML formula
- Use the Query function with the IMPORTHTML formula to get custom data
- Use Google Apps Script to import data
The IMPORTHTML formula is used to import data from a table or list with an HTML page. Normally, to import data from various websites, we copy/paste the data into our Google Sheet. Using the IMPORTHTML formula all you have to do is specify the link for the database and your data will get transferred to your Google Sheet.
Given below is the syntax for the formula, lets understand what goes into this formula.
- URL : Here you have to specify the target url where the database is present.
- Query : In the IMPORTHTML formula, you can either import the table or a list of table available on the website. In the query section of the formula you have specify whether you want to import a table or list.
- Index : Your webpage can have more than 1 table, so here you specify which database you want to import into your Google Sheet.
Now that we have understood the structure of the formula, lets take a look at it in action.
Import a Table using the IMPORTHTML formula
To see the IMPORTHTML formula in action we are going to start off by importing a table from a website.
For this blog, I will be using Netflix database that is available on wikipedia and a Google Sheet. If you want to work with the Google Sheet, click here.
https://en.wikipedia.org/wiki/List_of_Netflix_original_films_(2021)This is the database that we are going to import into our Google Sheet using the IMPORTHTML formula.
Once you open up the Google Sheet, go to a cell and type in the IMPORTHTML formula which will bring up the following.
Here you start out by specifying the url of the database.
After specifying the url of the database, you have to specify whether you want to import the table or import the list of tables on the website. For now just specify table in the query section of the formula.
Next, we specify the index of the table we want to import into the Google Sheet. For this example I want to get the first table from the website, hence I entered the index 1.
After setting the appropriate features, go ahead and hit enter.
On clicking enter you will see that the data from the webpage has successfully come into the Google Sheet. This means the IMPORTHTML formula has worked.
Import a List using the IMPORTHTML formula
Now that we saw how to import a table into your Google Sheet, now let’s take a look at how to import a list into your Google Sheet using the IMPORTHTML formula.
Everything is going to be the same as but you have to change the query section to list, show below.
After making the appropriate changes, go ahead and hit enter.
On clicking enter, you will see that the list of tables present on the website are listed in the sheet. Here the list of tables have been printed successfully using the IMPORTHTML formula.
In the previous examples we saw how we can import data from webpages using the IMPORTHTML, where we were putting all the parameters inside the formula. Now we are going to look at how to import using cell referencing inside the IMPORTHTML formula.
We are going to start off by declaring the link of the target web page from which you want to import your data. Next we declare the type of data you want to import. It can either be a table or a list. After declaring the URL and the query, go ahead and declare the index of the table you want to import.
Now that we have everything setup, lets go ahead and reference the cells inside the IMPORTHTML formula.
Here I have declared the IMPORTHTML formula where I have passed the cells inside the formula. The formula first accepts the url of the webpage, followed by the query and the index of the table you want to import.
On clicking enter you will see that the data from the webpage has successfully come into the Google Sheet.
The same can be done for importing a list into your Google Sheet using cell referencing inside the IMPORTHTML formula.
All you have to do is change the query to list and the formula will import the list of tables present on the target webpage.
Here you can see the formula has successfully imported the list of tables into your Google Sheet.
Now that we have learned how to get data from webpages into your Google Sheet using the IMPORTHTML formula in two different ways. It’s time to see how you can import custom data into your Google Sheet using the query function inside the IMPORTHTML formula.
Here I have started out by putting the query formula followed by the IMPORTHTML formula. Next you have to specify the URL, query followed by the table index.
After specifying the parameters for the IMPORTHTML formula, lets go ahead and declare the custom query for the query formula.
"select * where Col2 = 'Drama'")This is the query that I am going to use. The data that I have contains different movies from Netflix. So what I am going to do is get the movies with the Drama genre into my Google Sheet using the query formula.
The Column 2 of the data contains the genre of the movies. So in the formula we specify that we only want the movies who have the genre as Drama.
On clicking enter, you will see that the movies who only have the genre as Drama come into the sheet.
Here you can see that on successful execution of the formula, all the movies with the Drama genre have come into the sheet.
If you prefer watching the video, click on the link below.
In the next blog, we will be looking at how to import data into your Google Sheet using Google Apps Script.
In this blog we saw how we can import webpage data into your Google Sheet using the IMPORTHTML formula in different ways. I hope you have understood how to import webpage data into your Google Sheet. In the next blog we will be looking at how to import webpage data into your Google Sheets using Google Apps Script.
You can check out the official documentation for the IMPORTHTML formula.
Feel free to reach out if you have any issues/feedback at [email protected].
