Summary
- Easily import online data into Google Sheets using built-in IMPORT functions for accurate analysis.
- Utilize IMPORTDATA for CSV files, IMPORTHTML for tables, IMPORTFEED for RSS, and IMPORTXML for XML data.
- Try Google Sheets add-ons like Coefficient, Coupler.io, and Awesome Table for seamless data importing.
Importing online data into Google Sheets is a vital task for business analysts, researchers, and anyone dealing with big data. Copying and pasting from websites is inefficient and error-prone, but there are better ways.
Importing Data From a Website to Google Sheets
Google Sheets offers several methods to import data from websites. You can use the built-in IMPORT functions or install Google Sheets add-ons to import data effortlessly. The IMPORT family of functions consists of IMPORTDATA, IMPORTHTML, IMPORTXML, and IMPORTFEED. Each function is best suited for importing specific types of data.
These functions can't access secure information using credentials or security tokens. In such cases, you'll need to install a Google Sheet add-on to access the service and import the data into your spreadsheet.
1. IMPORTDATA
You can use the IMPORTDATA function to import data from online CSV or TSV files. This is the easiest way to import data from the web into Google Sheets since it requires minimal setup.
=IMPORTDATA(URL, delimeter, locale)IMPORTDATA fetches and imports data into your spreadsheet. In the syntax, URL is the file's address, delimiter is the character that separates each field in the file, and locale is the specific locale that IMPORTDATA should use.
The last two arguments, delimiter and locale, are optional. IMPORTDATA will look through the data and assume values for these arguments if you leave them blank. In most cases, it's best to leave these two arguments blank.
For example, you can import the US state names and their abbreviations from a CSV file using this formula:
=IMPORTDATA("https://people.sc.fsu.edu/~jburkardt/data/csv/crash_catalonia.csv")
Since the delimiter and locale arguments are blank, IMPORTDATA assumes these values. Note that this function works with CSV and TSV files, not webpages. If you input a webpage's URL in IMPORTDATA, it may not return the expected data or result in an error.
2. IMPORTHTML
You can use the IMPORTHTML function to import data from tables and lists on a website. This function is a more practical alternative to IMPORTDATA since it enables importing data tables directly from web pages without a CSV file.
=IMPORTHTML(URL, query_type, index)In the syntax, URL is the webpage's address, query_type is either table or list, and index is the table or list's number in the webpage.
For instance, you can use IMPORTHTML to import a table from a Wikipedia article:
=IMPORTHTML("https://en.wikipedia.org/wiki/Demographics_of_Germany", "table", 8)In this formula, IMPORTHTML goes to the specified URL, fetches the data, and outputs the eighth table.
Once you've imported the data, you can analyze it using everything that Google Sheets offers. For example, you can format the spreadsheet or create a chart to visualize the data.
3. IMPORTFEED
The IMPORTFEED function lets you import data from RSS or Atom feeds directly into Google Sheets. RSS or Atom feeds are essentially channels that deliver updates from websites or blogs in a standard format.
=IMPORTFEED(url, [query], [headers], [num_items])IMPORTFEED is relatively simple to use, requiring only the feed URL and optional parameters for filtering and formatting. For instance, the formula below imports the MakeUseOf feed into Google Sheets:
=IMPORTFEED("https://www.makeuseof.com/feed/", ,TRUE)
Note that the query and num_items parameters are left as their defaults. The headers parameter is set to TRUE, adding a header row to the imported table.
IMPORTFEED automatically updates your spreadsheet whenever there's a new item in the feed.
4. IMPORTXML
IMPORTXML is a powerful IMPORT function that lets you import data from an XML file or a webpage. It lets you extract almost any part of the data, but it's a bit more complex than other import functions. The syntax for this function is as follows:
=IMPORTXML(URL, xpath_query)In this syntax, URL is the webpage's address, and xpath_query is the XPath query identifying the nodes you want to import. A prerequisite to using IMPORTXML in Google Sheets is a basic knowledge of XML and how to navigate XML files using XPath queries.
You can learn more about XPath queries from our in-depth article on IMPORTXML.
As an example, this formula outputs an XML table containing the names and descriptions of some plants:
=IMPORTXML("https://www.w3schools.com/xml/plant_catalog.xml", "CATALOG/PLANT")In this formula, IMPORTXML collects the data in the XML file and then outputs the PLANT nodes that are children of a CATALOG node. Since the locale argument is blank, IMPORTXML uses the XML file's locale.
Since web URLs can get quite messy, it's best if you input the URL in a separate cell and reference that cell in your formula.
With a knowledge of essential HTML tags and XPath queries, you can do a lot more with IMPORTXML. For instance, the formula below extracts all the H2 headings in an article:
=IMPORTXML(B2, "//*/h2")
A crucial limitation of IMPORTXML is its inability to parse JavaScript. If the data you aim to import is dynamically generated through JavaScript, IMPORTXML will likely encounter errors.
5. Use Google Sheets Add-Ons to Import Data
In addition to the built-in functions, Google Sheets supports third-party add-ons that extend its capabilities for importing online data. These add-ons offer a convenient way to import data from online sources and supercharge your spreadsheet's capabilities.
The main advantage of these add-ons is that they enable access to resources that require authentication. Each add-on is best suited for different sources and data types. Thus, choosing the right Google Sheets add-on to import your data depends on the type and origin of your data.
Here's a selection of notable add-ons that facilitate data import in Google Sheets:
- Coefficient: Coefficient is a user-friendly Google Sheets add-on designed to help you fetch data from websites without coding. Through its interface, you can select specific data elements to import and enjoy real-time updates. Coefficient supports business systems like Salesforce, Hubspot, Google Analytics, Redshift, Looker, Tableau, MySQL, and more.
- Coupler.io: Coupler.io is another excellent add-on that makes it easier to import data into Google Sheets. Aside from importing data from various sources, Coupler.io enables you to schedule automatic imports to keep your spreadsheet up-to-date. Coupler.io seamlessly works with Google Analytics, Mailchimp, HubSpot, Salesforce, Shopify, Xero, Airtable, Trello, and more.
- Awesome Table: Beyond just importing data, Awesome Table allows you to customize and transform the data to enhance your data analysis. Awesome Table lets you choose the data to import with intuitive data filters and schedule an automatic refresh. It works with QuickBooks, Xero, HubSpot, Airtable, Notion, YouTube, and more.
No matter what sort of data you deal with, copy-pasting it from online sources into your spreadsheet doesn't feel right. Luckily, Google Sheets has built-in functions and an array of third-party add-ons to facilitate data import, and now you know how to use them.