10 Ways to Get Data into Google Sheets | Part 3

Automation Dec 8, 2020

In this post we continue to cover ways to import data into Google Sheets.

These are:

Web scraping
Google Sheets Scripting
3-rd party built-in integrations

To see other seven ways, check Part1 and Part2.

8. Web scraping

Web scraping (aka web harvesting) is a process of data extraction from the web on to other formats. As a result, an amount of specific data is collected and copied to be retrieved or analyzed later. And this data can be gathered in a database or a spreadsheet, a Google Sheet.

Web scraping can come in handy for lead generation, market analysis and insights, research, creating lists of specific information, and many others. As always, it all depends on what you need the data for.

How it works

If you ever copied and pasted any kind of information from a website - congrats, you already did some web scraping! Well, of course, it's the simplest version of web scraping there is, but you get the main idea. Basically, a web scraping tool works in a pretty much the same way… Almost.

To put it simply, after a web scraping tool (or a bot) is given one or several URLs, it loads the entire HTML code of a page (advanced ones can render the whole website, including CSS and Javascript elements), and then extracts all the data (or just the specific one selected by the user). After that, the data collected is transferred to the one of the following formats: CSV, XLSX, or JSON.

Legal notice

Though web scraping exists for a very long time and is widely used all over the world, there's still no unambiguous answer to the question whether it is legal or not. In general, web scraping itself is legal. But it can violate the law depending on the kind of data that is scraped, the way it is extracted and to what purpose this data is going to be used.

If the data you want to extract is copyrighted or personal and falls under General Data Protection Regulation (GDPR), or you need to create an account and login to access the data, there might be legal consequences if you decide to proceed with web scraping.

Check this detailed guide for more information.

Simple scraping tool

Here are a few scraping tools that are simple yet efficient, and don't require coding.

Octoparse is a robust web scraping tool available both for Windows and Mac Os. It can extract the data to Google Sheet via API.

Parsehub is a great web scraper that supports collecting data from websites that use AJAX technologies, JavaScript, cookies and etc. It can extract the data to Google Sheet using IMPORTDATA function

Web scraper has a chrome extension and cloud extension. It allows to export the data in CSV, XLSX and JSON.

9. Google Sheets Scripting

Sometimes built-in functions are not enough and you need something more powerful. In that case, you can use Google Apps Script to create more complex functions and formulas of your own.

Google Apps Script is a cloud-based scripting language that can help you to automate various tasks with snippets of custom Javascript code.

It may require more technical knowledge, but even if you're not a developer or not familiar with JavaScript, Google has prepared documentation and guides to help you get started. And there is great active community where you can find the answers and get support.

10. 3rd party built-in integrations

Using special tools and add-ons dedicated to importing data into Google Sheets can also be an effective way to pull in the data automatically.

Here are a few of them:

Google Analytics’ Google Sheets add-on to import detailed information from Google Analytics into a Google Sheets.
Supermetrics to import business data like ad, finance and social into Google Sheets.
Twitter Archiever to search Twitter by hashtags or keywords and save matching tweets into Google Sheets.
Email Hunter to find and fill a Google Sheet with the email addresses at a specific company.

And there are more. Spend a few minutes to find the one (or a few) that meets your needs and save tons of time after setting it up. Check the add-on store for Google Sheets.

Next step: Create a website based on Sheets data

After the data is gathered in your Google Sheet there are ways to make it more visually appealing and easy to share. Of course, it will depend on your case.

For example, if you created a curated list in a Sheet, you can turn it into a beautiful website using one of the no code solutions, like SpreadSimple.