How to Automate Data Entry in Google Sheets?

Are you manually copying and pasting data on your website to organize it into tables and lists? This manual process may be the easiest method when the amount of data is small. However, the larger the amount of data, the more time-consuming and labor-intensive it becomes.

If you have programming knowledge and skills, you can automatically extract it by web scraping using Python etc. But if you don't have programming knowledge, how can you efficiently retrieve data on the web?

Therefore, in this article, we will introduce two methods for acquiring web data that anyone can easily use.

1. How to Automate Data Entry in Google Sheets

2. How to scrape data with no-code tools

Each step is explained in an easy-to-understand manner, so let's try it right away!

What is web scraping

Web scraping is a computer software technique that automatically extracts certain information from websites. By using web scraping, you can search specific websites and databases on the Internet and automatically extract any data from a large amount of data.

In order to perform web scraping, it is necessary to create a scraper by programming such as Python or Ruby. However, it is not easy to learn programming from inexperienced. That's where spreadsheet functions and web scraping tools come in handy.

Automate data entry with Google Sheets

Here, I will introduce the procedure for building a simple web crawler that utilizes the "IMPORTXML function" of Google Sheets. The data collection site " Steam Spy " is used  as a data acquisition source .

Step 1 : Open a new Google Sheets .

Step 2: Open “ Steam Spy ” in your Chrome browser , right-click on the page and select “Verify” from the menu

Then the source code will be displayed, so click the "arrow icon" (red frame at the bottom left of the screen) to enable the selector.  

With selectors enabled, place the cursor where required and the corresponding information will be displayed in the Validation Panel. Select "PRICE" here.

Step 3: Paste the Steam Spy URL into your spreadsheet. Here we specify cell A2.

Step 4: Get the Xpath

This time, we will use the "IMPORTXML function" to automatically acquire the price data. The IMPORTXML function is a function that allows you to specify necessary information from a website and automatically output that information to a spreadsheet.

First, copy the Xpath that will be the element. Xpath is a language that specifies a specific part from a document conforming to the markup language XML. If you want to know more about Xpath, please see the following articles.

To get the Xpath, click "Pricing Info > Copy > Copy XPath".

If you paste it in a spreadsheet, you can see that the Xpath "//*[@id=”trendinggames”]/tbody/tr[1]/td[4]" has been obtained.