Nowadays, we have access to all kinds of data online. Whatever topic you choose, there’s sure to be relevant data available for you to analyze and use in your projects. You often just need a table or two, and using an API is not always an option. So how can you get your hands on that table?
Most people will try to copy/paste the table, but often you’ll end up with frustrating results. You’ll likely have to spend time fixing the structure, adding missing data, or resolving errors. Additionally, if the table is updated online, you’ll have to repeat the process.
Fortunately, the =IMPORTFROMWEB() formula allows you to quickly and easily copy a table from a webpage directly into Google Sheets. Let’s explore in more detail how you can import tables without worrying about formatting or updates.
Common issues copy and pasting tables from a website
If you’ve ever tried to copy/paste a table from a webpage, you’ve likely encountered some issues. The most serious issues are the following:
- Errors in cells
After copying and pasting the Top Websites table from SimilarWeb into Sheets, you can see that column D contains multiple errors due to formatting issues.
- Displaced data
Below, you can see the result of pasting the table with IMDB’s Top-rated TV Shows. Unfortunately, the contents are in the wrong columns:
As you can see, copying and pasting a table from a webpage may not be worth your time and effort. Luckily, there is a reliable alternative to this – the ImportFromWeb add-on!
Import online tables with importFromWeb
ImportFromWeb is a Google Sheets add-on that allows you to import any table from any website using just one formula. Anyone can use it, regardless of technical skills. All you need to worry about is finding the table you want; the add-on will do the rest.
The add-on gives you a new formula which requires two parameters:
=IMPORTFROMWEB(data_source, selectors)
- data_source: the URL for the webpage containing the table you want to copy. Learn more about the function’s data sources.
- selectors: The “table” selector is exactly what we’re looking for, but you can also use the XPath for the object you want.
How to copy a table with ImportFromWeb
ImportFromWeb makes importing the exact table you want from any webpage easy. Simply download the ImportFromWeb add-on in Google Sheets, then use the following instructions to copy a table using the IMPORTFROMWEB formula.
Copy the only table on a webpage
You may be on a website that only contains one table that you wish to copy. In this case, simply use the exact formula listed above to import your table. In this example, the webpage for SimilarWeb’s Top Websites contains only one table.
- In Sheets, paste the URL into a cell, and the selector “table” into another.
- In another cell, type in the =IMPORTFROMWEB() formula and choose the cells with the URL and the selector. Press Enter.
- That’s it. Your table should be imported correctly, with no formatting or data errors.
Copy one of multiple tables on a webpage
If you want to copy one table from a webpage containing multiple tables, you just need to adjust the =IMPORTFROMWEB() formula slightly.
For example, Wikipedia’s entry for “List of highest-grossing films” is a webpage containing multiple tables.
Since the tables are numbered in order, you simply need to note which one you want and modify the selector to “table/n”, where “n” is the number of the table.
- Type in the URL for the webpage. For the selector, type “table/1” to retrieve the first table.
- Type in the formula, selecting the URL and selector cells.
- And there you go. As you can see below, the table has been imported correctly.
That’s how easy it is to copy tables from web pages using the function =IMPORTFROMWEB(). If you prefer, you can also use XPaths to retrieve tables.
Additional benefits with ImportFromWeb
There are many benefits to using ImportFromWeb, in addition to those shown above.
- Full control over updates: manual or automatic.
- Hourly, daily, or weekly triggers to update your data, even if you’re offline.
- Automatically converts numbers and dates to the right format.
- Use advanced options to control how data is imported.
- Use regular expressions to clean your data before import.
As you have seen, ImportFromWeb is extremely easy to use and saves you from dealing with the usual copy/paste problems. You now know how to use it to copy tables from web pages, no matter how many tables the page contains.
The best part about this? Importing tables is only one of the uses of ImportFromWeb.
