- What is IMPORTDATA in Google Sheets?
- IMPORTDATA Function in Google Sheets
- How to Use the Google Sheets IMPORTDATA Function
- Google Sheets IMPORTDATA Not Working
- Import Data Automatically with Sheetgo
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. You will also learn about Sheetgo, an alternative that allows you to avoid these limitations and set up custom workflows to automate data transfers between your files.
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 Sheetgo, a tool that allows you to synchronize and manage your data across a variety of formats and locations.
Import Data Automatically with Sheetgo
Using Sheetgo, you can instantly import data from CSV/TSV files to your spreadsheets. Quickly connect your files and schedule updates to ensure your data is automatically refreshed. Using the “Append data” feature, you can easily keep track of historical data. To learn more about this, read our blog post on how to import CSV to Google Sheets automatically or watch this video.
If you work with Excel, check out this blog post to learn how to use Sheetgo to import CSV data to Excel automatically. If you prefer, it’s also available as a video tutorial.
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. Sign up for Sheetgo today to start saving time.