Cara menggunakan google sheets api query

I'm a self-taught web developer from India. I enjoy learning about new web technologies and working on projects. I hope that my work will assist other developers who are experiencing the same difficulties that I am.

Sebagai aplikasi penunjang pekerjaan, Google Sheet kini telah menjadi aplikasi yang populer dan powerful. Keunggulan yang dihadirkan oleh Google Sheet di antaranya adalah tidak diperlukan biaya untuk mengaksesnya, mudah diakses dari mana saja melalui perangkat apa saja secara real-time, serta kemudahan konektivitas dengan aplikasi Google lainnya.

Query pada dasarnya dapat diartikan sebagai kemampuan untuk menampilkan data dari database untuk diolah lebih lanjut yang biasanya diambil dari tabel tabel dalam database.

Bagi pengguna SQL akan merasa familiar dan mudah memahami fungsi Query yang ada di Google Sheet, karena dasar penulisan parameter pada fungsi ini mengacu kepada Google Visualization API Query Language yang menggunakan logic yang sama saat kita menggunakan SQL contoh seperti Select untuk mengambil data.

Query pada google spreadsheet secara sederhananya dapat diartikan sebagai fungsi untuk mengambil beberapa atau keseluruhan data dari suatu database ke dalam lembar kerja kita. Fungsi ini dapat menggantikan kebiasan copy-paste kita yang terlalu memakan waktu serta kurang praktis untuk data yang besar. Query ini sendiri dapat dimodifikasi dan dikombinasikan dengan beberapa fungsi seperti importrange, arithmetic (SUM, AVG, COUNT etc), dll.

Fungsi Query yang dapat diaplikasikan di Google Sheet meliputi :

• Select
• Where
• Order by
• Limit
• Group by

Sebagai tahap persiapan, kita perlu menyiapkan data yang akan diolah. Pada tulisan ini data yg digunakan yaitu Data Sales Super Store pada .

Langkah pertama yang dapat dilakukan adalah melakukan Named Range, hal ini bertujuan untuk memudahkan penulisan formula query. Langkah — langkahnya yaitu :

• Klik Data, kemudian pilih Named Range

• Tuliskan nama yang akan digunakan, sebagai contoh kita akan menamakan file tersebut dengan Aggregate

• Pilih range yang akan digunakan, dengan men-select kolom kolom yang akan masuk ke dalam range sumber data kita, kemudian klik Done. Setelah selesai Anda akan memiliki range data bernama Aggregate.

Hasil named range

Jika Named Range sudah terbuat, kita dapat langsung mengaplikasikan fungsi Query. Di sini sebagai study case kita akan mengolah sample data Sales Super Store .

Select

Fungsi Query pertama yang akan kita coba yaitu Select, yang digunakan untuk melakukan pengambilan data, baik secara keseluruhan atau hanya beberapa data yang kita butuhkan.

Untuk mengambil data secara keseluruhan formula yang digunakan yaitu : =query(Aggregate, “select*”, -1)

• Aggregate : range nilai yang telah kita berinama dengan “aggregate”
• “select*” : fungsi dari bahasa query untuk mengambil seluruh data dalam tabel range
• -1 : untuk mengambil seluruh data, ini juga bisa dihilangkan hasilnya juga akan sama

Hasil query select untuk mengambil keseluruhan data

Untuk mengambil sebagian data formula yang digunakan yaitu : =query(Aggregate, “select A”,1)

• Aggregate : range nilai yang telah kita berinama dengan “aggregate”
• “select A” : fungsi dari bahasa query untuk mengambil data dari kolom A berdasarkan range nilai
• 1 : untuk mengambil seluruh data, ini juga bisa dihilangkan hasilnya juga akan sama

Hasil query select untuk mengambil data tertentu (kolom A)

Where

Fungsi Where digunakan untuk menampilkan data/mencari nilai dalam suatu kondisi dimana kondisi itu di tentukan berdasarkan atau dari referensi yang kita kehendaki.

Sebagai contoh disini kita akan mencoba menggunakan fungsi where untuk memfilter data super store di mana kita hanya akan mengambil data dengan product category-nya office supplies. Formula yang digunakan yaitu :

=query(Aggregate, “select * where J = ‘Office Supplies’ “)

• Aggregate : range nilai yang telah kita beri nama dengan “aggregate”
• “select *: fungsi dari bahasa query untuk mengambil semua data berdasarkan range nilai
• where : fungsi dari bahasa query untuk memfilter data tertentu sesuai dengan kondisi yang kita tentukan
• where J = ‘Office Supplies’ → merupakan kondisi yang kita kehendaki sebagai filter, dapat diartikan ambil data yang pada kolom J hanya memuat kategori “office supplies”

Hasil fungsi query where yang memfilter data office supplies

Fungsi Where ini dapat digunakan secara lebih kompleks dan costumize. Anda dapat mengambil data dengan filter berdasarkan tanggal, tahun, bulan ataupun parameter lainnya. Untuk hal ini kita bahas di tulisan lainnya ya!

Order by

Fungsi Order by digunakan untuk menampilkan data berdasarkan order baik dari yang terkecil ke yang terbesar atau sebaliknya.

Sebagai contoh kita akan mencoba menggunakan fungsi order by untuk mengurutkan data super store dengan 2 perlakuan yaitu kecil ke besar (ascending) dan besar ke kecil (descending).

Pada case yang pertama kita akan urutkan berdasarkan nilai sales terkecil — terbesar (kolom W). Formula yang digunakan untuk urutan kecil ke besar (ascending) yaitu :

=QUERY(Aggregate, “select * order by W”)

• Aggregate : range nilai yang telah kita beri nama dengan “aggregate”
• “select *: fungsi dari bahasa query untuk mengambil semua data berdasarkan range nilai
• order by W : fungsi dari bahasa query untuk mengurutkan data berdasarkan kolom W dari yang terkecil ke terbesar

Hasil fungsi query order by secara ascending

Data yang ditampilkan akan sesuai dengan urutan nilai sales (kolom W) dari kecil ke besar. Sedangkan jika ingin menampilkan data dengan urutan nilai besar ke kecil, kita dapat menambahkan desc pada rumus tadi seperti berikut : =QUERY(Aggregate, “select * order by W desc”)

• Aggregate : range nilai yang telah kita beri nama dengan “aggregate”
• “select *: fungsi dari bahasa query untuk mengambil semua data berdasarkan range nilai
• order by W : fungsi dari bahasa query untuk mengurutkan data berdasarkan kolom W dari yang terbesar ke terkecil

Hasil fungsi query order by secara descending

Group by

Group berfungsi untuk mengkelompokan data yang akan ditampilkan. Misal pada data super-store tadi kita ingin menampilkan jumlah data penjualan berdasarkan wilayah, maka rumus yang dapat digunakan yaitu :

=query(Aggregate, “select P, sum(W) group by P”)

• Aggregate : range nilai yang telah kita beri nama dengan “aggregate”
• “select P, sum(W): fungsi dari bahasa query untuk mengambil data kolom P dan jumlah kolom W
• group by P : fungsi dari bahasa query untuk mengelompokan data berdasarkan kolom P

Hasil fungsi query group by pada data jumlah penjualan berdasarkan wilayah

Maka akan tampil data jumlah penjualan berdasarkan wilayah.

Kemudian kita dapat mengkombinasikan rumus group by dengan order by untuk mendapatkan jumlah data penjualan berdasarkan wilayah yang telah diurutkan berdasarkan dari yang terbesar hinggal terkecil dengan menggunakan rumus :

=query(Aggregate, “select P, sum(W) group by P order by sum(W) desc”)

• Aggregate : range nilai yang telah kita beri nama dengan “aggregate”
• “select P, sum(W): fungsi dari bahasa query untuk mengambil data kolom P dan jumlah kolom W
• group by P : fungsi dari bahasa query untuk mengelompokan data berdasarkan kolom P
• order by sum(W) desc : fungsi bahasa query untuk mengurutkan data berdasarkan total data W dari terbesar ke terkecil

Hasil fungsi query group by pada data jumlah penjualan berdasarkan wilayah yang diurutkan dari besar ke kecil

Demikian Query pada Google Sheet Spreadsheet ini, selanjutnya di tulisan lain kita akan membahas mengenai Query yang dikombinasikan dengan importrange dan aritmatika lainnya. Semoga tulisan ini membawa manfaat dan jika ada saran ataupun pertanyaan feel free to comment ya!

Apa saja rumus spreadsheet?

Apa itu data Query?

Importrange untuk apa?

Apa saja fitur utama dari Google Sheets?