Cara menggunakan importrange spreadsheet

Google Sheet saat ini sudah menjadi alat pengolah data yang cukup powerful untuk keperluan bisnis. Jika harus dibandingkan dengan Microsoft Excel, Google Sheet menawarkan hal-hal yang saat ini belum bisa ditawarkan oleh Microsoft Excel di antaranya adalah Gratis! (semua orang suka hal gratis bukan), kemudahan untuk berkolaborasi secara “real-time” (Ms Office 2016 sudah menawarkan hal ini, walau belum terlalu populer), kemudahaan konektivitas dengan aplikasi Google lain (kamu bisa mengirim email menggunakan Gmail langsung dari Google Sheet misalnya) dan banyak hal lainnya. Untuk tulisan pertama ini saya akan membahas tentang penggunaan Query dan Importrange untuk proses pengolah data di Google Sheet.

Jika kamu adalah pengguna setia Google Sheet tentu tidak asing dengan fungsi Importrange, fungsi ini sesuai namanya bertugas untuk meng-import data dari worksheet lain (kamu harus mempunyai akses ke worksheet tersebut tentunya) ke dalam worksheet yang kita miliki, penjelasan resminya dapat dibaca di tauntan ini. Selain Importrange fungsi lain yang sejenis adalah Importxml, Importhtml, Importfeed, Importdata yang mungkin akan kita bahas di lain waktu.

Importrange tidak hanya terbatas untuk satu sumber saja melainkan kita dapat menggabungkan data dari beberapa worksheet ke dalam 1 worksheet, contoh penggunaan akan dibahas pada tulisan lainnya karena sesuai judul kali ini kita akan fokus pada penggunaan Query dan Importrange.

Jika banyak pengguna Google Sheet familiar dengan Importrange, lain halnya dengan fungsi Query, walaupun menawarkan fungsi yang sangat powerful sepertinya fungsi ini belum banyak diketahui atau gunakan oleh para pengguna aktif Google Sheet (setidaknya di lingkungan saya berada).

Bagi pengguna SQL akan segera familiar dengan fungsi Query yang ada di Google Sheet, mengapa? karena dasar penulisan parameter pada fungsi ini mengacu kepada Google Visualization API Query Language yang menurut saya menggunakan logic yang sama saat kita menggunakan SQL contoh seperti Select * untuk memanggil seluruh data.

Kembali ke Query, pada dasarnya fungsi ini bertugas untuk melakukan manipulasi data dan format sesuai dengan request kepada sumber data dan memastikan isi dan struktur data yang disampaikan sesuai dengan yang diharapkan.

Berdasarkan penjelasan singkat tentang dua fungsi tersebut, maka dengan menggabungkan fungsi Query dan Importrange maka kita akan dapat melakukan import data dari worksheet lain ke dalam worksheet kita dan melakukan manipulasi format dan isi sesuai dengan kebutuhan kita (sudah cukup keren kan!).

Contoh kita memiliki data penjualan PT. ZXY dan data ini dipergunakan oleh seluruh departemen (jadi agak riskan kalo harus mengubahnya secara langsung), sebagai team operasional kita ingin mengetahui status masing-masing order apakah Cancelled, Shipped atau lainnya. akses data disini

Sebagaimana terlihat data kita memiliki 25 kolom dan 2,833 baris termasuk header, untuk keperluan training kita kali ini kita akan menggunakan data Order Number yang ada di Kolom A dan kolom status yang ada di kolom G.

Cara yang paling jamak dilakukan adalah men-unduh seluruh data untuk kemudian di olah menggunakan Pivot atau Count di Ms Excel, walau cara ini dapat diterima tetapi mengharuskan kita membuka file, men-unduh, membuka file hasil unduhan, melakukan perhitungan setiap kali data terbaharui (update) karena kalian sedang membaca artikel ini so let’s make our life easier.

Step pertama yang harus kita lakukan adalah mengkoneksikan Source Data dengan worksheet yang akan kita gunakan, untuk itu kita menggunakan fungsi Importrange.

Karena kita tentukan range A:Y sebagai sumber maka kita akan mendapatkan seluruh data di Source, berhubung saat ini kita hanya membutuhkan total order dari masing-masing status maka kita membutuhkan Query agar data yng disajikan sesuai dengan format dan informasi yang kita inginkan, untuk pertama kita perlu tambahkan fungsi Query di depan formula Importrange. Sehingga menjadi

=Query(Importrange(“1Av2OvvqSY0nK5VD19xAfbQFmQk9hXWbwzexHan1ehpw”,”sales_data!A:Y”), [query], [header])

Untuk saat ini Importrange sudah menjadi sumber data fungsi Query sehingga kita akan memiliki 25 kolom terdiri dari Col1, Col2, Col3, Col4 … sampai dengan Col25 dengan 2,833 row dataset.

Untuk [query] isi menggunakan formula query yang kita inginkan, untuk contoh data kita membutuhkan status pengiriman yang berada di kolom G atau Col7 maka kita menuliskan harus menuliskan Select diikuti dengan urutan kolom:

“Select Col7”.

Karena kita ingin menghitung berapa banyak order per status maka kita akan membutuhkan kolom Order Number dan dengan menggunakan fungsi Count pada kolom ordernumber di kolom A atau Col1 sehingga query kita menjadi:

“Select Col7, Count(Col1)”

Seperti terlihat kita memisahkan antar kolom menggunakan koma (,) kemudian memberikan fungsi operator Count untuk Col1 sehingga menjadi Count Col1.

Penggunaan Count mensyaratkan kita melakukan data grouping jika tidak maka fungsi akan menunjukan nilai error #VALUE sebagaimana terlihat, untuk itu kita perlu menambahkan parameter Group By berdasarkan status pada kolom G atau Col7 sehingga query kita menjadi:

“Select Col7, Count(Col1) group by Col7”

Untuk selanjutnya, kita menginginkan data yang disajikan hanya data yang memiliki nomor order, sehingga perlu kita tambahakan logika where pada query kita dan tempatkan diantara parameter select dan group by, sehingga saat ini query kita menjadi:

“Select Col7, Count(Col1) where Col1 is not null group by Col7 ”

Mengapa kita tidak menggunakan Count(Col1) setelah where, hal ini dikarenakan kita menginginkan fungsi melakukan pemeriksaan pada sumber data di Col1 dan bukan pada Count(Col1). is not null sendiri dapat diartikan data tidak kosong.

Data kita semakin menunjukan hasil sesuai yang kita inginkan, tetapi terlihat data masih mengikut sertakan Row header STATUS.

Untuk menghilangkan header kita dapat menambahkan 1 setelah parameter [query] sehingga formula komplit kita menjadi seperti ini:

=query(importrange(“1Av2OvvqSY0nK5VD19xAfbQFmQk9hXWbwzexHan1ehpw”,”sales_data!A:Y”), “Select Col7, Count(Col1) where Col1 is not null group by Col7 ”, 1)

Untuk lebih mempercantik, kita dapat mengganti label pada header count ORDERNUMBER dengan menggunaan parameter label.

=query(importrange(“1Av2OvvqSY0nK5VD19xAfbQFmQk9hXWbwzexHan1ehpw”,”sales_data!A:Y”),”Select Col7, Count(Col1) where Col1 is not null group by Col7 label Count(Col1) ‘TOTAL ORDER’”,1)

Hasil dapat diakses di sini, paramater lain yang dapat dipergunakaan adalah Order By, Limit, Offset, Format, Options, Pivot.

Keuntungan menggunakan Query + Importrange adalah data bersifat dinamis dalam artian setiap perubahan data yang dilakukan pada data source maka akan juga merubah informasi pada data kita, ringan karena tidak perlu men-download seluruh data dan konsitensi data dapat terpelihara karena kita hanya melakukan proses stream dari data asal tanpa melakukan perubahan di workbook atau worksheet.

Bagaimana cara menyalin data antar sheet dari Sheet1 ke sheet2?

Bagaimana cara berbagi Google Spreadsheet?

Bagaimana cara mengambil data dari sheet lain?

Bagaimana cara mengakses spreadsheet?