- What is IMPORTDATA in Google Sheets?
- IMPORTDATA Function in Google Sheets
- How to Use the Google Sheets IMPORTDATA Function
- Google Sheets IMPORTDATA Not Working
- How to import data into Google Sheets with Layer?
Google Sheets offers various functions that allow you to import data in different formats, from different sources. In other articles, you can learn how to use IMPORTXML or IMPORTHTML to import tables and lists from the web into your Google Sheets.
If you work with comma-separated or tab-separate value files, the IMPORTDATA function is a good option. For example, it allows you to import CSV and TSV files stored online, using the URL.
In this article, you will learn about the Google Sheets IMPORTDATA function, as well as how to use it. Additionally, you will learn about some of its limitations and how to deal with common problems.
What is IMPORTDATA in Google Sheets?
The IMPORTDATA function in Google Sheets lets you import comma-separated values (CSV) and tab-separated values (TSV) files, using the URL where the file is stored. If you have CSV or TSV files stored online, you can quickly import your data into Google Sheets.
IMPORTDATA Function in Google Sheets
Now that you know what IMPORTDATA is, here is the formula syntax you’ll need to use in Google Sheets.
IMPORTDATA Formula Syntax
The function has only one required parameter:
=IMPORTDATA(url*, delimiter, locale)
- url refers to a required argument, and it defines the URL where the file is stored.
- delimiter is used to specify the delimiter to be used when parsing data. If left blank, the function will infer the delimiter from the file extension - “comma” for .csv, and “tab” for .tsv.
- locale is used to determine the language and region locale code that should be used. However, if left blank, the function will use the document’s locale.
NOTE: If you type the URL directly into the formula, you need to use double quotation marks:
If you type the URL into a cell and use the cell reference in the formula, the quotation marks aren’t required.
How To Use IMPORTRANGE Function In Google Sheets?
Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here's how to use the IMPORTRANGE function in Google SheetsREAD MORE
How to Use the Google Sheets IMPORTDATA Function
To start using the formula, follow these steps and apply them to your case.
- 1. Open Google Sheets and type the URL into a cell.
- 2. In another cell, type in the formula and use the cell with the URL as input.
- 3. That’s it. The data is in your spreadsheet.
Google Sheets IMPORTDATA Not Working
As you have seen, it is very easy to use the IMPORTDATA function. However, there are some known errors and limitations.
Limitation on Updates
While you can always force a refresh by deleting and retyping the formula, there is no native functionality to customize the frequency of automatic updates. Functions that import external data - IMPORTDATA, IMPORTHTML, IMPORTFEED, and IMPORTXML - automatically update every hour. There is simply no way to change this without using a script.
Sometimes you have to reference or merge data from multiple sheets or spreadsheets. Here's how to easily link multiple Google SheetsREAD MORE
Limitation on Number of URLs
Unfortunately, it is not possible to import from multiple URLs at the same time, so you’ll need a separate instance of the formula for each URL. While it is technically possible to use this function up to 50 times per spreadsheet, the reality is that the Sheet can become extremely slow and unresponsive.
Problem with File Size
As you can see in the screenshot below, there is a limitation regarding the size of the file that can be imported. You will see an error message saying the “resource at URL contents exceeded maximum size.” The exact limit is unclear, but it’s lower than if you were importing the CSV file directly.
If you come across this issue, try downloading the file from the URL and importing it directly to Sheets.
Problem with Sheet Size
The function will not automatically increase the number of columns if necessary. You will see an error message saying the “result was not automatically expanded. Please insert more columns.” This means you need to check the number of columns in the source file to ensure that you have enough in your spreadsheet.
In the next section, you will learn about Layer, a tool that allows you to synchronize and manage your data across a variety of formats and locations.
How to import data into Google Sheets with Layer?
Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.
Using Layer, you can:
- Share & Collaborate: Automate your data collection and validation through user controls.
- Automate & Schedule: Schedule recurring data collection and distribution tasks.
- Integrate & Sync: Connect to your tech stack and sync all your data in one place.
- Visualize & Report: Generate and share reports with real-time data and actionable decisions.
Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!
As you have seen, the Google Sheets IMPORTDATA function can be used to import CSV and TSV files to your spreadsheet. However, there are some limitations to its use, particularly if you intend to use it frequently or use multiple instances.
You now know how to use the IMPORTDATA function to get your CSV and TSV data into Google Sheets. You also know about a tool that allows you to synchronize and manage your data across multiple file formats, including CSV and TSV files: Layer.
Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.GET STARTED FOR FREE