How to Import Web Data into Google Sheets
You have been using Google Docs to create basic documents and spreadsheets but did you know that Google’s cloud-based Office suite can do even more.
For instance, you can directly import data from web pages and edit it inside Google Docs. You can translate the text of a cell from one language to another. You can read RSS feeds inside Google Docs. You can even use Google Docs for monitoring web page changes - this is handy for tracking price fluctuations for a product on any shopping website.
You don’t have to be a geek nor do you have to write any scripts to do such amazing stuff inside Google Docs. All you need to know is a couple of Google functions - ImportFeed, ImportHTML and ImportXML - and you’ll have a head start. Ready?
Today we’ll discuss ImportHTML, a useful Google formula that will help you fetch tables and lists from an external web pages into a Google Sheet.
Let’s start with an example. Open a new spreadsheet inside Google Docs, double-click any cell to enter the Edit mode and copy-paste the following function:
=ImportHTML("http://en.wikipedia.org/wiki/List_of_Bollywood_films_of_2012", "table", 2)
When you press Return, Google Docs will instantly import the second table from the corresponding Wikipedia page into your current sheet. If you replace the value of the third parameter in the above formula from 2 to, say, 4, Google Docs will import the forth table from the Wikipedia page.
In addition to
<tables>, you can also import HTML lists into Google Sheets that are created using the
<ul> tags. Try the next function on your Google Sheet:
=ImportHTML("https://www.labnol.org/internet/tips-for-tech-startups/19483/", "list", 2)
The 25 start-up tips are listed using an Ordered HTML List and you can import the entire list into your Google Sheet with one formula. If there are multiple lists on a page, you can change the third parameter with the index of the list that you wish to fetch inside Google Docs.
It is important to note that once a table or list has been imported into Google Docs, the table won’t update itself even if the data on the source page has changed. Also, Google imports the tables as plain text and all the formatting and links will be lost. ↓
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.