Each branch keeps its own Google Sheet, and you want one summary. IMPORTRANGE links them live.
In this article
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AbC.../edit", "Sales!A1:F")The first time, the cell shows #REF! with an Allow access button — click it once per source file.
Only the rows you need: wrap it in QUERY
=QUERY(IMPORTRANGE("URL","Sales!A1:F"), "select Col1, Col2, Col6 where Col3 = 'North' and Col6 > 50000", 1)Inside QUERY on imported data, refer to columns as Col1, Col2…, not letters. See the QUERY function for Excel users.
Stack several files
={IMPORTRANGE("URL1","Sales!A2:F"); IMPORTRANGE("URL2","Sales!A2:F")}⚠️ Each IMPORTRANGE adds load time. For more than a handful of sources, consider a small Apps Script that copies data once a day instead.
A practical setup: one summary from several branch sheets
Say Delhi, Mumbai and Pune each maintain their own sales sheet with the same columns: Date, Customer, Product, Qty, Amount. You want one head-office sheet that updates on its own.
- In the summary file, put each source URL in a small config range (A2:A4) and the tab/range (B2:B4) — easier to maintain than URLs buried in formulas.
- Stack them:
={IMPORTRANGE(A2,B2); IMPORTRANGE(A3,B3); IMPORTRANGE(A4,B4)} - Filter and total with QUERY on top:
=QUERY({IMPORTRANGE(A2,B2);IMPORTRANGE(A3,B3);IMPORTRANGE(A4,B4)}, "select Col3, sum(Col5) where Col1 is not null group by Col3 order by sum(Col5) desc label sum(Col5) 'Revenue'", 0)
Why it breaks, and the fixes
| Symptom | Cause | Fix |
|---|---|---|
#REF! “You need to connect these sheets” |
Access not granted yet | Click the cell and choose Allow access — once per source file |
| Rows appear with the header repeated | Each range includes its own header | Import from row 2 (Sales!A2:E) and add one header row yourself |
| Stacking fails with “array literal” error | Different number of columns in the sources | Make every source range exactly the same width |
| Very slow file | Many IMPORTRANGE calls on whole columns | Import only the columns you need; consider an Apps Script that copies data nightly |
💡 Anyone who can edit the summary can see all imported data, even if they have no access to the branch files. Share the summary carefully.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong