Cara menggunakan google sheets query regexreplace

Can 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
• Option#2: Grab price data with a simple formula: ImportXML
• Option#3: Another formula to get data with Google Sheets
• Option#4: Automatic web scraping tool without coding

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

Top 30 Big Data Tools for Data Analysis

Export Data to Google Sheets

Video: Create Your First Scraper with Octoparse 8.X