How much do you use Google Sheets?

If you are one who ends up creating a lot of reports or somebody who is playing with data, scratching it and finding out insights from it - you must have used Google sheets. 

There are some mind blowing functionalities in Google sheets that can make your work easy and understandable. Do you know you can copy paste a table from a webpage into google sheet or even fetch a title from a webpage using google sheets?

Doesn’t it sound wow? A simple tool like Google sheet can be an effective web scraper tool. 

Formula 

Before going to other aspects, let’s start with a bit of technicalities. The formulas that you can use for basic web scraping -

• Import XML Formula - A formula that can scrape structured data from HTML, XML file Formula - IMPORTXML(url, xpath_query)
• IMPORTHTML Formula - It is generally used to fetch data from website table     Formula - IMPORTHTML(url, query, index)

There are many more but these two are the basic ones. If you got lost? Don’t worry. It will be clear with examples. 

Example 1 - Scrape a table from website -

Suppose you want to fetch below table from wikipedia to your google sheet

Formula used - 

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_highest-grossing_films","table",1)

Result - 

Example 2 - How to scrape an Article Title? 

Some might feel that fetching an article title is not a tough task. Why use a formula? It's true that it's not a big task. But think of a situation when you want to fetch a title for 100 articles. 

This formula can make your work in minutes. 

Let me take 2 different articles -

Find out the H1 HTML tag 

Formula - =IMPORTXML(A2,"//*[@class='main-head single-view-head’]")

Result  :-

Example 3 - How to scrape data from Amazon?

Isn’t it amazing that you can even fetch amazon product title, rating and reviews. 

For example, let me use the following product - https://www.amazon.com/Microsoft-Ergonomic-Keyboard-Business-5KV-00001/dp/B00CYX26BC/

Formula - =IMPORTXML(A2,"//*[@id='productTitle']")

Result

If you want to find a price or rating, the formula will be changed a bit. 

To be honest, Web scraping via Google sheets is quite impressive. It can make big monotonous tasks easy. 

So, next time don’t engage in a boring job. Apply a formula and scrape your data easily. 

Hope you enjoyed reading it!!