Simple Web Scraping using Google Sheets (2020 updated)

A web scraping tool can automate the process of repetitive copying and pasting. Actually, Google sheets can be regarded as a basic web scraper. You can use a special formula to extract data from a webpage, import the data directly to google sheets and share it with your friends.

In this article, I will first show you how to build a simple web scraper with Google Sheets. Then I will compare it with an automatic web scraper, Octoparse. After reading it, you will have a clear idea about which method would work better for your specific web scraping needs. 

Option#1: Build an easy web scraper using ImportXML in Google Spreadsheets

Step 1: Open a new Google sheet.

Step 2: Open a target website with Chrome. In this case, we choose Games sales. Right-click on the web page and it brings out a drop-down menu. Then select "inspect". Press a combination of three keys: "Ctrl” + “Shift” + “C" to activate "Selector". This would allow the inspection panel to get the information of the selected element within the webpage.

Step 3: Copy and paste the website URL into the sheet.

Option#2: Let's try to grab price data with a simple formula: ImportXML

Step1: Copy the Xpath of the element. Select the price element, and Right-Click to bring out the drop-down menu. Then select “Copy”, choose “Copy XPath”.

Step 2: Type the formula to the spreadsheet.

=IMPORTXML(“URL”, “XPATH expression”) 

Note the "Xpath expression" is the one we just copied from Chrome. Replace the double quotation mark " " within the Xpath expression with a single quotation mark''.

Option#3: There's another formula we can use:

=IMPORTHTML(“URL”, “QUERY”, Index)

With this formula, you extract the whole table.

Now, let's see how the same scraping task can be accomplished with a web scraper, Octoparse.

Step 1: Open Octoparse, build a new task by choosing “+Task” under the “Advanced Mode”

Step 2: Choose your preferred Task Group. Then enter the target website URL, and click "Save URL". In this case: Game Sale website http://steamspy.com/

Step 3: Notice Game Sale website is displayed within Octoparse interactive view section. We need to create a loop list to make Octoparse go through the listings. 

1. Click one table row (it could be any file within the table) Octoparse then detects similar items and highlights them in red.

2. We need to extract by rows, so choose “TR” (Table Row) from the control panel.

3. After one row has been selected, choose the “Select all sub-element” command from the Action Tips panel.
Choose “Select All” command to select all rows from the table.

Step 4: Choose “Extract data in the loop” to extract the data.

You can export the data to Excel, CSV, TXT or other desired formats. Whereas the spreadsheet needs you to physically copy and paste, Octoparse automates the process. In addition, Octoparse has more control over dynamic websites with AJAX or reCaptcha. 

Artículo en español: Simple Scraping con Google Sheets 2020 actualizado
También puede leer artículos de web scraping en el Website Oficia

More Resources:

Top 20 Web Crawling Tools for Extracting Web Data

Top 30 Big Data Tools for Data Analysis

25 Hacks to Grow your Business with Web Data Extraction

Web Scraping Templates Take Away

Video: Create Your First Scraper with Octoparse 8.X