IMPORTDATA lets you fetch data from published online CSV files. It’s a good solution, but not the best one. Why? It lacks advanced features that can improve user experience and productivity. We claim that Coupler.io can do better than IMPORTDATA, and this article is meant to prove that. So, let’s try out both solutions in real-life use cases to come to an unbiased verdict.
What is IMPORTDATA and how does it work?
IMPORTDATA is a Google Sheets function that imports data from a given URL in either CSV (comma-separated value) or TSV (tab-separated value) format.
URLis the location of a .csv or .tsv file containing the data to be imported. The
URLmust be specified, including the protocol.
reference-to-cellis a reference to a cell within Google Sheets, which contains the
IMPORTDATA formula examples
Case #1: Direct reference to the URL
Case #2: Reference to the cell containing the URL
IMPORTDATA pros and cons
What is Coupler.io?
Coupler.io is a Google Sheets add-on that lets you connect different data sources with spreadsheets. To import data from published online CSV files, you need to install the add-on and set up a CSV importer. We blogged about this in detail in “Import CSV into Google Sheets Without Any Coding”. Here is how the CSV importer at Coupler.io works:
Coupler.io CSV importer pros and cons
IMPORTDATA vs. Coupler.io
TL;DR comparison table
|Each error cleans your data||Error dependency||You are always safe from data loss if any error occurs|
|Maximum of 50 calls on a single spreadsheet||Limitations||Google Spreadsheets limits or Google Sheets API limits|
|No support has been officially claimed||Import from CSV links with authentication||Available|
|Not supported||Data update on schedule||Available|
|Available with the help of the QUERY function||Data import with a selected number of columns||Available|
|Not supported||Columns reordering||Available|
|Available with the help of the QUERY function||Data import with a limited number of rows||Available|
|Available with the help of the QUERY function||Advanced requests||Available|
|Not supported||Import data from secured URLs||Available|
|Not supported||Append imported data||Available|
|Manual||Date format recognition||Automatic|
Now, let’s check out how good both IMPORTDATA and Coupler.io are in real-life use cases. For this, we’ll inspect additional features available with each solution and demonstrate the outcome. Let the battle begin!
Data update on demand
After the initial import, you may want to update the data fetched from your online CSV file. With IMPORTDATA, you’ll have to manually execute the function. With Coupler.io, you need to click on the Run Importer button to update the data on demand.
Data update on schedule
Coupler.io provides the automatic data refresh feature. Select the frequency for data re-syncs and the data will be automatically imported on a set schedule. IMPORTDATA lacks any scheduling option.
Import data with a selected number of columns
You can select the number of columns to be imported via IMPORTDATA. For this, you’ll need to apply a combination of two functions: IMPORTDATA and QUERY. For example, here is how you can select columns #1,#2, and #3:
=QUERY(IMPORTDATA("https://www.quandl.com/api/v3/datasets/EUREX/FCPEG2020.csv?api_key=eVEFx_xdJBXEHGnmu3sc"), "Select Col1,Col2,Col3")
Read our blog post about QUERY Google Sheets function to learn more data manipulation capabilities.
With Coupler.io, you can also cut off the unnecessary columns. For this, there is the Fields parameter, where you need to type relevant column names separated by comma. An extra benefit is that the order of names you specify will result in the order of columns for output. An important note is that you have to be sure such headers are first row in the output result. For example, let’s import the columns that we fetched using QUERY+IMPORTDATA, but in a reversed order (High, Open and Date):
Import data with a limited number of rows
Both solutions also allow you to limit the total number of rows to be imported. For IMPORTDATA, you’ll need the QUERY function again. For example, here is how to limit your output to 10 rows:
=QUERY(IMPORTDATA("https://www.quandl.com/api/v3/datasets/EUREX/FCPEG2020.csv?api_key=eVEFx_xdJBXEHGnmu3sc"), "Select * limit 10")
In Coupler.io, you can cut off the unnecessary rows using the URL query string parameter. Let’s limit our output to 10 rows again:
You can also skip rows from the top of the CSV file. This is useful when you want to cut off notation data before table structured data or column headers. For this, you need to specify the number of rows to be skipped in the Skip rows field of the CSV importer. For example, let’s skip 2 rows:
Import data with conditions
Again, a combination of QUERY and IMPORTDATA broadens your importing capabilities. You can set up conditions for fetching data. For example, let’s import values in the Settle column that exceed 115:
=QUERY(IMPORTDATA("https://www.quandl.com/api/v3/datasets/EUREX/FCPEG2020.csv?api_key=eVEFx_xdJBXEHGnmu3sc"), "Select * where Col5 > 115",1)
With Coupler.io, you can use the URL query string parameter to make more precise requests, such as time ranges, users, and so on. This feature is usually available if you pull data from secured connections for API-styled requests. So, the parameters available will differ. For example, we use a CSV dataset stored on Quandl. Their API provides certain times-series parameters to customize dataset prior to download. Here is how you can fetch data rows on and after the specified start date: 2020-01-07.
Import data from secured CSV URLs
In the examples above, we fetched data from a publicly available CSV file, which did not require any authentication. It contained an API key attached to the URL (
csv?api_key=eVEFx_xdJBXEHGnmu3sc), so IMPORTDATA had no issues with pulling data from there. However, the function won’t work the same as with secured URLs. For example, let’s try to import a Clockify report from a CSV file:
With Coupler.io, you can cope with this task using such parameters as Request headers, Request body and HTTP method. Parameters will always differ depending on the app you’re fetching data from. Check out our blog posts Connect Jira Server to Google Sheets and Connect Clockify to Google Sheets for details. Here is how it works on the example of the Clockify link:
Append imported data
If you want to place data from multiple sources that have the same structure and order of columns, check out the APPEND mode in the Settings. You can set up a few importers and fetch data from all the CSV files without any distortion. This is quite useful for cases when you need to combine monthly reports into a single year report. Check out how it works:
If you want to do the same using the IMPORTDATA function, you’ll have to manually place formulas to cells and apply the QUERY function to skip the first row.
Date format recognition
An attentive reader will have noticed that the data fetched with IMPORTDATA and Coupler.io differ a bit:
Google Sheets does not recognize date values and, hence, you’ll have to update their format manually after the import. With Coupler.io, you won’t need to spend your time on this.
To wrap up: which solution should I choose for my needs?
If you import data from online published CSV docs from time to time, IMPORTDATA is likely a good fit. It’s simple to use and does not require any prerequisites. Moreover, you can enhance its import capabilities if you combine it with the QUERY function. Coupler.io with its CSV importer is the best option for recurrent import. You can automate data fetching and benefit from lots of available features. Besides, the Coupler.io team is working on different ready-to-use integrations, such as Jira Cloud, Xero, and others. Check them out on our blog. Feel free to provide your feedback in the comments section, and good luck with your data!Back to Blog