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
| You see | Cause | Fix |
|---|---|---|
| #REF! “You need to connect these sheets” | Access not granted yet | Click the cell, then Allow access |
| #REF! “You don't have permission” | Your account can't open the source | Ask the owner to share it with you |
| Loading… for a long time | Large ranges or many imports | Import only the columns and rows you need |
| “Result too large” | The imported range is too big | Split it into smaller ranges or filter at the source |
| Columns don't line up | Different column order in a source | Reorder 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
| Region | Date | Product | Units |
|---|---|---|---|
| North | 2026-09-01 | Desk | 4 |
| North | 2026-09-03 | Lamp | 9 |
| South | 2026-09-02 | Chair | 12 |
| West | 2026-09-02 | Desk | 3 |
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
- Create a new spreadsheet for the combined data. Make sure your account can at least view every source file.
- 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).
- Copy each source's URL into a Sources tab, one per row, with a label such as North in the next column.
- Test one import on its own first:
=IMPORTRANGE(Sources!A2, "Sales!A1:D5"). Click Allow access when prompted. - Repeat the test for each source so every connection is approved, then delete the test formulas.
- 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
| Change in the source | Effect on the combined sheet | What to do |
|---|---|---|
| Tab renamed from Sales to Sales 2026 | #REF! for that import | Update the range string; quote names with spaces: 'Sales 2026'!A2:D |
| Column inserted in the source | Columns shift for that file only | Ask owners not to insert columns, or select columns by position with QUERY |
| File copied and the copy used instead | Old data keeps appearing | Update the URL in the Sources tab and allow access again |
| Your access removed | #REF! permission error | Ask 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.