Simple Web Scraping using Google Sheets (2022 updated)
Monday, June 20, 2022Can you web scrape in Google Sheets? You may also have the same question as Google sheets almost become one of the most popular cloud-based tool. Actually, Google sheets can be regarded as a basic web scraper. You can use a special formula to extract data from websites, import the data directly to google sheets and share it with your friends. By reading the following parts, you can learn the easy methods on how to build a simple web scraper with Google Sheets.
Table of Contents
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: 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: Another formula to get data with Google Sheets
There's another formula we can use:
=IMPORTHTML(“URL”, “QUERY”, Index)
With this formula, you extract the whole table.
Option#4: Automatic web scraping tool without coding
Now, let's see how the same scraping task can be accomplished easily with a web scraper, Octoparse. It allows you extract more data from website than Google Sheets, and you don't need to learn coding skills as it has auto-detect mode. Octoparse works on both Windows and Mac devices, download it and follow the steps below to enjoy.
A video about web scraping with Octoparse
Steps to Scrape Web Data with Google Sheets Web Scraping Alternative
Step 1: Open Octoparse after the quick installation, 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.
Top 20 Web Crawling Tools for Extracting Web Data
Most popular posts
Posts by topic
