How =IMPORTDATA refreshes, its limits, and what to do when it shows #N/A
IMPORTDATA is one function with one argument, and it still manages to confuse people: it refreshes when it likes, shows errors that do not say what is wrong, and quietly changes long numbers. This guide explains each behaviour for a live comment link and how to work with it.
Updated Nov 19, 2026 · 7 min read
What the formula does
On an export page, Make a live link gives a formula like this:
=IMPORTDATA("https://commentstosheets.com/s/…csv")
Pasted into a cell, it makes Google's servers fetch the CSV at that address and spill it into the sheet: the header in the formula's row, one comment per row below, thirteen columns from # to platform. Setting it up is covered on the live link page; this guide is about what happens afterwards.
When it refreshes
Google Sheets re-fetches IMPORTDATA on its own schedule: about once an hour while the spreadsheet is open in a browser, and when the spreadsheet is opened after being closed. It does not run while nobody has the file open, and there is no refresh button.
What comes back on each fetch is the export as it was read. The rows are a fixed list, so a refresh brings the same rows in the same order. They change in exactly two cases:
- The export itself changed, for example a preview of a big post was unlocked and now holds the whole thread.
- You made a new export of the post. That is a new export page with its own link, so the formula has to be replaced with the new one.
A live link is "live" in that the sheet reads straight from the export. It is not a feed of new comments. To follow a post over time, export it again and keep each day in its own tab.
To force a fetch now, change the address in a way that does not matter. Add ?v=2 after .csv, inside the quotes:
=IMPORTDATA("https://…/s/…csv?v=2")
Sheets sees a new address and fetches at once. Raise the number each time. The file itself may be held by caches for up to five minutes, so two forced fetches inside that time can return the same copy.
Every error, its cause and its fix
| What the cell shows | Cause | Fix |
|---|---|---|
#N/A, "Could not fetch url" | The address no longer answers with a file. Someone pressed New link (the old address stops at once), or the export was deleted on schedule. | Open the export page and make a new link. If the page is gone too, export the post again. |
#REF!, "Array result was not expanded because it would overwrite data in …" | Something is typed in a cell the import needs. | Clear everything below and to the right of the formula, or put the formula in A1 of an empty tab. |
#ERROR!, "Formula parse error" | The quotes were turned into curly quotes by a chat or mail app, or part of the formula was cut off. | Copy the formula again from the export page with Copy formula, or retype the two quotes. |
| A warning about formulas sending and receiving data from external parties | Sheets asks before the first fetch from an outside address. | Press Allow access. It is asked once per spreadsheet. |
| "Resource at url contents exceeded maximum size" | The export is bigger than IMPORTDATA accepts. | Use the Excel workbook and File, Import instead. |
Loading… that never ends | A slow fetch, or many import formulas at once. | Wait a minute, then delete the formula and paste it again. Keep one import per tab. |
An export that is still being read, or has expired, answers with a short text message instead of a CSV, and Sheets reports that as #N/A. Exports are kept for 30 days and YouTube exports for 30, for the reason given in how to export YouTube comments.
Limits worth knowing
- Size. IMPORTDATA refuses files above a size Google sets, and a spreadsheet has an overall ceiling of 10 million cells. A comment export has 13 columns, so 100,000 comments is 1.3 million cells in one tab. Long before the ceiling, a sheet that size is slow. For very large posts use the workbook.
- What is served. The link always serves the whole export, unfiltered. Filters you set on the export page (only replies, one row per person, a search) apply to downloads and to Copy for Sheets, not to the link. Filter in the sheet instead, with the formulas below.
- A preview stays a preview. If the post is above the free limit and not unlocked, the link serves the preview rows, the same as the downloads do.
- One export per link. To combine exports, import each into its own tab and stack them: compare comments across networks in one sheet.
- Google Sheets only. Excel has no IMPORTDATA. In Excel, Data, Get Data, From Web accepts the same address and loads it through Power Query; set the file origin to UTF-8 as described in why emoji and accents break in a CSV.
Types: what IMPORTDATA changes on the way in
IMPORTDATA decides the type of each value by itself, and there is no option to stop it.
posted_at_utcbecomes a real date and time, which is what you want. It is still UTC.likesand#become numbers.- Long IDs may become numbers and lose their last digits. Instagram and TikTok IDs are longer than the 15 digits a spreadsheet number holds. If you need
comment_idexact, for example to rebuild threads, use Copy for Sheets or the workbook, which mark IDs as text. The background is in why spreadsheets round long comment IDs. - A comment that is only a number, such as "100", arrives as a number. Formulas that expect text, such as REGEXMATCH, fail on those cells. Write
E2&""instead ofE2inside such formulas to turn the value back into text.
Build on an import without breaking it
The imported range belongs to the formula. Typing in any of its cells breaks the import with #REF!, and sorting it does nothing. So leave the tab alone, name it Import, and work in other tabs that read from it.
=QUERY(Import!A:M, "select * where H = 'no' order by G desc", 1)
Top-level comments only, most liked first.
=FILTER(Import!A2:M, REGEXMATCH(Import!E2:E&"", "(?i)price|how much"))
Comments that ask about price. More patterns are in find questions and buying intent with REGEXMATCH.
=IFERROR(QUERY(Import!A:M, "select C, count(A) where A is not null group by C order by count(A) desc label count(A) 'comments'", 1), "Waiting for the import")
Comments per person, with a readable message instead of an error while the import is loading or after the link has ended.
Keep the rows after the link ends
Every live link ends when its export is deleted. When it does, the import shows #N/A and every tab built on it empties. To keep the data:
- Click the tab's top-left corner to select everything, and copy.
- Add a new tab, click A1, and choose Edit, Paste special, Values only.
- Point your formulas at the new tab, or rename the tabs so the names your formulas use now refer to the values.
Do this on the day you finish the analysis, not on the day the link stops. The values tab is also the right source for a Looker Studio dashboard.
Until then, remember that anyone who has the address can read the rows, which is what lets Google fetch it without your password. If a link has travelled further than you meant, press New link on the export page: the old address stops at once. For a one-off look, a paste is simpler than a link, and the Google Sheets export page compares the two.
FAQ
How often does IMPORTDATA refresh?
About once an hour while the spreadsheet is open, and when it is reopened. It does not refresh while the file is closed.
Can I make IMPORTDATA refresh on demand?
Yes. Change the address slightly, for example by adding ?v=2 after .csv inside the quotes. Sheets treats it as a new address and fetches at once.
Why does my sheet show #N/A where the comments were?
The link stopped answering: a new link replaced it, or the export was deleted on schedule. Make a new link on the export page, or export the post again.
Will the link bring in comments posted after I exported?
No. It serves the export as it was read. Export the post again for newer comments and use the new link.