Sheets IMPORTDATA and Its Limits for Live Data Feeds

=IMPORTDATA("https://example.com/report.csv") is the shortest integration in all of spreadsheets: one formula, no code, no credentials. For a public CSV that changes slowly, it is hard to beat. It is also easy to push past its limits without noticing, because the failure modes are quiet: a stale value, a number that turned into a date, a refresh that happened six hours ago. This article lays out what the function does, where it stops and how to measure it for your own data before you depend on it. It sits in the toolkit described in Google Sheets alongside databases.

What it does

IMPORTDATA takes a URL and an optional delimiter, fetches the content from Google's servers and spills it into the cells below and to the right of the formula.

=IMPORTDATA("https://example.com/exports/prices.csv")
=IMPORTDATA("https://example.com/exports/prices.tsv", "	")

The delimiter is guessed from the file type (comma for .csv, tab for .tsv) unless you pass one. The result is a block of cells, one per field, starting where the formula sits. If the area below or to the right has content, the formula shows a #REF! error: "Array result was not expanded because it would overwrite data". Leave room.

It belongs to a family: IMPORTHTML reads a table or list from a web page, IMPORTXML runs an XPath query on a page or feed, IMPORTFEED reads RSS and Atom and IMPORTRANGE reads another spreadsheet. They share the same properties: Google fetches, Google schedules and Google caches.

What it cannot do

The limits that matter most are about access.

No authentication. The request comes from Google's servers with no credentials and no custom headers. An endpoint that needs a bearer token, a cookie or basic auth cannot be reached, except by putting the secret in the URL, which is a poor idea: the URL is stored in the sheet, visible to everyone who can view it, and written to your server logs. If the file must be private, do not use IMPORTDATA.

Only publicly reachable URLs. Google's servers must be able to connect. An internal host, a VPN-only address or localhost will fail. Allow-listing Google's addresses is not a stable strategy, because the ranges are large and change.

Only text in CSV or TSV shape. A JSON endpoint, an Excel file or a response with HTML around the data will import as garbage or fail.

No control over refresh. The sheet updates the imported data on a schedule Google decides, which has varied from minutes to hours and is not promised. You can force a refresh by changing the formula, for instance by appending a throwaway query parameter to the URL (?t= plus a value), but a script cannot rely on that, and each new URL adds load to your server.

Limits on size and number. Google documents limits on the size of an imported file and on how many import functions a spreadsheet can hold, and has changed them over time. A large file that works today may fail after growing. Look up the current limits in the function's help page, then test your own file rather than trusting a remembered number.

What it does to your data

The import parses each field as though someone typed it into a cell, according to the spreadsheet's locale. That leads to predictable problems.

The practical consequence: identifiers lose leading zeros, long numbers lose digits, and dates arrive in a type you did not choose. If the file is yours, fix what you can at the source: write identifiers in a column that your own format makes unambiguous, such as a prefix letter, or avoid exposing them as bare digits. If the file is someone else's, expect to clean on the sheet side, with formulas that rebuild text (TEXT(A2, "00000") to restore five-digit codes), which only works if you know the width.

Text beginning with =, +, - or @ is the injection risk again. If the feed includes user-supplied text, a hostile value may be interpreted by the sheet. Generate your CSV so that such fields are neutralized (escaping formula characters in a CSV export).

Encoding is another quiet trap. The file should be UTF-8, and a byte order mark may appear as a stray character in the first cell (UTF-8 BOM in CSV files). Quoted fields with embedded newlines are generally handled, but test a row that has one.

How to size it for your data

Do not guess a limit. Measure.

1. Publish a representative file. Use real data, or generated rows with the same shape: the same number of columns, similar string lengths, the same quoting.

2. Import it in a fresh sheet. Note whether the whole file arrives, how long the first load takes and whether the sheet stays responsive.

3. Grow it in steps. Double the rows and repeat, until it fails or becomes unusable. Record the row count and the file size at each step.

4. Look at the failure. Is it an error message, a truncated import or an endless "Loading..."? Truncation without an error is the dangerous one.

5. Watch refresh. Change a value at the source, note the time and check the sheet at intervals. Record how long it took, and over several days, not once.

6. Leave a margin. If the import fails at 40,000 rows, plan for 10,000 and review quarterly.

A tiny server is enough to test with, serving a generated file:

from http.server import BaseHTTPRequestHandler, HTTPServer

class H(BaseHTTPRequestHandler):
    def do_GET(self):
        body = "id,value\n" + "\n".join(f"{i},{i*3}" for i in range(50_000))
        self.send_response(200)
        self.send_header("Content-Type", "text/csv; charset=utf-8")
        self.end_headers()
        self.wfile.write(body.encode())

HTTPServer(("0.0.0.0", 8000), H).serve_forever()

It needs to be reachable from the internet to be tested, so use a temporary public host with nothing sensitive on it. This is a test of Google's side, not of your data.

Compare the alternatives

When IMPORTDATA stops working for you, the next steps are a spectrum.

The API route is more work and gives you control of timing, authentication, types and error handling (using the Sheets API from Python and publishing database results to a sheet automatically).

Make a failure visible

If you do use IMPORTDATA, make a stale or failed import show. Add a column that your server fills with a generation time, and display it in a header cell with a formula such as =INDEX(IMPORTDATA(...), 2, 4). Add a check cell that compares the row count with an expected minimum (=COUNTA(A:A) > 100) and flags red when it fails. A reader who finds a red banner asks questions; a reader who finds a plausible but stale table makes decisions.

Also decide who owns the URL. If the file lives on someone's personal server and they leave, the sheet breaks. Put it on infrastructure with an owner and a name.

When IMPORTDATA is the wrong feed

It is the wrong feed for private data, for anything large, for numbers that must be current to the minute and for data where types matter, such as codes with leading zeros. It is wrong when you cannot afford silent staleness. It is a good fit for a public, slow changing, modest CSV where the reader understands that the data is approximate: a currency table, a list of public holidays, an open dataset. The IMPORTDATA help page documents the syntax, and the Sheets API values reference is the way out when the limits bite.