4 ways to import data from websites into Google Sheets

Data plays an important role in decision making and analysis. Whether you're a business analyst, researcher, or anyone who needs to process big data, the ability to efficiently enter data online into your spreadsheet is invaluable.

If you use Google Sheets to analyze data then you already know that copying and pasting data from websites is ineffective. In most cases, despite your efforts, the spreadsheet remains formatted incorrectly.

Luckily, there are many effective methods for importing data from websites into Google Sheets.

1. IMPORTDATA

You can use the IMPORTDATA function to simply import data from a CSV or TSV file online. This is the easiest way to import data online into Google Sheets because it requires minimal setup.

=IMPORTDATA(URL, delimeter, locale)

IMPORTDATA automatically fetches and imports data into your spreadsheet. In the syntax, URL is the address of the file, delimiter is the character used to parse the data in the file, and locale is the specific language that IMPORTDATA should use.

The last two arguments, delimiter and locale, are optional. If left blank, IMPORTDATA will look through the data and automatically assume these arguments. In most cases, it's best to leave these two arguments blank.

4 ways to import data from websites into Google Sheets Picture 1

For example, the formula below fetches data from the specified CSV file:

=IMPORTDATA("https://people.sc.fsu.edu/~jburkardt/data/csv/crash_catalonia.csv")

Because the delimiter and locale arguments are empty, IMPORTDATA automatically finds and assumes these values. Note that this function works with CSV and TSV files, not web pages. If you enter a website URL into IMPORTDATA, it may not return the expected data or may result in an error.

2. IMPORTHTML

You can use the IMPORTHTML function to import data from tables and lists on a web page. This function is a more practical alternative to IMPORTDATA because it allows you to import data tables from web pages without requiring a CSV file.

=IMPORTHTML(URL, query_type, index)

According to the syntax, URL is the address of the web page, query_type is the table or list, and index is the number of the table or list in the web page.

4 ways to import data from websites into Google Sheets Picture 2

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 formula, IMPORTHTML goes to the specified URL, fetches the data, and outputs it to the 8th table.

3. IMPORTXML

The IMPORTXML function fetches data from XML and HTML pages. The syntax of this function is as follows:

=IMPORTXML(URL, xpath_query)

In this syntax, URL is the address of the website and xpath_query is the XPath query that identifies the nodes you want to import. The prerequisite for using IMPORTXML in Google Sheets is to have a basic knowledge of what XML is and how you can navigate XML files using XPath queries.

4 ways to import data from websites into Google Sheets Picture 3

For example, the formula below outputs an XML table containing the names and descriptions of several tree types:

=IMPORTXML("https://www.w3schools.com/xml/plant_catalog.xml", "CATALOG/PLANT")

In this formula, IMPORTXML collects data in an XML file and then outputs to PLANT nodes that are children of the CATALOG node. Because the locale argument is empty, IMPORTXML uses the language of the XML file.

Note : Because web URLs can be quite messy, it's best to enter the URL in a separate cell and refer to that cell in your formula.

With knowledge of essential HTML tags and XPath queries, you can do more with IMPORTXML. For example, the formula below extracts all H2 headings in an article:

=IMPORTXML(B2, "//*/h2")

4 ways to import data from websites into Google Sheets Picture 4

4. Use the Google Sheets add-on to import data

In addition to built-in functions, Google Sheets also supports third-party add-ons that expand online data entry capabilities. These add-ons provide a convenient way to import data from online sources and enhance spreadsheet capabilities.

4 ways to import data from websites into Google Sheets Picture 5

The main advantage of these add-ons is that they allow access to resources that require authentication. Each add-on is best suited for a variety of data sources and types. Therefore, choosing the right Google Sheets add-on for data import depends on the type and source of your data.

Here is a selection of notable add-ons that support data import in Google Sheets:

1. : Coefficient is a user-friendly Google Sheets add-on designed to help you fetch data from websites without code. Through its interface, you can select specific data elements to import and enjoy real-time updates. Coefficient supports business systems such as Salesforce, Hubspot, Google Analytics, Redshift, Looker, Tableau, MySQL, etc.
2. : Coupler.io is another great add-on that simplifies data entry into Google Sheets. In addition to importing data from a variety of sources, Coupler.io allows you to schedule automatic imports to update your spreadsheet. Coupler.io works seamlessly with Google Analytics, Mailchimp, HubSpot, Salesforce, Shopify, Xero, Airtable, Trello, etc.
3. : Beyond just importing data, Awesome Table also allows you to customize and transform data to enhance your data analysis capabilities. Awesome panels let you select the data you want to import using visual data filters and schedule automatic refreshes. It works with QuickBooks, Xero, HubSpot, Airtable, Notion, YouTube, etc.

By taking advantage of these Google Sheets add-ons, you can import your data without needing to enter a long formula or know XML. Additional features, such as data conversion and scheduled import, enhance your workflow efficiency even further.

Micah Soto

Update 18 October 2023