Analistable

How to combine multiple IMPORTRANGE formulas

By the Analistable team · Updated · 5 min read

Put each IMPORTRANGE inside VSTACK (or { ; }) to stack several files: =VSTACK(IMPORTRANGE(url1, "Sales!A2:D"), IMPORTRANGE(url2, "Sales!A2:D")). Wrap it in QUERY(..., "where Col1 is not null", 0) to drop blank rows. Each source file needs Allow access once, and every file must have the same columns in the same order.

Part of our guide: How to combine data in Google Sheets

Combine your sheets now

Files stay in your browser. Google access is read-only.

The formula

=QUERY(VSTACK(
  IMPORTRANGE("1AbC…north", "Sales!A2:D"),
  IMPORTRANGE("1AbC…south", "Sales!A2:D"),
  IMPORTRANGE("1AbC…west",  "Sales!A2:D")
), "select * where Col1 is not null", 0)

You can pass the full URL or just the spreadsheet key (the long ID between /d/ and /edit).

Keep the file list in cells

IMPORTRANGE accepts a cell reference for the spreadsheet, so keep the source URLs or keys in a Sources tab and refer to them: IMPORTRANGE(Sources!A2, "Sales!A2:D"). Replacing a file then means editing a cell, not the formula. Each new source still needs Allow access the first time.

Errors and fixes

IMPORTRANGE problems
You seeCauseFix
#REF! “You need to connect these sheets”Access not granted yetClick the cell, then Allow access
#REF! “You don't have permission”Your account can't open the sourceAsk the owner to share it with you
Loading… for a long timeLarge ranges or many importsImport only the columns and rows you need
“Result too large”The imported range is too bigSplit it into smaller ranges or filter at the source
Columns don't line upDifferent column order in a sourceReorder with QUERY(IMPORTRANGE(...), "select Col2, Col1…")

Performance tips

  • Import closed ranges (A2:D5000) rather than whole columns when you know the size.
  • Import each file once into its own hidden tab, then stack the tabs. Lookups against a local tab are faster than repeated IMPORTRANGE calls.
  • Avoid volatile functions (NOW, RAND) in the source files; they make imports refresh constantly.

For many large files, a scheduled Apps Script merge writes values once instead of recalculating live.

Worked example

Three regional files stacked with IMPORTRANGE
RegionDateProductUnits
North2026-09-01Desk4
North2026-09-03Lamp9
South2026-09-02Chair12
West2026-09-02Desk3

Each regional file has a Region column, so the source survives the stack. If yours don't, add one in each source file — IMPORTRANGE can't add a constant column on its own, though you can wrap each import in HSTACK with a repeated label.

Alternatives

For a handful of files, IMPORTRANGE is the simplest option. For dozens, or when the combined data feeds other heavy formulas, a scheduled Apps Script merge is faster because it writes values once.

Step by step: set up a combined file

  1. Create a new spreadsheet for the combined data. Make sure your account can at least view every source file.
  2. In each source file, check that the tab name and column order are identical (for example, a tab called Sales with Date, Product, Units, Region in A:D).
  3. Copy each source's URL into a Sources tab, one per row, with a label such as North in the next column.
  4. Test one import on its own first: =IMPORTRANGE(Sources!A2, "Sales!A1:D5"). Click Allow access when prompted.
  5. Repeat the test for each source so every connection is approved, then delete the test formulas.
  6. Write the combined QUERY(VSTACK(...)) formula on a Combined tab.

Approving each connection on its own makes errors easy to trace: if the combined formula later fails, you know access isn't the reason.

Label each source with HSTACK

When the source files don't have a Region column, add the label inside the combined formula:

=LET(
  n, IMPORTRANGE(Sources!A2, "Sales!A2:C"),
  s, IMPORTRANGE(Sources!A3, "Sales!A2:C"),
  QUERY(VSTACK(
    HSTACK(n, MAP(INDEX(n,,1), LAMBDA(x, IF(x="", , Sources!B2)))),
    HSTACK(s, MAP(INDEX(s,,1), LAMBDA(x, IF(x="", , Sources!B3))))
  ), "select * where Col1 is not null", 0)
)

Each import is stored in a LET variable so it is fetched once, and MAP writes the label from the Sources tab next to every non-empty row. The label ends up in the last column (Col4).

Filter at the source to stay under the limits

Importing whole tabs and filtering afterwards moves more data than you need. Two ways to bring in less:

  • Import only the columns you use: IMPORTRANGE(url, "Sales!A2:C") instead of A:Z.
  • Filter each import before stacking: QUERY(IMPORTRANGE(url, "Sales!A2:D"), "select * where Col1 >= date '2026-01-01'", 0) keeps only this year's rows.
  • Keep a summary tab in each source file (for example monthly totals by product) and import that instead of the raw rows.

When a source file moves or is shared differently

Maintenance problems
Change in the sourceEffect on the combined sheetWhat to do
Tab renamed from Sales to Sales 2026#REF! for that importUpdate the range string; quote names with spaces: 'Sales 2026'!A2:D
Column inserted in the sourceColumns shift for that file onlyAsk owners not to insert columns, or select columns by position with QUERY
File copied and the copy used insteadOld data keeps appearingUpdate the URL in the Sources tab and allow access again
Your access removed#REF! permission errorAsk the owner to share the file again

Check the combined total

Add a check cell that sums each source separately and compares the result with the combined tab, for example =SUM(INDEX(IMPORTRANGE(Sources!A2, "Sales!C2:C"),,1)) per file. In the worked example above, the units are 4 + 9 = 13 for North, 12 for South and 3 for West, so the combined Units column should total 28.

Frequently asked questions

Can I use IMPORTRANGE with multiple spreadsheets?
Yes. Put several IMPORTRANGE calls inside VSTACK or an array literal { ; } to stack them.
Do I have to allow access for every IMPORTRANGE?
Once per pair of files. After that, every IMPORTRANGE from the same source file works.
Why is IMPORTRANGE so slow?
Each call fetches data from another file. Import fewer columns, use closed ranges, and import each file once.
Can IMPORTRANGE pull from a tab whose name has spaces?
Yes. Put the tab name in single quotes inside the range string, for example "'Sales 2026'!A2:D".
How do I add a new source file later?
Add its URL to the Sources tab, allow access once with a test IMPORTRANGE, then add one more IMPORTRANGE line to the VSTACK.
Is there a limit on how many IMPORTRANGE formulas I can use?
Many imports make a spreadsheet slow to load. Keep the number of separate IMPORTRANGE calls low by importing each file once and stacking the results, or switch to a scheduled Apps Script merge.

Related guides