Importing data from websites into Google Sheets can be a quick and efficient way to analyze and use information more broadly. Whether you’re collecting statistics or tracking product prices, it’s a vital task for business analysts, researchers, and anyone who works with big data. Copying and pasting from websites is inefficient and error-prone, but there are better ways.
There are simple tools and steps that can help you bring this data into your spreadsheets easily, saving you time and effort. Check out how to effectively analyze data and make smart decisions using Google Sheets.
Import data from a website into Google Sheets
Google Sheets offers several ways to import data from websites. You can use the built-in IMPORT functions or install custom Google Sheets add-ons to import data effortlessly. The IMPORT function set consists of IMPORTDATA, IMPORTHTML, IMPORTXML, and IMPORTFEED. Each function is best suited for importing specific types of data.
These functions cannot access information secured using credentials or security tokens. In such cases, you will need to install a Google Sheets add-on to access the service and import data into your spreadsheet.
1. IMPORTDATA
You can use the IMPORTDATA function to import data from CSV or TSV files online. This is the easiest way to import data from the web into Google Sheets because it requires minimal setup.
=IMPORTDATA(URL, delimeter, locale)
IMPORTDATA fetches and imports data into your spreadsheet. In syntax, URL is the address of the file, delimiter is the character that separates each field in the file, and locale is the specific location 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 is best to leave these arguments blank.
For example, you can import US state names and abbreviations from a CSV file using the following function:
=IMPORTDATA("https://people.sc.fsu.edu/~jburkardt/data/csv/crash_catalonia.csv")
Because the separator and locale arguments are empty, IMPORTDATA assumes these values. Note that this function works with CSV and TSV files, not web pages. If you enter a web page URL in IMPORTDATA, it may not return the expected data or cause 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 because it allows you to import data tables directly from web pages without a CSV file.
=IMPORTHTML(URL, query_type, index)
In syntax, URL is the address of the web page, query_type is either a table or list, and index is the number of the table or list in the web page.
For example, 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 function, IMPORTHTML navigates to the specified URL, fetches the data, and outputs the eighth table.
Once you’ve imported your data, you can analyze it using everything Google Sheets has to offer. For example, you can format your spreadsheet or create a chart to visualize your data. Check out how to use ChatGPT on Google Sheets with GPT for Sheets™ and Docs™.
3. IMPORTFEED
The IMPORTFEED function lets you import data from RSS or Atom feeds directly into Google Sheets. RSS or Atom feeds are basic channels that deliver updates from websites or blogs in a standard format.
=IMPORTFEED(url, (query), (headers), (num_items))
IMPORTFEED feeds are relatively easy to use, requiring only the feed URL and optional parameters for filtering and formatting. For example, the function below imports a MakeUseOf feed into Google Sheets:
=IMPORTFEED("https://www.makeuseof.com/feed/", ,TRUE)
Note that the query parameters and number of items are left as default. The headers parameter is set to TRUE, which adds a header row to the imported table.
advice: IMPORTFEED automatically updates your spreadsheet whenever there is a new item in the feed.
4. IMPORTXML
IMPORTXML is a powerful import function that lets you import data from an XML file or web page. It lets you extract almost any piece of data, but it is 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 address of the web page, and xpath_query is the XPath query that identifies the nodes you want to import. The prerequisite for using IMPORTXML in Google Sheets is basic knowledge of XML and how to navigate XML files using XPath queries.
For example, this function 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 the CATALOG node. Since the locale argument is empty, IMPORTXML uses the locale of the XML file.
advice: Since web URLs can be very messy, it's best to enter the URL in a separate cell and refer to that cell in the formula.
Knowing basic HTML tags and XPath queries, you can do a lot with IMPORTXML. For example, the function below extracts all the H2 headings in an article:
=IMPORTXML(B2, "//*/h2")
A critical limitation of IMPORTXML is its inability to parse JavaScript. If the data you are trying to import is dynamically generated through JavaScript, IMPORTXML is likely to 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 to import data online. These add-ons provide a convenient way to import data from online sources and enhance the capabilities of your spreadsheet. Check out the best dynamic add-ons for Google Sheets to better visualize your data.
The main advantage of these add-ons is that they enable access to resources and items that require authentication. Each add-on is better suited for different data sources and types. Therefore, choosing the right Google Sheets add-on to import your data depends on the type and source of your data.
Here are a selection of notable add-ons that make it easy to import data into Google Sheets:
- CoefficientCoefficient is an easy-to-use Google Sheets add-on designed to help you fetch data from websites without any programming. 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.ioCoupler.io is another great plugin that makes it easy to import data into Google Sheets. Aside from importing data from different sources, Coupler.io lets you schedule automatic imports to keep your spreadsheet up to date. Coupler.io works seamlessly with Google Analytics, Mailchimp, HubSpot, Salesforce, Shopify, Xero, Airtable, Trello, and more.
- Awesome Table: Beyond just importing data, Awesome Table lets you customize and transform data to improve your data analysis. Awesome Table lets you choose which data to import using intuitive data filters and scheduling an automatic update. It works with QuickBooks, Xero, HubSpot, Airtable, Notion, YouTube, and more.
No matter what type of data you’re working with, copying and pasting it from online sources into your spreadsheet just doesn’t feel right. Fortunately, Google Sheets has built-in functions and a bunch of third-party add-ins to make importing data easy, and now you know how to use them. Learn how to unformat a table in Excel to better organize your data.
Get IPTV Free Trial Now