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 may use the built-in IMPORT functions orinstall Google Sheets add-onsto 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.

Using IMPORTDATA to import CSV files in Google Sheets

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 fetches and imports data into your spreadsheet. In the syntax,URLis the file’s address,delimiteris the character that separates each field in the file, andlocaleis the specific locale that IMPORTDATA should use.

Sample table from Wikipedia

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:

Using IMPORTHTML in Google Sheets

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.

In the syntax,URLis the webpage’s address,query_typeis eithertableorlist, andindexis the table or list’s number in the webpage.

Using IMPORTFEED in Google Sheets

For instance, you may use IMPORTHTML to import a table from a Wikipedia article:

In this formula, IMPORTHTML goes to the specified URL, fetches the data, and outputs the eighth table.

Using IMPORTXML in Google Sheets

Once you’ve imported the data, you can analyze it using everything that Google Sheets offers. For example, you canformat the spreadsheetorcreate a chartto 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 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:

Note that thequeryandnum_itemsparameters are left as their defaults. Theheadersparameter is set toTRUE, 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:

In this syntax,URLis the webpage’s address, andxpath_queryis the XPath query identifying the nodes you want to import. A prerequisite to using IMPORTXML in Google Sheets is abasic knowledge of XMLand how to navigate XML files using XPath queries.

You can learn more about XPath queries fromour in-depth article on IMPORTXML.

As an example, this formula outputs an XML table containing the names and descriptions of some plants:

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 ofessential HTML tagsand XPath queries, you can do a lot more with IMPORTXML. For instance, the formula below extracts all the H2 headings in an article:

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:

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.