You could need to work with knowledge in your spreadsheet that resides elsewhere. Utilizing a set of Google Sheets features, you’ll be able to import knowledge from a CSV file, RSS feed, net web page, or one other spreadsheet.
With the features we’ll describe right here, you’ll be able to pull knowledge into your sheet from exterior sources. Then, analyze, manipulate, format, and do what you please along with your new knowledge.
IMPORTDATA for a CSV or TSV File
In the event you see a CSV or TSV file on a web site you’d prefer to import, you should use the IMPORTDATA perform.
RELATED: What Is a CSV File, and How Do I Open It?
The syntax for the perform is IMPORTDATA(reference, delimiter, locale) the place solely the primary argument is required because the URL or a cell reference. If you wish to use a unique delimiter than the default file kind, use the delimiter argument. And if it’s worthwhile to change the language, use the locale argument with the area’s code.
Right here, we’ll import a CSV file utilizing the URL with this components:
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv")
On this instance, we add the delimiter argument slightly than utilizing the default (comma) for the CSV file:
=IMPORTDATA("https://www.bls.gov/cew/classifications/aggregation/agg-level-titles-csv.csv",".")
IMPORTFEED for an RSS or ATOM Feed
Possibly there’s an RSS or ATOM feed that you simply need to pull knowledge from to govern it in your sheet. You’ll use the IMPORTFEED perform.
RELATED: What Is RSS, and How Can I Profit From Utilizing It?
The syntax for the perform is IMPORTDATFEED(reference, question, headers, number_items) the place solely the primary argument is required, and you should use the URL or a cell reference.
- Question: Enter the default “gadgets” or use “feed” for a single row of knowledge, “feed [type]” for a sure feed component, or “gadgets [type]” for a sure merchandise component.
- Headers: The default is FALSE, however you should use TRUE to incorporate a header row.
- Number_items: The default is all gadgets within the feed, however you’ll be able to enter a selected variety of gadgets.
To import our How-To Geek feed with 5 gadgets, you should use this components:
=IMPORTFEED("https://www.howtogeek.com/feed","gadgets",,5)
Utilizing this subsequent components, you’ll be able to import 5 gadgets from the identical feed and embody the header row:
=IMPORTFEED("https://www.howtogeek.com/feed","gadgets",TRUE,5)
For another instance, utilizing the identical feed, we’ll import simply the titles for 5 gadgets utilizing this components:
=IMPORTFEED("https://www.howtogeek.com/feed","gadgets title",,5)
IMPORTHTML for a Desk or Listing on a Net Web page
Tables and lists from an online web page (HTML) are simple to import into Google Sheets with the IMPORTHTML perform.
RELATED: What Is HTML?
The syntax for the perform is IMPORTHTML(reference, question, index) the place you could need to use all three arguments relying on the web page. Enter a URL or cell for the reference, “desk” or “checklist” for the question, and a quantity for the index. The index is the identifier within the web page’s HTML for the desk or checklist if there’s a couple of.
For example, we’ll import the primary desk on a Wikipedia web page for Star Wars movies utilizing this components:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","desk",1)
While you view the net web page, you’ll be able to see this primary desk is the one on the highest proper.
As a result of it’s the following desk on that web page we actually need, we’ll embody the following index quantity as an alternative with this components:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","desk",2)
Now we’ve the desk proven beneath in our Google Sheet as an alternative.
For another instance, we’ll import a listing from that very same web page. That is the third checklist recognized on the web page which is the contents of the article. Right here’s the components:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_Star_Wars_films","checklist",3)
IMPORTRANGE for a Cell Vary in a Spreadsheet
Another helpful import perform is for bringing in knowledge from one other spreadsheet. Though it’s simple sufficient to drag knowledge from a sheet in the identical workbook, you may want knowledge from a unique workbook. For this, you should use the IMPORTRANGE perform.
RELATED: The way to Import Information from One other Google Sheet
The syntax for the perform is IMPORTRANGE(reference, sheet_range) the place you’ll want each arguments. Enter the URL for the sheet in quotes or use a cell reference. Then, embody the sheet title and cell vary as a string or a cell reference, each needs to be in citation marks.
While you first enter a components for the IMPORTRANGE perform, you’ll possible see an error just like the one beneath. That is merely to provide you with a warning that it’s worthwhile to enable entry to the sheet you need to import. Choose “Enable Entry” to proceed.
On this instance, we’ll import the vary A1 by E7 from one other workbook. This workbook solely has one sheet, so the import is profitable with out the sheet title. Right here’s the components:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/mysheet/edit","A1:E7")
For the following instance, we’re importing from one other workbook that has a number of sheets. So, you’d embody the sheet title and cell vary as a single string: Gross sales!D1:F13
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/mysheet/edit#gid=111525310","Gross sales!D1:F13")
These Google Sheets import features can are available tremendous helpful once you want exterior knowledge like the categories talked about right here. Understand that if you wish to import a selected kind of file out of your pc, reminiscent of a Microsoft Excel workbook, you are able to do that with the Google Sheets menu.
