How to Pull Data From Another Google Sheet Automatically

One function pulls data from another Google Sheets file:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AbC.../edit", "Sheet1!A1:D100")

It will return #REF! the first time. That's expected, and the fix is one click — covered immediately below, because it's where everyone gets stuck.

The formula is correct. This is Sheets asking permission to connect the two files, and it only asks once.

The step nobody mentions

The first time you import from a particular file, the formula returns #REF! rather than data. Hover over the cell and a small prompt appears: "You need to connect these sheets", with an Allow access button.

Click it. The data appears within a second or two.

This is a deliberate security step, not a bug. It means nobody can silently pull data out of a file you own by knowing its URL — someone has to explicitly authorise the connection, and only someone with edit access to both files can do it.

⚠️ The authorisation is per pair of files and permanent until revoked. Once granted, every future IMPORTRANGE between those two files works without asking again — including ones added by other editors later.

What goes in the two arguments

Argument	What to put
Source	The whole URL, in quotes. The long ID between /d/ and /edit works equally well.
Range	"Sheet1!A1:D100", in quotes. Without a sheet name it uses the first tab.

Both arguments are text, so both need quotation marks. Missing one is the most common cause of #ERROR! on an otherwise correct formula.

Better practice: put the URL in a cell of its own and reference it.

=IMPORTRANGE($A$1, "Sheet1!A1:D100")

When the source file moves or is replaced, you change one cell rather than hunting through every formula that referenced it — the same argument as keeping a threshold in one place rather than typing it into every row.

The mistake that costs you

Scattering IMPORTRANGE through a workbook is the wrong structure. Every instance is a separate cross-file request, and a sheet with thirty of them spends its life showing "Loading...".

Import once, then reference locally.

1. Create one tab called something like Imported.
2. Put a single IMPORTRANGE in A1, pulling everything you need.
3. Every other formula in the file reads that tab, not the source file.
4. Hide the tab.

One request instead of thirty, and one place to look when something breaks. It's the same separation as keeping data, calculations and the view on separate sheets.

Filtering as you import

Pulling 40,000 rows to use 300 of them wastes the request. QUERY filters at the source:

=QUERY(IMPORTRANGE($A$1, "Sales!A1:F5000"),
       "select Col1, Col2, Col5 where Col4 > 1000", 1)

Note the column syntax: Col1, Col2, not A, B. When QUERY wraps an imported range it loses the letters and numbers them instead — and using letters here is the second most common cause of #ERROR!.

The trailing 1 tells QUERY the first row is headers.

The permission trap worth understanding

This one matters and most guides skip it entirely.

Once data is imported, anyone who can open the destination file can see it — regardless of whether they have access to the source.

Import from a restricted salary sheet into a file the whole team can read, and the whole team can read the salaries. The source file's permissions do not travel with the data.

So before importing anything, ask who can open the destination. If the answer is broader than who can open the source, you have just widened access to that data, and nothing will warn you.

How current is it?

Near-live, but not instant. Changes in the source propagate within a few minutes under normal conditions, and can lag noticeably when the file is large or heavily used.

For anything you'll present or act on, open the source and confirm rather than assuming the import is current. If you need a figure frozen at a point in time — a month-end total, a submitted quote — copy it and Paste special → Values only. An imported figure that silently updates is the wrong thing to build a record on.

The limits

• A Google Sheets file holds 10 million cells, and imported cells count toward the destination's limit.
• There is a cap on how many cross-file references one file can hold. Well-built sheets never approach it; a sheet with an IMPORTRANGE in every row will.
• Imports are one-way and read-only. You cannot write back to the source.
• Formatting doesn't come across — only values.
• If the source file is deleted or you lose access, every dependent formula breaks at once.

Can Excel do this?

Not with a formula, and this is the one place Sheets is clearly ahead.

Excel can link to another workbook, but the link only refreshes reliably while the source file is open, and it breaks when the file moves. The proper route is Power Query — Data → Get Data → From File — which is more capable than IMPORTRANGE in what it can transform, and needs a manual refresh rather than updating on its own.

For genuinely live shared data across files, Sheets wins. For heavy transformation of large files, Power Query does.

When it goes wrong

#REF! with "You need to connect these sheets"

Click Allow access. If the button doesn't appear, you don't have edit access to both files — someone who does has to authorise it once.

#REF! saying the result would overwrite data

The import needs space to expand into and something is in the way. Clear the cells below and to the right — the same principle as Excel's spill error.

#ERROR!

Almost always a quoting problem, and usually not one you made. Sheets automatically inserts a closing quote when you type an opening one, and a closing bracket when you type an opening bracket — so typing the formula around a pasted URL leaves you with "" and )) where you meant one of each.

Look at the end of the formula in the formula bar. Two quotes together, or two closing brackets, is the whole problem.

Avoid it by pasting the formula complete rather than typing around a pasted URL. Using just the file ID — the long string between /d/ and /edit — also makes the quoting far easier to see:

=IMPORTRANGE("1AbC2dEf3GhI...", "Sheet1!A1:D100")

Inside QUERY, the other cause is using column letters. It must be Col1, Col2.

#N/A

The range doesn't exist in the source — usually a renamed tab. The formula holds the old name as text, so renaming a sheet breaks every import pointing at it.

Stuck on "Loading..."

Too many separate imports, or too large a range. Consolidate to one import and narrow the range.

It worked and now returns nothing

Access to the source was revoked, the file was moved to a different Drive, or its owner left the organisation. Check you can still open the source directly.

Common questions

Can I import from a sheet I can only view?

You can import from a file you have view access to, but authorising the connection requires edit access to the destination. If both are outside your control, ask the owner.

Does it work with files shared "anyone with the link"?

Yes, and think about it first. A publicly linked source file, imported into another file, is data with two doors into it.

Can I import several ranges at once?

Stack them: ={IMPORTRANGE(url1, range1); IMPORTRANGE(url2, range2)}. The semicolon stacks vertically, a comma joins side by side — the same array syntax used for combining sheets.

Will it slow the file down?

Yes, proportionally to how much you import and how often. One import of the rows you need is fine; thirty imports of whole sheets is not.

Is there any way to make it refresh on demand?

Not directly. The usual workaround is to change something the formula depends on — toggling a referenced cell forces a recalculation. Clumsy, and it works.

The short version

• =IMPORTRANGE(url, "Sheet1!A1:D100") — both arguments in quotes.
• The first #REF! is the permission prompt. Click Allow access.
• Put the URL in one cell and reference it.
• Import once into a hidden tab; reference that tab everywhere else.
• Filter at the source with QUERY, using Col1 rather than A.
• Imported data inherits the destination's permissions, not the source's.
• Freeze anything that needs to be a record — paste it as values.
• Renaming a tab in the source breaks every import pointing at it.

Next steps: if you're pulling data in to combine it with what you already have, stacking and joining are different jobs, and imported data deserves the same checks as any other import before you build on it. Within a single file, INDEX MATCH is the tool and needs no permissions at all.