🗺️
What You're BuildingA live HQ dashboard that automatically pulls sales data from three separate Branch sheets using IMPORTRANGE, displays product images using IMPORTIMAGE, and generates branch-level summaries with QUERY — all updating in real time without any copy-pasting.
Architecture Overview3 Branch Sheets (North, South, East) → IMPORTRANGE into Master Dashboard → QUERY summarises → ARRAYFORMULA calculates totals. Each layer has exactly one job.
🪜
Step-by-Step Guide1
Prepare the Three Branch Sheets
Create 3 separate Google Sheets (or tabs) — one each for Branch North, South, East. Each must have the same columns: Product ID, Product Name, Category, Qty Sold, Unit Price, Branch, Region. Add 10 rows of realistic data per branch. Share each sheet: Share → Anyone with the link → Viewer. Copy the URL of each sheet.
2
IMPORTRANGE — Pull Branch 1 Data
In your Master Dashboard sheet, click cell A2 and enter:
Formula — Cell A2
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/YOUR_BRANCH1_ID",
"Sheet1!A1:G11")
Authorization RequiredThe first time you use IMPORTRANGE, Google shows an "Allow Access" popup. You MUST click it — otherwise the cell shows #REF! and nothing loads. This only happens once per sheet pair.
Repeat for Branch 2 starting at A14 and Branch 3 at A26.
3
IMPORTIMAGE — Display Product Images
In a "Product Images" section (start at column I), use IMPORTIMAGE to fetch and display images:
Formula — Cell I2
=IMPORTIMAGE("https://openweathermap.org/img/wn/[email protected]")
Finding Image URLsRight-click any image on the web → "Copy image address". Make sure the URL ends in .jpg, .png, or .webp. Google Images thumbnail URLs often don't work — use the direct image link.
Will NOT WorkURLs from Google Drive, Dropbox preview links, or Instagram do not work with IMPORTIMAGE. Use CDN-hosted or Wikipedia/Wikimedia URLs.
4
QUERY — Branch Sales Summary
With all branch data in rows 2–35 of your dashboard, use QUERY to summarise by branch:
Formula — Branch Summary
=QUERY(A2:G35,
"SELECT F, SUM(D), SUM(E)
GROUP BY F
ORDER BY SUM(D) DESC
LABEL SUM(D) 'Total Qty', SUM(E) 'Total Revenue'", 1)
SELECT F, SUM(D) GROUP BY F ORDER BY SUM(D) DESC
SELECT F, SUM(D) — Show Branch name (col F) and total Qty (col D)
GROUP BY F — One result row per unique branch name
ORDER BY SUM(D) DESC — Highest quantity branch appears first
, 1 — Tells QUERY that row 1 is a header (skip it)
5
ARRAYFORMULA — Auto-Calculate Total Value
Instead of writing =D2*E2 and dragging it down for every row, use a single ARRAYFORMULA in column H:
Formula — Cell H2 only (no dragging needed)
=ARRAYFORMULA(D2:D * E2:E)
Why ARRAYFORMULA?When new data arrives via IMPORTRANGE, ARRAYFORMULA automatically calculates the Total Value for new rows — no manual dragging ever needed.
6
Revision History — Recover Deleted Data
Your instructor will intentionally delete a column. To recover it: File → Version History → See version history. A timeline appears on the right. Click a timestamp from before the deletion. You'll see the old version highlighted in green. Click "Restore this version".
Key LessonNothing is permanently lost in Google Sheets. Every change is logged automatically. You can name versions: File → Version History → Name current version — do this after every major task.
🔎
IMPORTRANGE vs Copy-Paste: Side by Side❌ Copy-Paste Approach
- ✗Manual — must repeat every week
- ✗Old data shows until you update
- ✗Breaks if branch renames their tab
- ✗No audit trail of data origin
✅ IMPORTRANGE Approach
- ✓Auto-updates when source changes
- ✓Always shows live data
- ✓One formula, infinite rows
- ✓Traceable back to source sheet