Here’s how to retrieve data from a webpage with Google Sheets

Welcome to TNW Basics, a collection of tips, guides, and advice on how to get the most out of your gadgets, apps, and more.

Data scraping is all the rage these days. But what many don’t know is that you don’t have to be a sophisticated hacker to be able to collect data from websites. In fact, you don’t even need coding skills.

A multitude of tools such as browser extensions exist to alleviate the technical knowledge required. But even if this is too much of an obstacle for you, don’t worry. Google will come to the rescue. Google Sheets to be precise.

[Read: This fancy Google Sheets formula is the coolest function you’ll never use]

It has a nifty little formula that lets you enter a webpage’s list or table of data into any sheet of your choice. It’s called importhtml, and it works like this:

Find a website you want to extract data from

As an example, let’s go for the List of the largest technology companies by revenue Wikipedia page.

Identify a table or list that you want to retrieve

In this case, let’s go for the second table on the page, in the ‘2019 List’ section.

Now type the following in a cell of your choice, say A1

= IMPORTHTML (“https://en.wikipedia.org/wiki/List_of_largest_technology_companies_by_revenue”, “table”, 2)

As you can see, you will need to declare three things in the formula: the URL from which you want to retrieve the data, the data type (table or list) and the position (in this case the second table, so 2 ).

Press Enter, and voila, the table appears in your sheet:

To take it to the next level and actually transform or cleanse this data, first make sure that it becomes static instead of linked data. To do this, select the table, right click on cell A1, ‘Paste special’> ‘Paste values ​​only’.

So there you go, have fun playing around with the data in Google Sheets!


Source link

Comments are closed.