Tiap cabang punya spreadsheet sendiri, tapi kamu butuh satu file pusat yang nampung semuanya? IMPORTRANGE narik data dari file lain secara otomatis — begitu cabang update, pusat ikut update. Nggak perlu copy-paste tiap hari. Kita bahas dari struktur sampai cara gabungin banyak cabang.
Each branch has its own spreadsheet, but you need one central file holding everything? IMPORTRANGE pulls data from other files automatically — when a branch updates, the center updates too. No daily copy-paste. We cover it from the structure to merging many branches.
Fungsinya menghubungkan antar file Google Sheets yang berbeda. Data tetap tinggal di file aslinya, tapi “cerminnya” muncul di file kamu dan ikut berubah otomatis tiap kali sumbernya diubah. Ideal buat konsolidasi data dari banyak sumber ke satu tempat.Its job is to link separate Google Sheets files. The data stays in its original file, but a “mirror” of it appears in yours and updates automatically whenever the source changes. Ideal for consolidating data from many sources into one place.
=IMPORTRANGE("url_file"; "Sheet1!A1:D"). Argumen pertama adalah alamat (URL atau ID) file sumber, kedua nama sheet plus rentang yang mau diambil. Hasilnya seluruh rentang itu muncul di filemu, mulai dari sel tempat kamu nulis rumusnya.=IMPORTRANGE("file_url", "Sheet1!A1:D"). The first argument is the source file's address (URL or ID), the second is the sheet name plus the range to pull. The whole range then appears in your file, starting at the cell where you wrote the formula.
Pertama kali dipakai, selnya bakal muncul #REF! dengan tombol “Izinkan akses”. Klik itu sekali buat menyambungkan kedua file — ini semacam jabat tangan antar dokumen. Tanpa langkah ini, datanya nggak akan masuk sama sekali.The first time you use it, the cell shows #REF! with an “Allow access” button. Click it once to connect the two files — it's a handshake between the documents. Without this step, no data comes through at all.
Izin ini antar file, bukan antar orang. Pastikan akun yang buka file pusat juga punya akses (minimal view) ke file sumber — kalau nggak, IMPORTRANGE nggak bisa narik.This access is between files, not people. Make sure the account opening the central file also has at least view access to the source file — otherwise IMPORTRANGE can't pull.
IMPORTRANGE narik semua yang ada di rentang, tapi sering kamu cuma butuh sebagian — misal cuma transaksi yang lunas. Bungkus pakai QUERY: =QUERY(IMPORTRANGE(...); "select Col1, Col3 where Col2='Lunas'"). Jadi kamu narik sambil nyaring dan milih kolom sekaligus.IMPORTRANGE pulls everything in the range, but often you need only part — say only paid transactions. Wrap it in QUERY: =QUERY(IMPORTRANGE(...), "select Col1, Col3 where Col2='Paid'"). So you pull, filter, and pick columns all at once.
Catatan: di dalam QUERY, kolom disebut Col1, Col2, dst (bukan huruf A, B), karena yang dibaca adalah hasil IMPORTRANGE, bukan posisi sel di filemu.Note: inside QUERY, columns are referred to as Col1, Col2, etc. (not letters A, B), because it's reading the IMPORTRANGE result, not your file's cell positions.
Buat nyatuin beberapa file sekaligus, tumpuk pakai kurung kurawal: ={IMPORTRANGE(A); IMPORTRANGE(B); IMPORTRANGE(C)} menumpuk data B dan C di bawah A jadi satu tabel panjang. Syaratnya: jumlah kolom tiap IMPORTRANGE harus sama, kalau beda hasilnya error.To merge several files at once, stack them with curly braces: ={IMPORTRANGE(A); IMPORTRANGE(B); IMPORTRANGE(C)} stacks B's and C's data below A's into one long table. The condition: each IMPORTRANGE must have the same column count, or it errors.
Mau rekap langsung dari tumpukan itu? Bungkus semuanya dengan QUERY di luar kurung kurawal buat jumlahin atau kelompokin seluruh cabang sekaligus.Want to recap straight from that stack? Wrap the whole thing in a QUERY outside the braces to sum or group all branches at once.
Cabang Jakarta, Bandung, Surabaya masing-masing punya file penjualan dengan format kolom yang sama. Di file pusat, satu sheet “Gabungan” pakai ={IMPORTRANGE(url_jkt;"Data!A2:E"); IMPORTRANGE(url_bdg;"Data!A2:E"); IMPORTRANGE(url_sby;"Data!A2:E")} — semua transaksi tiga cabang jadi satu tabel.The Jakarta, Bandung, and Surabaya branches each have a sales file with the same column format. In the central file, one “Combined” sheet uses ={IMPORTRANGE(jkt_url,"Data!A2:E"); IMPORTRANGE(bdg_url,"Data!A2:E"); IMPORTRANGE(sby_url,"Data!A2:E")} — all three branches' transactions in one table.
Dari sheet gabungan itu, kamu tinggal pasang pivot atau SUMIFS buat lihat total per cabang, per produk, per bulan. Tiap cabang cukup update file mereka sendiri, dan laporan pusat ikut hidup otomatis — nggak ada lagi tarik-tarik file tiap akhir bulan.From that combined sheet, just add a pivot or SUMIFS to see totals per branch, product, or month. Each branch just updates their own file, and the central report stays live automatically — no more collecting files every month-end.
Tips: IMPORTRANGE bisa bikin file lemot kalau narik puluhan ribu baris dari banyak sumber. Tarik secukupnya (batasi rentang/kolom yang beneran dipakai), dan kalau cuma butuh ringkasan, bungkus langsung dengan QUERY biar yang masuk cuma hasil rekapnya.Tip: IMPORTRANGE can slow a file if it pulls tens of thousands of rows from many sources. Pull only what you need (limit the range/columns actually used), and if you only need a summary, wrap it in QUERY so only the recap comes in.
(1) Performa — makin banyak dan besar sumbernya, makin berat; pertimbangkan narik hasil rekap, bukan data mentah. (2) Izin bisa putus — kalau file sumber dipindah/dihapus atau aksesnya dicabut, muncul #REF lagi. (3) Jumlah kolom harus sama saat pakai kurung kurawal. (4) Jangan buat edit — hasil IMPORTRANGE cuma bisa dibaca, nggak bisa diketik ulang di file pusat.(1) Performance — the more and bigger the sources, the heavier; consider pulling the recap, not raw data. (2) Access can break — if a source file is moved/deleted or access is revoked, #REF returns. (3) Column counts must match when using curly braces. (4) Not for editing — the IMPORTRANGE result is read-only; you can't type over it in the central file.
Pola yang sering dipakai:Common patterns:
| TujuanGoal | RumusFormula |
|---|---|
| Tarik dataPull data | =IMPORTRANGE("url";"Sheet1!A:D")=IMPORTRANGE("url","Sheet1!A:D") |
| Tarik + saringPull + filter | =QUERY(IMPORTRANGE(...);"select Col1 where Col2='Lunas'")=QUERY(IMPORTRANGE(...),"select Col1 where Col2='Paid'") |
| Gabung 2 fileMerge 2 files | ={IMPORTRANGE(A);IMPORTRANGE(B)}={IMPORTRANGE(A);IMPORTRANGE(B)} |
| Gabung + rekapMerge + recap | =QUERY({IMPORTRANGE(A);IMPORTRANGE(B)};"...")=QUERY({IMPORTRANGE(A);IMPORTRANGE(B)},"...") |
IMPORTRANGE adalah kunci laporan multi-cabang tanpa ribet. Sambungkan sekali, kasih izin, dan data pusatmu update sendiri selamanya — tiap cabang cukup ngurus filenya masing-masing.IMPORTRANGE is the key to fuss-free multi-branch reports. Connect once, grant access, and your central data updates itself forever — each branch just minds their own file.
Rakitdata punya puluhan template Google Sheets siap pakai — rumus, dashboard, dan rekapnya udah jalan dari awal.Rakitdata has dozens of ready-to-use Google Sheets templates — formulas, dashboards, and recaps already running from the start.