How to Import Data With Google Sheets Functions

You may want to work with data in your spreadsheet that resides elsewhere. Using a set of Google Sheets functions, you can import data from a CSV file, RSS feed, web page, or another spreadsheet.

With the functions we’ll describe here, you can pull data into your sheet from external sources. Then, analyze, manipulate, format, and do what you please with your new data.

IMPORTDATA for a CSV or TSV File

If you see a CSV or TSV file on a website you’d like to import, you can use the IMPORTDATA function.

RELATED: What Is a CSV File, and How Do I Open It?

The syntax for the function is IMPORTDATA(reference, delimiter, locale) where only the first argument is required as the URL or a cell reference. If you want to use a different delimiter than the default file type, use the delimiter argument. And if you need to change the language, use the locale argument with the region’s code.

Here, we’ll import a CSV file using the URL with this formula:

=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv")

IMPORTDATA function in Google Sheets

In this example, we add the delimiter argument rather than using the default (comma) for the CSV file:

=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")

IMPORTDATA function with the delimiter argument

IMPORTFEED for an RSS or ATOM Feed

Maybe there’s an RSS or ATOM feed that you want to pull data from to manipulate it in your sheet. You’ll use the IMPORTFEED function.

RELATED: What Is RSS, and How Can I Benefit From Using It?

The syntax for the function is IMPORTDATFEED(reference, query, headers, number_items) where only the first argument is required, and you can use the URL or a cell reference.

  • Query: Enter the default “items” or use “feed” for a single row of data, “feed [type]” for a certain feed element, or “items [type]” for a certain item element.
  • Headers: The default is FALSE, but you can use TRUE to include a header row.
  • Number_items: The default is all items in the feed, but you can enter a specific number of items.

To import our How-To Geek feed with five items, you can use this formula:

=IMPORTFEED("https://www.howtogeek.com/feed","items",,5)

IMPORTFEED function for a number of items

Using this next formula, you can import five items from the same feed and include the header row:

=IMPORTFEED("https://www.howtogeek.com/feed","items",TRUE,5)

IMPORTFEED function with headers

For one more example, using the same feed, we’ll import just the titles for five items using this formula:

=IMPORTFEED("https://www.howtogeek.com/feed","items title",,5)

IMPORTFEED with titles only

IMPORTHTML for a Table or List on a Web Page

Tables and lists from a web page (HTML) are easy to import into Google Sheets with the IMPORTHTML function.

RELATED: What Is HTML?

The syntax for the function is IMPORTHTML(reference, query, index) where you may want to use all three arguments depending on the page. Enter a URL or cell for the reference, “table” or “list” for the query, and a number for the index. The index is the identifier in the page’s HTML for the table or list if there’s more than one.

As an example, we’ll import the first table on a Wikipedia page for Star Wars films using this formula:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","table",1)

IMPORTHTML for the first table on a page

When you view the web page, you can see this first table is the one on the top right.

First table on a page

Because it’s the next table on that page we really want, we’ll include the next index number instead with this formula:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","table",2)

IMPORTHTML for the second table on a page

Now we have the table shown below in our Google Sheet instead.

Second table on a page

For one more example, we’ll import a list from that same page. This is the third list identified on the page which is the contents of the article. Here’s the formula:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","list",3)

IMPORTHTML for the third list on a page

IMPORTRANGE for a Cell Range in a Spreadsheet

One more handy import function is for bringing in data from another spreadsheet. Although it’s easy enough to pull data from a sheet in the same workbook, you might want data from a different workbook. For this, you can use the IMPORTRANGE function.

RELATED: How to Import Data from Another Google Sheet

The syntax for the function is IMPORTRANGE(reference, sheet_range) where you’ll need both arguments. Enter the URL for the sheet in quotes or use a cell reference. Then, include the sheet name and cell range as a string or a cell reference, both should be in quotation marks.

When you first enter a formula for the IMPORTRANGE function, you’ll likely see an error like the one below. This is simply to alert you that you need to allow access to the sheet you want to import. Select “Allow Access” to continue.

IMPORTRANGE access message

In this example, we’ll import the range A1 through E7 from another workbook. This workbook only has one sheet, so the import is successful without the sheet name. Here’s the formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/mysheet/edit","A1:E7")

IMPORTRANGE function in Google Sheets

For the next example, we’re importing from another workbook that has multiple sheets. So, you’d include the sheet name and cell range as a single string: Sales!D1:F13

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/mysheet/edit#gid=111525310","Sales!D1:F13")

IMPORTRANGE function with the sheet name

These Google Sheets import functions can come in super handy when you need external data like the types mentioned here. Keep in mind that if you want to import a specific type of file from your computer, such as a Microsoft Excel workbook, you can do that with the Google Sheets menu.

RELATED: How to Import an Excel Document into Google Sheets

Leave a Reply

Your email address will not be published.