December 27, 2020

How to Use Google Sheets IMPORT Functions [IMPORTDATA, IMPORTFEED, & More!]

Chart with arrows pointing up and down
SECTIONS
  1. IMPORTDATA
  2. IMPORTFEED
  3. IMPORTHTML
  4. IMPORTRANGE
  5. Summary, plus a sample sheet

A lot of data is available over the internet in various formats. Fortunately, Google Sheets has robust functions to import them to your spreadsheet. These functions are the IMPORTDATA, IMPORTFEED, IMPORTHTML, and IMPORTRANGE. We will learn how to use them in this tutorial. 

IMPORTDATA

One of the simplest data file formats available over the Internet is the CSV file, where the acronym means “comma-separated values.” It is simply a file containing entries in a table, with cells separated by commas and rows separated by line breaks. A CSV file can look like this:

A portion of a CSV file.
A portion of a CSV file.

The format is simple but is nonetheless effective in storing arrays of data. CSV is still a popular format for storing data as CSV files are readable in simple apps such as Notepad. 

Another related format is the TSV file, where the T stands for “tab”. Instead of a comma separating the cells, the tab does the job and makes it more readable since the file is formatted visually like an actual table, minus the cell borders. A TSV file can look like this:

A portion of a TSV file. 
A portion of a TSV file. With a bit of more proper formatting, this will look like an actual table. 


Both CSV and TSV files hosted over the internet can be directly imported to Google Sheets using the IMPORTDATA function. The syntax is as follows:

=IMPORTDATA(“URL”)

... where URL is the URL to the hosted CSV or TSV file. 

You can use IMPORTDATA for publicly-available data, as a lot of them are encoded in CSV format. 

For our example, we want to import the population change data from Census.gov:

Data in CSV format available in Census.gov.
Data in CSV format available in Census.gov.

The data is in csv format. We simply copy the link address, and then we go to Google Sheets to use the IMPORTDATA function: =IMPORTDATA("http://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-alldata.csv")

The result looks like this:

IMPORTDATA importing a CSV file to a sheet. 
IMPORTDATA importing a CSV file to a sheet. 

That’s it! IMPORTDATA is as simple as that.

IMPORTFEED

RSS and ATOM feeds are a format for delivering news from a source to a device. You can subscribe to a certain RSS or ATOM feed to stay updated. 

You can use the IMPORTFEED function to spice up your dashboard a bit. Here is the syntax of the IMPORTFEED function:

IMPORTFEED(URL, [query], [headers], [num_items])

... where URL is the URL of the feed; query specifies what to fetch from the feed (by default, it’s the items of the feed); headers specify whether to include the feed headers or not; and num_items to limit the number of items loaded in the sheet.

As an example, we want to import the latest news from CNBC using the IMPORTFEED function: =importfeed("http://www.cnbc.com/id/19746125/device/rss/rss.xml")

Our sheet now looks like this:

IMPORTFEED importing the latest news headlines with their summary and timestamp.
IMPORTFEED importing the latest news headlines with their summary and timestamp.

You may need to do some adjustments to your sheet to make the feed look more aesthetically pleasing.

IMPORTHTML

Sometimes the relevant data is presented as a table in the webpage that is being updated regularly. Fortunately, Google Sheets has the IMPORTHML function that can scan a webpage for a table or a list and then import them to the sheet. The syntax of the function is as follows:

IMPORTHTML(URL, query, index)

... where URL is the URL of the webpage; query can either be a “list” or a “table”, but must be specified; and index is to help the function identify which table or list should be imported, especially if the webpage has more than 1 table or list. 

As an example, we want to import the current temperatures in Canadian cities to our sheet:

The latest weather in Canadian cities, listed on a table. 
The latest weather in Canadian cities, listed on a table. 

We can use the IMPORTHTML function: =importhtml("https://weather.gc.ca/canada_e.html","table","1")

The result looks like this:

The latest weather in Canadian cities, now imported to the sheet. 
The latest weather in Canadian cities, now imported to the sheet. 

IMPORTRANGE

Finally, the IMPORTRANGE function allows you to import data from another Google Sheet, as long as you have access to it. It has the following syntax:

importrange("url_of_workbook","data_range")

Both the URL and the data range should be enclosed by quotation marks. The workbook must be either a public workbook or that you have access to it. If the workbook you are including is also yours, a prompt will pop up, asking you to connect the sheets. Click Allow access. 

A pop-up prompt by Google Sheets to allow access to another Google Sheets worksheet. Statement: You need to connect these sheets. 
A pop-up prompt by Google Sheets to allow access to another Google Sheets worksheet.


Afterward, the data will be loaded. 

If the sheets are located in the same workbook (thus having the same URL), they still need to be added through the IMPORTRANGE function individually. 

Summary, plus a sample sheet

After reading this tutorial, you are now ready to extract more data from the Internet to your sheet! 

If you need some help, here is a sample sheet you can look on.

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.