How to Import Tables from Website to Google Sheets
You don’t have to manually enter each detail into Google Sheets if you want to collect information from the web. We have Google Sheets functions at our disposal that will make this process easier allowing anyone to import tables from websites to Google Sheets in a matter of seconds. We can also choose to show only specific columns or rows if need be. Also, we can filter the table and only add data that we want to Google Sheets.
So let’s get started.
Table of Contents
How to Import Tables from Websites to Google Sheets
The data that I am pulling is from aWikipedia page, but you can follow the same steps on any website.
1.Open the website that you want to grab the table from. Here check the index of the table that you want to copy from that page. If it is the fifth table on the page, the index is 5. Keep note of this index, we will need it later.
2.Now copy the URL of the page from the browser’s address bar.
3.OpenGoogle Sheetfile where you want to import the table.
4.Place your cursor on the cell to start importing the table from that position.
5.Now we can use the function=IMPORTHTML( URL, query, index, locale)to grab that table. First enter=IMPORTHTML()and then:
The end result will be something as below. Note that you have to use quotation marks for URL, query, and locale as shown below.
6.Once done, pressEnterso Google Sheets can grab the required data from the web page.
That’s it, the table should be imported to Google Sheets now. You can import as many tables as you need easily. If there are more than two tables and it imports the wrong one, the index of the table might be wrong. Recheck and change the table’s index accordingly.
How to Reshape the Table on Google Sheets
We have grabbed the entire table from a website in the previous method. But what if you want to reshape the data? For example, you want to import only some rows or columns to Google Sheets. What if you only want to filter data or change the direction of the table? Again, you can do all of it easily with Google Sheet functions.
We will useQUERY(data, query, headers)function here to manipulate data before importing it to Sheets:
1.Just paste the entireIMPORTHTMLfunction as the data. It should look like this.
3.To select only specific columns, use the Select option with the columns you want to use. Something likeSELECT col1, col2. You need to add it before closing the QUERY function in the quotes though. Once done, pressEnterto only show data in selected columns. I am not including the optional header number here.
4.To select only a few rows, you may use the Limit and Offset options in the QUERTY. Something likeSELECT * LIMIT 4 OFFSET 2. Here * represents all the columns, LIMIT represents the rows of table data you want to copy, and OFFSET represents rows of data you want to leave in the starting. So with the function below, we are grabbing the data in all columns, but copying only 4 rows leaving 2 as an offset at the start.
5.you may also combine both and select only a few rows and columns like this.
6.There is a way to filter the data using the WHERE option in QUERY. With the function below, it will check the col 5 and only include rows that have Kevin Feige.
7.To turn the table sideways to convert rows into columns and vice versa, useTRANSPOSE()function.
You can also use filter the data with the QUERY function and then turn the table with the TRANSPOSE function by combining both.
Can you import tables from a webpage in Google Sheets mobile app?
Yes, all the formulas and functions will work on the Google Sheets mobile apps too. The only difference is that you have to edit the formula at the bottom of the page.
Can we import tables from all websites?
No, some websites prevent web scrapping functionality, so you can not parse data. But, most websites allow you to import tables from their pages to your Google Sheet.
What happens if the website is down?
This feature syncs the table with the website. So if the website is down, you cannot access the data from your Google Sheet either. Not just that, if the website owner decides to add another table above the table that you imported, the index of the table will change and Sheets will import data from a different table instead. Unless you own the website, you have no control over table data.
Importing Tables From Web to Google Sheets
you’re able to easily import tables from websites with just a URL of the web page, the index of the table on that webpage, and the ImportHTML function. You can also use Transpose and Query functions to reshape the data to only query specific rows and columns, or even filter the data. Once imported, the data will remain in sync. So if the table on the web page is updated with new info, the changes will also reflect on your Google Sheets table. But if you change any cell manually later, it will not sync.
On a related note, here is how you cancreate a calendar in Google Sheets. And we havelisted several useful Google Sheets add-onsto help you manipulate data further.
Ravi Teja KNTS
Tech writer with over 4 years of experience at TechWiser, where he has authored more than 700 articles on AI, Google apps, Chrome OS, Discord, and Android. His journey started with a passion for discussing technology and helping others in online forums, which naturally grew into a career in tech journalism. Ravi’s writing focuses on simplifying technology, making it accessible and jargon-free for readers. When he’s not breaking down the latest tech, he’s often immersed in a classic film – a true cinephile at heart.
