Simple Web Scraping using Google Sheets (2022 updated)
Monday, May 9, 2022A 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.
Table of Contents
Option#1: Build an easy web scraper using ImportXML in Google Spreadsheets
Option#2: Grab price data with a simple formula: ImportXML
Option#3: There's another formula we can use
Option#4: No-code web scraping tool: Octoparse
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.
If you're finding a data service for your project, Octoparse data service is a good choice. We work closely with you to understand your data requirement and make sure we deliver what you desire. Talk to Octoparse data expert now to discuss how web scraping services can help you maximize efforts.
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
Most popular posts
Posts by topic
