Manually entering data from external sources into any spreadsheet is a really challenging job. It will not just take an enormous amount of time, but manual integration is also severely prone to errors. But now, you can easily import data in Google Sheets by using the IMPORTDATA function.
IMPORTDATA function lets you integrate data from any publicly available CSV or TSV files. You can either import the complete data from an external file or import a particular set of data from the file. Besides, you can even refer to a cell in this function where you have placed any link.
But using this function is a bit tricky. And it is even more challenging if you want to combine it with other functions. IMPORTDATA has some limitations too. So, without any further ado, let’s understand what this function is all about and how you can use it to easily import and integrate data.
How To Use IMPORTDATA In Google Sheets?
IMPORTDATA is one of the important functions to learn if you don’t know how to import data the right way into Google Sheets. This function lets you import data from publicly available CSV and TSV files.
It can be extremely helpful if you are trying to get data from any online place, as you don’t need to do it manually.
Definition And Purpose Of IMPORTDATA
IMPORTDATA function in Google Sheets works as the importer of any CSV or TSV available in public domains (even password-protected ones). For any given URL, this function can fetch and import the values of the cells right onto your spreadsheet on GS.
It is also instrumental in analytical studies where you need to import tons of data from public domains. This function remains useful in reporting as well.
But you also need to understand that the IMPORTDATA function doesn’t precisely “import” the data from the CSV/TSV file. Instead, it refers to the data from the specific file in your Google Sheets.
Bonus: Importing formulas is a tough job in Google Sheets. It is even tougher if you want to implement the formula in an entire column. Check out my newest guide on applying formulas to entire column in Google Sheets to do it the easy way.
IMPORTDATA Syntax And Parameters
Before you understand how to use IMPORTDATA in Google Sheets, you must know the correct syntax. There are just three parameters in the IMPORTDATA function.
The actual formula of this function is “=IMPORTDATA(<URL of the file>,<delimiter>,<locale>)” in GS.
Let’s understand what these three parameters are.
- URL: The actual permalink of the publicly available CSV or TSV file.
- Delimiter: “Comma” is used as the delimiter for CSV files, and “Tab” is used as the delimiter of the TSV files. When left blank, it will fetch the delimiter from the file extension.
- Locale: You can assign language and region code to the locale parameter. But if left blank, it will use the locale already used in the spreadsheet.
IMPORTDATA function can also be used without any parameters. If you want to import all the data from any URL, just use that URL inside a double quotation mark. The formula will look like “=IMPORTDATA(“URL”)” in Google Sheets.
You can even refer to a cell where a link is posted in this same formula. And to do that, you need to use the “IMPORTDATA(<cell number>)” formula.
Supported Data Formats
As I’ve said earlier, you can only import publicly available CSV and TSV files from URLs with this function. It only supports comma-separated values and tab-separated values, and tab-separated values.
Even if the URL is password-protected, you can still access the data by using this function, although you need to mention the ID and password in the formula itself.
Note: If you want to import a large dataset, it can be complicated if it has different parameters. In that case, named ranges can be a solution. If you don’t know how to do it, follow my latest guide on named ranges in Google Sheets.
Use Import Data – Real-Life Example
I hope you already understand the function and how to use it. So now, it is time to test it with some real-world examples.
Let’s say I want to import the dataset from the https://data.nasdaq.com/api/v3/datasets/FED/SUBLPDMBS_XWB_N_Q.csv URL. The steps will be:
- Step 1: Launch Google Sheets and open a spreadsheet.
- Step 2: Select the A1 cell (Or any cell from where you want to start the import).
- Step 3: Click on the formula (fx) field.
- Step 4: Enter the “=IMPORTDATA(“https://data.nasdaq.com/api/v3/datasets/FED/SUBLPDMBS_XWB_N_Q.csv”)” formula.
- Step 5: Hit the enter button, and the dataset will be automatically imported from the specified URL.
Now, you can also paste the link to a cell and use that cell as the reference in this formula to import data as well. The steps are as follows.
- Step 1: Launch Google Sheets and select a blank cell (Here, we are selecting B1).
- Step 2: Paste the link in that blank cell.
- Step 3: Select the blank cell from where you want the import to start (We selected A2).
- Step 4: Click on the formula (fx) field and enter the “=IMPORTDATA(B1)” formula.
- Step 5: Hit the enter button, and it will import the data.
You need to make sure that you are using the complete URL, including HTTP/HTTPS, in the formula. It will return an error if you are using the exact full link.
Bonus: You can easily work with a large dataset if you can group the rows and columns according to your needs. And here goes my detailed guide on grouping rows and columns in Google Sheets. Don’t miss it!
Best Use Cases For The IMPORTDATA Function
IMPORTDATA in Google Sheets is a vital function. And in many use cases, it becomes extremely useful. Here are the best use cases where you can use this function to easily import your required dataset.
Currency Exchange Rates
If you are in the money market or in the Forex business, you need to keep a tab on the current price of various currencies. By using the IMPORTDATA function, you can easily extract the current exchange rate from external sources.
And if you combine this with the “On change and every minute” calculation setting, you will always get the latest updated exchange rate. You can get publicly accessible CSV files on exchange rates in these two places:
Social Media Analytics
If you are a social media marketer or a digital marketer, you need to observe and keep track of the analytics to get the best results. It is also required to rightly optimize your social media handles and to keep a tab on the target audience.
All social media platforms, including Facebook, Instagram, Twitter, and TikTok, offer API integration through which you can access the data. And by using the IMPORTDATA function, you can easily integrate your social media analytics into Google Sheets.
And from a single place, you can monitor and modify the parameters of your analytics.
Bonus: You need to number your rows if you want a clean and organized analytical report of your social media matrix. And to do it the easy way, check out my comprehensive guide on automatically numbering rows in Google Sheets.
E-commerce Inventory Management
Many people are now into e-commerce and dropshipping businesses, as these businesses witnessed the highest profit in recent years. But it is true that keeping track of inventory is a really tough job, which mainly needs expertise.
However, it is not always possible for a small-time business owner to hire a professional. But by using this function, they can now easily manage their inventory.
If the inventory file is publicly available (direct or through API), you can simply integrate it on Google Sheets using this function. And you’ll get an updated report every hour.
Sports Statistics
Sport is a field that needs real-time updates. So, if you are a sports reporter or statistician, you must have access to sports data in real-time. But it is almost impossible to manually fetch and integrate real-time data.
Instead, you can integrate publicly accessible sports databases (such as ESPN Cricinfo) into your Google Sheets to get real-time reports. You can also merge different sources in a single spreadsheet to get the same data from different places (for fact-checking and accurate reporting).
Competitor Analysis
If you are into marketing or SEO, you need to keep track of your competitors to yield the best and most competitive results. But if you have multiple competitors targeting the same keyword or longtail, it can be difficult to keep track of the entire data from different resources.
In that case, you can easily import the competitor’s matrix from tools like AHREF or SEMRUSH. You can directly integrate the CSV files into Google Sheets to get details reports in a single place.
Bonus: If you are exporting from various sources, you may get data or text in different languages as well. But there is a function that can easily detect it. Check out my all-in-one guide about the DETECTLANGUAGE function in Google Sheets.
Best Practices For Using The IMPORTDATA Function
IMPORTDATA in Google Sheets becomes extremely efficient if you follow some common thumb rules. And here are the four best practices you must implement to get the best result from this function.
Regularly Updating URLs
You need to keep a tab on the URLs and regularly update those, as most websites also update and shift files from URLs. So, if any URL is not there anymore in the actual source website, you’ll start getting errors in your spreadsheet.
Even if a website moves any CSV file from an HTTP to an HTTPS location, you’ll start getting errors in your spreadsheet as the new URL doesn’t match exactly with the old one.
So, to get the best result, you must check all the URLs at frequent intervals and keep on updating them. You also need to replace the link if it is showing an error in Google Sheets.
Maintaining A Clean And Organized Spreadsheet
If you want to import a large dataset and then work with it, it is absolutely necessary to organize your spreadsheet. It will not just make it easy to work with but will also help you to easily filter specific data.
So, if you are importing data from external CSV and TSV files, you need to organize the data first and give your spreadsheet and clean look. You can even import a specific number of columns by using the IMPORTDATA function, which is also useful for organizing the required data.
Bonus: If you have lost any data after your recent edit that you can’t undo, you can still get it by shuffling through your edit history. To do it the right way, follow my trending guide on edit history in Google Sheets.
Utilizing Data Validation To Ensure Accurate Results
Data validation is another great parameter that works best with the IMPORTDATA function. You may get loads of blank cells in your spreadsheet if you import data from external resources.
In that case, data validation can be your savior to help you eliminate blank cells even before they appear. It will restrict all the cells that don’t match the condition prespecified by you. And the combination of these two can ensure accurate results with concise data.
Note: You can now easily share and collaborate in Google Sheets with your teammates and colleagues. It is extremely beneficial if you are working with a team and handling complex data assigned to different people.
Balancing Performance And Functionality
IMPORTDATA function has certain limitations (which I’ll discuss shortly). If you use this function too many times, it can make your spreadsheet extremely slow. You may even get errors if the number of cells you are trying to import is more than the available blank cell on your spreadsheet.
So, you need to make sure that you are not using this function too many times. Besides, you also need to ensure you are not importing more than 50,000 cells at once. If you can balance the functionality of this function, you will get the best performance possible.
IMPORTDATA Limitations
The main limitation of this IMPORTDATA function is the fact that you can import up to 50,000 cells with this one. However, there are some other limitations of this function as well. Let’s shed some light on a few of them.
Data Format (Only CSV And TSV)
As I’ve said, you can only import CSV (comma-separated values) and TSV (tab-separated values) files, which are available in public domains. It can only fetch the data from these two file formats.
But most public-access domains now provide either XLS files or JSON. So, it will not be possible to import those data with this function.
However, there is a way to import those files into your spreadsheet as well. If you want to know, check out my newest guide on importing JSON files in Google Sheets.
Data Publicly Accessible
The IMPORTDATA function can only fetch datasets from any URL that is publicly accessible. That means the CSV or TSV file should be accessible without logging into the website where it is hosted.
However, you can still access the data even if it is password protected, given the fact that the URL itself is publicly accessible.
You can use the “=IMPORTDATA(“https://<username>:<password>@<URL.csv”)” formula to access the password-protected files. Use your ID in the username parameter and your password in the designated one.
But it is not recommended for sensitive credentials, as the ID and password will be visible in the formula, and there is no way to hide those.
Maximum Number Of Imported URLs
You can only use the IMPORTDATA function up to 50 times in a single spreadsheet in Google Sheets. But in reality, the spreadsheet will become drastically slower (sometimes even become unresponsive) if you try to use it more than 20-25 times.
If you have a large dataset in the URLs, your spreadsheet may even start to act buggy after just 10 or 15 imports. So, you need to keep a tab on how many links you are importing.
It is also not possible to import data from multiple CVS/TSV files at the same time. You need to use separate formulas in separate cells to fetch different URLs.
Bonus: If you are importing too much data from external files, there will always be a chance of a huge number of duplicates. But to get a cleaner and more organized sheet, follow my latest guide on removing duplicates in Google Sheets the easy way.
Refresh Rate
There is no in-built functionality or parameter to change the refresh rate in the IMPORTDATA function in Google Sheets. You can get the fresh data by manually reentering the formula again or by using the same formula in a different cell.
But by default, all the IMPORT functions, including IMPORTDATA, IMPORTFEED, IMPORTHTML, and IMPORTXML, update every hour automatically.
However, you can change the refresh rate frequency to make sure your data remains up to date all the time. And the steps are easy as well.
- Step 1: Launch Google Sheets and open your spreadsheet where you have the “IMPORTDATA” function placed.
- Step 2: Click on the “File” option from the header menu.
- Step 3: Select the “Settings” option from the list.
- Step 4: Once you get a popup window, click on the “Calculations” tab from the header.
- Step 5: Click on the drop-down menu placed under the “Recalculation” header.
- Step 6: Select the “On change and every minute” option.
- Step 7: Click on the “Save Settings” option.
Note: Tweaking the calculation settings will not just refresh the IMPORTDATA function but will also refresh the NOW, TODAY, RAND, and RANDBETWEEN functions. You cannot change the refresh rate of just the IMPORTDATA function in Google Sheets.
Large Data Sets And Performance Considerations
The more data you import on Google Sheets with this IMPORTDATA function, the slower and more unresponsive your spreadsheet will become. There is a serious problem with the maximum file size you can import with this function.
Although the exact limit of file size is unclear (not even properly specified by Google), I started getting the “Resource at URL contents exceeded maximum size” message once I tried to import files that were more than 1.5MB.
If you want to import a large file, it is better not to rely on this function. Instead, you must download the file manually and then import the data from the file to Google Sheets.
Secondly, you may also get the “Result was not automatically expanded. Please insert more columns” message if there is not enough space in your spreadsheet. You need to make sure that you have the same number of blank cells available on your Google Sheets as the URL.
Bonus: Working with a large file always needs a good filtering process to make it concise. And to do that the easy way, follow my 2023 guide on filtering data in Google Sheets.
Google Sheets IMPORTDATA Not Working – What To Do?
Many of our users have complained that IMPORTDATA is not working on their Google Sheets. But all the minor issues can be solved by following some rules of thumb, which are as follows.
- You need to use the correct version of HTTP or HTTPS in your URL.
- You can only access files that are publicly available (It will give the “Could not fetch URL” error otherwise).
- The URL in the IMPORTDATA function must be placed inside double inverted commas (Otherwise, Google Sheets will not recognize it as a URL).
- The URL you are trying to fetch should not be incomplete, broken, or unavailable (Or else you’ll get the “Resource at URL not found” error).
- Make sure that the file extension is not broken or misplaced (Such as .cs instead of .csv or .xml instead of .csv).
- You also need to make sure that the file doesn’t need any API key for additional authentication.
As you can see, most of the time, the link and the parameters are the main reasons why the IMPORTDATA function is not working. So, it is better that you manually open the URL first to check if it is publicly accessible and if the file can be opened without logging in.
IMPORTDATA Function Alternatives
IMPORTDATA is an important function in Google Sheets; no doubt about that! But there are four other functions also available on GS that can work as alternatives to this one. Some of them are even more versatile in specific use cases.
The four alternatives to the IMPORTDATA function are as follows.
- IMPORTFEED: You can import data from the custom or default RSS and ATOM feed of any website with this function. It is useful if you want to fetch data from news portals, daily blogs, and other feed-centric domains.
- IMPORTRANGE: This function is key if you want to import data stored in another spreadsheet on Google Sheets to a new one. It is advantageous if you want to organize data from multiple spreadsheets.
- IMPORTHTML: With this function, you can import any publicly accessible HTML table into your Google Sheets. You can even extract lists and items from any website with this one.
- IMPORTXML: If you want to import any XML file, markup, or structure, you can easily do it with this one. This function is extremely useful if you want to integrate any API in your data or spreadsheet.
You can also use the SPLIT function after importing a large dataset on Google Sheets. It will help you to split texts into different cells. You can also use specific delimiters with this function.
IMPORTDATA Function – Recommended Alternative Tool
If you want the goodness of all the IMPORT functions on Google Sheets without the hassle of applying the right formula (which can be a complex one), there are some tools available.
Coupler.io is our personal favorite as it is incredibly versatile and can be easily integrated with Google Sheets. You can also import data from Microsoft Excel and Google Big Query with this tool.
Right now, you can access the tool from the official site. Besides it is also available as a plugin in the Google Workspace Marketplace. Just download and install it on your Google Sheets to unlock all the potential of Coupler.io.
Note: While working with a complex spreadsheet, you may need to lock the cell ranges to make it more organized. But if you don’t know the easiest way to do it, follow my latest guide on locking cell ranges in Google Sheets.
Final Note
Although IMPORTDATA is a versatile function in Google Sheets, there are many limitations to it. And if it is a large file that you are trying to import, your spreadsheet on GS can become unresponsive in no time.
Your spreadsheet will also start to lag if you use the IMPORTDATA function or the combination of any IMPORT functions. So, unless you want to import simple data from publicly accessible files, it is not recommended to use this one.
As a workaround, you can always download the CSV or TSV file manually and then upload it to Google Sheets. And you can do that easily by clicking on “File” and then the “Import” option.