So let’s get started.

How to Import Tables from Websites to Google Sheets

The data that I am pulling is from a Wikipedia 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. Open Google Sheet file 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:

In the place of URL, paste the URL of the web page that you have copied earlier.In the place of query, enter the word “table”. In the place of the index, enter the index of the table on that web page. In the place of locale, you can enter your language preference such as “en_US”. But this is optional and you don’t have to specify if the website is already in the language you prefer.

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, press Enter so 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 use QUERY(data, query, headers) function here to manipulate data before importing it to Sheets:

Where data is the table dataWhere query is the function we want to do with that dataWhere headers are the optional number to say how many header rows there are in your data

  1. Just paste the entire IMPORTHTML function as the data. It should look like this.
  2. To select only specific columns, use the Select option with the columns you want to use. Something like SELECT col1, col2. You need to add it before closing the QUERY function in the quotes though. Once done, press Enter to only show data in selected columns. I am not including the optional header number here.
  3. To select only a few rows, you can use the Limit and Offset options in the QUERTY. Something like SELECT * 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.
  4. You can also combine both and select only a few rows and columns like this.
  5. 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.
  6. To turn the table sideways to convert rows into columns and vice versa, use TRANSPOSE() 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 can 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 can create a calendar in Google Sheets. And we have listed several useful Google Sheets add-ons to help you manipulate data further.