Importing Tables from Websites to Google Sheets

Collecting information from the web and importing tables into Google Sheets can be a time-consuming task. However, with the help of Google Sheets functions, this process can be simplified, allowing you to import tables from websites in just a few seconds. Additionally, you have the option to filter and select specific columns or rows to import, making it even more convenient. In this article, we will guide you through the steps to import tables from websites to Google Sheets and reshape the data according to your requirements.

Importing Tables from Websites to Google Sheets

To begin the process, follow these steps:

  1. Open the website containing the table you want to import and take note of the table's index.
  2. Copy the URL of the webpage from the browser's address bar.
  3. Open your Google Sheets file where you want to import the table.
  4. Position your cursor on the cell where you want to start importing the table.
  5. Use the function =IMPORTHTML( URL, query, index, locale) to import the table. Replace the placeholders with the appropriate values:
    • URL: Paste the URL of the webpage you copied earlier.
    • Query: Enter the word “table” to indicate that you want to import a table.
    • Index: Enter the index of the table on the webpage.
    • Locale: (Optional) Enter your language preference, such as “en_US”.

Here is an example of how the function should look:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 2)

  1. Press Enter to import the table into Google Sheets.

By following these steps, you can easily import tables from websites into Google Sheets. Feel free to import as many tables as you need. If the wrong table is imported, double-check the index and make any necessary adjustments.

Reshaping the Table on Google Sheets

If you want to reshape the imported table or manipulate the data further, you can use the QUERY(data, query, headers) function. Here's how:

  1. Copy the entire IMPORTHTML function and use it as the data parameter in the QUERY function. It should look like this:

    =QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 2))

  2. To select specific columns, use the SELECT option along with the desired column names. For example:

    =QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 2), "SELECT col1, col2")

    Press Enter to display only the selected columns.

  3. To select specific rows, use the LIMIT and OFFSET options in the QUERY function. For example:

    =QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 2), "SELECT * LIMIT 4 OFFSET 2")

    This function selects all columns, copies only 4 rows, and leaves the first 2 rows as an offset.

  4. You can also combine both the column and row selections. For example:

    =QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 2), "SELECT col1, col2 LIMIT 4 OFFSET 2")

  5. To filter the data based on specific criteria, use the WHERE option in the QUERY function. For example:

    =QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 2), "SELECT * WHERE col5 = Kevin Feige")

    This function filters the data based on the value in column 5 and includes only the rows that have “Kevin Feige” in that column.

  6. To transpose the table and convert rows into columns or vice versa, use the TRANSPOSE() function. For example:

    =TRANSPOSE(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 2))

    You can also combine the QUERY function with the TRANSPOSE function to filter the data and transpose the table simultaneously.

By utilizing the QUERY function and other Google Sheets functions, you can reshape the imported table to suit your needs. Whether you want to select specific rows or columns, filter data, or change the table's orientation, Google Sheets provides the necessary tools.

Keep in mind that the imported table will remain in sync with the original webpage. If the webpage is updated with new information, the changes will be reflected in your Google Sheets table as well. However, if you manually modify any cells, those changes will not sync with the webpage.

Importing tables from websites to Google Sheets is a powerful feature that allows you to collect and manipulate data efficiently. Whether you need to import tables for personal or professional purposes, Google Sheets provides the necessary tools to streamline the process.


On a related note, if you want to create a calendar in Google Sheets, check out our tutorial here. Additionally, we have compiled a list of useful Google Sheets add-ons that can further enhance your data manipulation capabilities. You can find the list here.