IMPORTDATA Function in Google Sheets

If you have a CSV or TSV sitting at a public URL and you want to pull it straight into a Google Sheet, the IMPORTDATA function is what you reach for.

It fetches the file, splits it into rows and columns, and spills the whole thing into the grid. In this article, I’ll show you how to use IMPORTDATA with a small monthly CSV, a wider stats CSV, a wide geographic CSV, a grade book, and a QUERY wrapper that filters the result.

IMPORTDATA Function Syntax in Google Sheets

Here is how the IMPORTDATA function is written:

=IMPORTDATA(url, [delimiter], [locale])
  • url is the public link to a CSV or TSV file. It needs to be a direct link to the data itself, not a webpage that contains a download button.
  • delimiter is the character used to split fields. This argument is optional. If you leave it out, IMPORTDATA auto-detects comma versus tab.
  • locale is an optional language locale used when parsing numbers and dates from the file.

When to Use IMPORTDATA Function

  • Pull a public CSV into a sheet without downloading the file and re-uploading it.
  • Mirror a small dataset that updates at its source, so your sheet stays current.
  • Combine the imported data with QUERY, FILTER, or SORT to slice or sort it on the fly.
  • Pull reference lists like city tables or country codes for quick analysis.
  • Build a one-shot dashboard from a CSV your team publishes to a stable URL.

Example 1: Import A Small Monthly Numbers CSV

Let’s start with a tiny CSV so the spill is easy to see.

Below is the dataset, with a single CSV URL sitting in cell A2. The file is airtravel.csv from a public FSU mirror, holding US air-travel passenger counts for the months of 1958, 1959, and 1960.

Google Sheets: Cell A1 contains 'CSV URL', Cell A2 contains an airtravel.csv link.

You want IMPORTDATA in B2 to pull the whole file into the sheet.

Here is the formula:

=IMPORTDATA(A2)
Google Sheets: Formula `=IMPORTDATA(A2)` retrieves CSV data from the URL in cell A2.

IMPORTDATA reads the URL in A2, fetches the CSV, and spills the contents starting at B2.

The header row lands in B2 to E2 with the labels Month, 1958, 1959, and 1960. The next 12 rows below the header fill in with the months JAN through DEC and their passenger counts.

Pro Tip: The spill cascades down and to the right from the formula cell. If you put IMPORTDATA in B2 and the CSV has 4 columns and 13 rows, every cell from B2 to E14 gets filled. Leave enough empty space, or you’ll get a `#REF!` spill error.

Example 2: Import A People Stats CSV With Mixed Columns

Real CSVs rarely have just numbers. They mix text labels with numeric measurements.

Below is the dataset, with the URL to biostats.csv in cell A2. That file holds 18 sample people with a name, sex, age, height in inches, and weight in pounds.

Google Sheet showing "CSV URL" in A1 and a "biostats.csv" URL in cell A2.

You want the same one-cell formula to pull the whole table in, with text and numbers landing in the right types.

Here is the formula:

=IMPORTDATA(A2)
Formula =IMPORTDATA(A2) in B2 imports CSV data from A2's URL.

IMPORTDATA spills the file with 5 columns of headers (Name, Sex, Age, Height (in), Weight (lbs)) and the 18 data rows below them.

Notice that text columns like Name and Sex stay left-aligned as strings, while Age, Height, and Weight come in as right-aligned numbers. IMPORTDATA respects the type of each value in the source file.

Example 3: Import A US Cities Latitude Longitude CSV

Wider CSVs work the same way. The spill just expands further to the right.

Below is the dataset, with the URL to cities.csv in cell A2. The file lists 49 cities, mostly in the US with a few from Canada, and breaks latitude and longitude into degrees, minutes, seconds, and a direction indicator.

Google Sheet: 'CSV URL' in A1, cities.csv data link in A2.

You want the full table pulled in with one formula.

Here is the formula:

=IMPORTDATA(A2)
Google Sheets: Cell B2 selected, formula bar shows `=IMPORTDATA(A2)` referencing the CSV URL in A2.

The spill expands across 10 columns (LatD, LatM, LatS, NS, LonD, LonM, LonS, EW, City, State) and fills the 49 city rows below the header.

IMPORTDATA does not care how wide the CSV is. It carves out whatever shape the file needs and drops the data into place.

Pro Tip: Once IMPORTDATA spills, you can reference the spill range from anywhere else in the sheet. For example, `=COUNTA(B3:B)` counts the city names below the header row, no matter how the source file grows.

Example 4: Import A Student Grades CSV

CSVs in the wild can have quirks. IMPORTDATA brings them in exactly as they are.

Below is the dataset, with the URL to grades.csv in cell A2. The file has 15 students with last name, first name, an SSN-style placeholder, four test scores, a final, and a letter grade.

Google Sheet showing cell A1 with 'CSV URL' and A2 with a grades.csv URL.

You want the grade book pulled into the sheet.

Here is the formula:

=IMPORTDATA(A2)
Google Sheet: Formula `=IMPORTDATA(A2)` in formula bar, B2 shows 'Last name'.

The spill lands with 9 columns of headers and 15 student rows below them.

A couple of rows in this CSV have slightly messy data, like a Test1 cell that holds two numbers separated by spaces. IMPORTDATA does not try to clean any of it. What’s in the CSV is what shows up in your sheet.

Pro Tip: Never trust IMPORTDATA blindly. The imported data is only as clean as the source CSV, so always eyeball the result before you run averages or sums on it.

Example 5: Filter An Imported CSV With QUERY

IMPORTDATA composes naturally with other functions. Wrap it in QUERY and you can slice the imported data on the way in.

Below is the dataset, with the URL to biostats.csv again in cell A2.

Google Sheet: cell A1 has 'CSV URL', cell A2 has the biostats.csv URL.

You want only Name, Age, and Weight columns, and only for people older than 35.

Here is the formula:

=QUERY(IMPORTDATA(A2), "SELECT Col1, Col3, Col5 WHERE Col3 > 35", 1)
Google Sheets: cell B2 shows a QUERY and IMPORTDATA formula using a CSV URL from A2.

How this formula works:

  • IMPORTDATA fetches the biostats CSV and hands the whole 5-column table to QUERY.
  • The SELECT clause keeps Col1 (Name), Col3 (Age), and Col5 (Weight).
  • The WHERE clause filters down to rows where Col3 (Age) is greater than 35.
  • The trailing 1 tells QUERY the first row is a header.

The spill lands with the 3 selected columns and 7 people who are older than 35. Anything that takes a range works with the spilled output of IMPORTDATA, so SORT and FILTER play the same way.

Tips & Common Mistakes

  • The URL must be a direct CSV link. If the URL ends in .csv or returns a text/csv response, you’re fine. If it points to a webpage that contains a download button, IMPORTDATA returns nothing useful. Open the URL in your browser and confirm you see raw data, not HTML.
  • Public URLs only. IMPORTDATA cannot reach behind a login or a corporate VPN. For private data, paste the CSV manually, or use the IMPORTRANGE function to pull from another Google sheet you own.
  • First-time use prompts you to allow access. Google Sheets blocks IMPORT-family calls by default until the sheet’s owner clicks Allow access in a desktop browser. One click per sheet, then future imports run without prompts.

IMPORTDATA is the quickest way to land a public CSV in your sheet. Point it at a direct file URL, give the spill enough room, and the data drops in.

It pairs nicely with QUERY, FILTER, and SORT for on-the-fly cleanup, but the cleanliness of the source CSV is what you end up with. Eyeball it before you compute on it. You can also explore the full list of Google Sheets functions for related tools that work well alongside IMPORTDATA.

List of All Google Sheets Functions

Related Google Sheets Functions / Articles: