Import CSV Data Using the Google Sheets IMPORTDATA Function or Its Alternative

IMPORTDATA Google Sheets lets you fetch data from published online CSV files. It’s a good function to work with CSV data for reporting, building dashboards, and other analytical purposes. At the same time, Google Sheets IMPORTDATA does not actually import data, but only refers to the dataset in the specified URL. If you do need to import CSV from the web, you can use the IMPORTDATA alternative that we’re going to introduce below. Check out both options in real-life use cases to choose the best one for your needs.

What is IMPORTDATA Google Sheets and how does it work?

IMPORTDATA is a Google Sheets function that loads data in either CSV (comma-separated value) or TSV (tab-separated value) format from a given URL. So, you can use the IMPORTDATA function in Google Sheets to retrieve records from online published CSV/TSV files.

IMPORTDATA Google Sheets syntax

• IMPORTDATA("URL")
  • URL is the location of a .csv or .tsv file containing the data to be imported. The URL must be specified, including the protocol.

• IMPORTDATA(reference-to-cell)
  • reference-to-cell is a reference to a cell within Google Sheets, which contains the URL. 

IMPORTDATA in Google Sheets formula examples

Google Sheets IMPORTDATA with a direct reference to the URL

=importdata("https://data.nasdaq.com/api/v3/datasets/FED/SUBLPDMBS_XWB_N_Q.csv")

Google Sheets IMPORTDATA with a reference to the cell containing the URL

=IMPORTDATA(A1)

IMPORTDATA pros and cons

Pros	Cons
Fast and simple implementation No installation required You can fetch data from online published CSV, TSV, and TXT files You can adjust the number of rows and columns to be imported	Maximum of 50 IMPORTDATA calls on a single spreadsheet You are not secured from data loss as each error cleans your data Only manual re-import is available The date values need to be manually updated to the date format

Google Sheets IMPORTDATA not working

The main issue with Google Sheets IMPORTDATA is that all the records vanish in case of any error. In most cases, you will get the following error:

Resource at URL not found

This means that the URL you’re fetching data from is either broken or unavailable. So, it’s not actually an error on the side of the IMPORTDATA function in Google Sheets. 

Anyway, to fix it, you need to either replace the URL to your CSV file or make it valid.

IMPORTDATA Google Sheets error – Could not fetch URL 

Meanwhile, the most common error associated with IMPORTDATA Google Sheets is Could not fetch URL.

It can be caused by two things:

• Syntax error
• No access to the URL

First, double-check the URL syntax. In our example above, the CSV file extension was broken – .cs instead or .csv. 

If everything is okay with the syntax, check whether the file is accessible without any additional authentication like the API key as in our example. These steps should help you solve the problems with Google Sheets IMPORTDATA. However, we do advise you to try out its alternative – CSV integration by Coupler.io.

Alternative to the IMPORTDATA function in Google Sheets

Coupler.io is a data integration solution to automate exports of data from different apps to Google Sheets, Microsoft Excel, and Google BigQuery. It provides a CSV integration to import data from published online CSV files.

Compared to IMPORTDATA Google Sheets, the CSV integration actually imports the data from the CSV files. This means that if anything is wrong with your source file, you will still have the data in your spreadsheet.

Note: Coupler.io is available as a web app and a Google Sheets add-on that you need to install from the Google Workspace Marketplace.

Here is how it works:

• Sign up to Coupler.io, click the Add new importer button and select CSV as a source application and Google Sheets as a destination application.
• Enter the URL of your CSV file. Optionally, you can specify which columns to fetch, which rows to skip, and other supported query parameters. 
• Jump to the destination section and connect your Google account.
• Then select a spreadsheet file on your Google Drive and a sheet to load data to. You can also create a new sheet by typing in a name.

Check out what an importer may look like:

For automated imports of CSV to Google Sheets, enable the Automatic data refresh and configure the schedule: interval, days of the week, time preferences, and timezone.

CSV integration pros and cons

Pros

Cons

No total data loss if any error occurs You can use the headers parameter to provide authentication credentials for data import from secured sources Data re-import automation on a schedule You can select the number of rows and columns, as well as the order of columns to be imported You can import CSV data to several destinations: Google Sheets, BigQuery, Microsoft Excel. You can set up the target cell/range to get output where you need it You can place data imported from multiple sources (with the same fields structure and order) You can make more precise data import requests The date values are automatically converted into the date format

Not a native solution No data filtering

How to use IMPORTDATA in Google Sheets: real-life use cases

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 refresh 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 button to update the data on demand. 

Data refresh on schedule

IMPORTDATA lacks any scheduling option.

Coupler.io provides the automatic data refresh feature. You can configure the following parameters:

• Select interval: Every 15 minutes/ 30 minutes/ Hour/ Day/ Month
• Select day of the week: Mon, Tue, Wed, Thu, Fri, Sat, Sun
• Select time preferences: From 0:00 to 23:00
• Select time zone: From UTC−12:00 to UTC+14:00.

QUERY + IMPORTDATA Google Sheets formula to import data with a selected number of columns 

You can select the number of columns to be imported via IMPORTDATA Google Sheets. 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://data.nasdaq.com/api/v3/datasets/LBMA/GOLD.csv?api_key=eVEFx_xdJBXEHGnmu3sc"),
"Select Col1,Col2,Col3")

Read our blog post about QUERY Google Sheets function to learn more about data manipulation capabilities.

With the CSV integration by Coupler.io, you can also cut off unnecessary columns. For this, there is the Columns parameter, where you need to type relevant column names separated by a 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 the first row in the output result.

QUERY + IMPORTDATA Google Sheets formula to import data with a limited number of rows

Both solutions also allow you to limit the total number of rows to be imported. For Google Sheets IMPORTDATA, you’ll need the QUERY function again. For example, here is how to limit your output to 10 rows:

=QUERY(IMPORTDATA("https://data.nasdaq.com/api/v3/datasets/LBMA/GOLD.csv?api_key=eVEFx_xdJBXEHGnmu3sc"),
"Select * limit 10")

In Coupler.io, you can cut off the unnecessary rows using the limit parameter in the URL query string field.

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 integration. Otherwise, Coupler.io will import all rows by default.

QUERY + IMPORTDATA Google Sheets formula to import data with conditions

Again, a combination of QUERY and IMPORTDATA functions in Google Sheets 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://data.nasdaq.com/api/v3/datasets/LBMA/GOLD.csv?api_key=eVEFx_xdJBXEHGnmu3sc"),
"Select * where Col5 > 115",1)

With Coupler.io, you can use the URL query string field 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.  

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:

https://global.api.clockify.me/workspaces/5e450bcf1fdfac54e1b1e0e3/reports/summary?export=csv

With Coupler.io, you can cope with this task using such fields as Request headers, Request body, and HTTP method. Parameters will always differ depending on the app you’re fetching data from. Here is what it may look like:

Note: However, now Coupler.io provides a Clockify to Google Sheets integration which allows you to get data from Clockify without the CSV integration.

Append imported data

If you want to place data from multiple sources that have the same structure and order of columns, Coupler.io provides the APPEND mode in the Destination section. 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.  

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

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. 

IMPORTDATA Google Sheets vs. CSV importer comparison table

Check out the table with a concise comparison of the main features of IMPORTDATA and its alternative.

IMPORTDATA	Feature	Coupler.io
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 Sheets limits or Google Sheets API limits
No support has been officially claimed	Import from CSV links with authentication	Available
Not supported	Data refresh 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

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 of CSV from online published URLs, cloud storage, public-facing servers, and many more. You can automate data fetching and benefit from lots of available features. Good luck with your data!

Back to Blog