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:
- Open the website containing the table you want to import and take note of the table's index.
- Copy the URL of the webpage from the browser's address bar.
- Open your Google Sheets file where you want to import the table.
- Position your cursor on the cell where you want to start importing the table.
- 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)
- 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:
-
Copy the entire
IMPORTHTML
function and use it as thedata
parameter in theQUERY
function. It should look like this:=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Marvel_Cinematic_Universe_films", "table", 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.
-
To select specific rows, use the
LIMIT
andOFFSET
options in theQUERY
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.
-
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")
-
To filter the data based on specific criteria, use the
WHERE
option in theQUERY
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.
-
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 theTRANSPOSE
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.
Jim's passion for Apple products ignited in 2007 when Steve Jobs introduced the first iPhone. This was a canon event in his life. Noticing a lack of iPad-focused content that is easy to understand even for “tech-noob”, he decided to create Tabletmonkeys in 2011.
Jim continues to share his expertise and passion for tablets, helping his audience as much as he can with his motto “One Swipe at a Time!”