Pada tulisan ini akan diuraikan tahapan untuk mengambil data dari file Google Sheet kemudian diolah lebih lanjut di Microsoft excel dengan menggunakan tool Power Query. Tool ini sudah ada di Microsoft Excel 2010 pro sampai sekarang yang digunakan untuk melakukan koneksi ke beberapa type database, halaman web, text file, transformasi data dan analisa data. Khusus di Microsoft Excel 2010 pro dan 2013, Power Query tersedia dalam bentuk add-in sedangkan untuk versi setelah itu sudah menjadi built-in tool. Cara instalasi power query silakan mengacu ke https://www.howtoexcel.org/power-query/how-to-install-power-query/

Saat menulis tutorial ini, saya menggunakan dimana tool Power Query ini ada di menu [Data] pada ribbon [Get & Transform Data]

Gambaran file google sheet yang akan diolah berupa catatan progress harian pekerjaan pengukuran bidang tanah yang terbagi dalam beberapa sheet. Tiap sheet menunjukkan desa sedangkan dalam sheet terdapat catatan harian yang dibedakan berdasarkan blok.

Contoh salah satu sheet di google sheet:

Tiga (3) desa lainnya menggunakan format yang sama tetapi disimpan dalam sheet terpisah.

Hasil yang akah dihasiklan adalah rekap progress tiap blok di masing-masing desa lengkap dengan tanggal terakhir laporan di masing-masing blok.

Berikut Langkah-langkah yang aku kerjakan. Mohon saran dan usulan jika ada metode yang lain selain yang aku uraikan dalam tutorial ini.

Setting Shareable Link di Google Sheet

Buka file google sheet kemudian click tombol di kanan atas Google Sheet.

Pilih setting:

Copy url sheet download file google sheet

Url ini nantinya digunakan sebagai alamat web saat menggunakan get data from web di Power Query.

Pada menu Google Sheet di menu [File] pilih [Download] kemudian pilih [Microsoft Excel (.xlsx).

Tentukan lokasi download di komputer. File di komputer ini sifatnya hanya sementara saja karena pada saat nanti program dijalankan proses pengambilan data akan selalu mengambil data dari google sheet yang tersimpan di google drive.

Jika proses download selesai, tekan Ctrl+J di chrome untuk melihat link hasil download. Click kanan di link download, kemudian pilih [Copy link address]

Simpan / Paste link address tersebut di notepad atau di file lainnya untuk nantinya akan digunakan di power query.

Setting connection ke web di Power Query

1. Jalankan program Excel.
2. Pilih sheet yang kosong kemudian buat table seeperti di bawah ini:

Cell [A2] berisi alamat link download yang dicopy dari google sheet di Langkah sebelumnya.

1. Rubah table di atas menjadi dengan cara blok range [A1:A2], kemudian tekan CTRL+T. Check pilihan [My table has headers].
2. Namai table misalnya : tbl_sheet

1. Convert tabe tbl_gsheet menjadi data connection dengan memilih menu [Data] kemudian [From Table/Range]

Setelah Langkah di atas, maka ditampilkan window Power Query Editor.

1. Click kanan, Task Pane Queries (area kosong di bawah tulisan tbl_gsheet), pilih [New Query]>[Other Sources]>[Web]

Pada isian URL, masukkan lagi alamat link google sheet.

1. Pada setting Navigator, Pilih salah satu sheet yang akan diolah, misal Bantarwuni, kemudian click [OK]

Data yang akan diambil mulai baris ke 5 ke bawah.

Setelah tahap di atas Task Pane Queries menampilkan tambahan query [Bantarwuni]

1. Pilih query [Bantarwuni] kemudian di pilih [Source[ di tahapan Query Setting.

Copy formula di formula bar:

Formula ini akan ditambahkan di query tbl_gsheet.

1. Pilih Query tbl_gsheet kemudian di Power Query pilih menu [Add Column], pilih [Custom Column]

1. Pada column name isikan nama kolom misal Data, kemudian paste formula dari Langkah sebelumnya ke custom colum formula:

1. Text dalam tanda petik hasil copy dari Langkah sebelumnya adalah sama dengan kolom Gsheet, sehingga kita bisa ganti text tersebut, termasuk tanda petiknya, dengan columns [Gsheet]. Blok mulai tanda petik awal sampai tanda petik akhir, lalu pilih kolom Gsheet di Available columns kemudian click [<<Insert], custom column formula menjadi

Click tombol [OK], kemudian click [Continue] saat ada warning data privacy. Anda bisa juga set data privacy setting dengan pilihan yang telah disediakan. Pada tutorial saat ini saya pilih “Ignore Privacy Level”

Click [Save]

Sampai tahap ini , sudah dilakukan setting agar Power Query menjalankan query dengan terlebih dahulu mencari sumber data yang ditulis ditabel tbl_gsheet. Jika menggunakan data yang lain, cukup merubah alamat url di table tbl_gsheet tanpa perlu mengulang Langkah 1-11.

Untuk menyimpan Langkah di atas, sebelum dilanjutkan ke Langkah berikutnya, pada menu [File] di query editor, pilih [Save & Load to] kemudian pilih [Only create connection]

Menggabungkan sheet yang diinginkan

Seperti pada Langkah sebelumnya , proses penggabungan menggunakan fungsi yang di-copy dari query tbl_bantarwuni.

1. Pilih query [Bantarwuni] kemudian di pilih [Source[ di tahapan Query Setting, click setting [Navigation]

   

   Pada tampilan table di atas, baris yang akan diproses adalah mulai baris ke-5 dengan sedangkan untuk baris ke-4 tidak diikutkan dalam proses agar saat penggabungan dari sheet lainnya text Desa, No dan Tanggal tidak muncul di hasil query penggabungan.

    

2. Agar baris 1-3 tidak tampil di table hasil import, saat setting [Navigation] terpilih rubah formula

    

   Semula:

   = Source{[Item=”Bantarwuni”,Kind=”Sheet”]}[Data]

    

   Menjadi:

   =Table.Skip(Source{[Item=”Bantarwuni”,Kind=”Sheet”]}[Data],3)

    

   Sehingga tampilan table menjadi:

   

    

   Formula pada langkah 2 di atas kemudian dicopy ke query tbl_gsheet sehingga nantinya akan berlaku di sheet yang lain tidak hanya di sheet Bantarwuni.

    

3. Pilih query bantarwuni, kemudian click tanda panah / tombol [Expand] di samping tulisan Data

   

   Pilih [Select all columns] kemudian click [OK]

   

   Lakukan filter Data.Kind = Sheet dan [Data.Item]=Petahunan, Pangebatan dan Bantarwuni. [Data.Item] berisi data sheet yang mempunyai struktur table yang sama dengan table/sheet bantarwuni yang akan kita gabung menjadi satu table untuk proses Analisa selanjutnya.

    

4. Pilih kolom [Data.Data] kemudian dari menu [Home], pada pilihan [Remove Columns], click [Remove Other Columns]

    

   

   Table menjadi:

   

5. Langkah selanjutnya adalah membuat kolom baru yang berisi table baru dengan tidak mengikutkan data di baris 1 sampai 3 (Langkah 2).

   Click [Add Colum]>[Custom Column]

    

   Pada New Column Name isikan Data kemudian paste formula =Table.Skip(Source{[Item=”Bantarwuni”,Kind=”Sheet”]}[Data],4) ke custom column formula kemudian rubah formula tersebut menjadi

   

   Kemudian click [OK]

6. Pilih column [Data] kemudian pilih [Remove Other Column] dari menu [Home], sehingga query hanya menampilkan kolom [Data]
7. Click tombol [Expand] di samping kanan tulisan [Data], kemudian pilih [Select All Columns], Hasil dari proses menghasilkan table baru hasil gabungan dari sheet yang dipilih pada Langkah 3, tetapi belum ada nama headernya.

    

   

    

8. Jadikan baris pertama sebagai nama header dengan cara click tombol [Use First Row as Header] di menu [Transform]

   

    

9. Hasil dari Langkah-9, text header dari sheet yang lain masih masuk di hasil query gabungan, untuk menghilangkannya filter header [Desa] kemudian uncheck tulisan “Desa” dan (null)

Analisa Produktivitas Harian dengan Menggunakan Fungsi Agregat

Pada tahap ini akan dilakukan rangkuman progress pengukuran tiap blok di masing-masing desa dengan menjumlahkan progress harian di masing-masing blok.

1. Pilih header atau kolom [Blok-01] sampai [Blok-68]
2. Kemudian dari menu [Transform], pilih [Unpivot Column]
3. Tekan tombol CTRL, pilih kolom [Desa], [Tanggal], [Atribute] dan [Value] kemudian dari menu [Home] pilih [Remove other column] sehingga tampilan table menjadi:

    

   

    

4. Tekan tombol CTRL, pilih kolom [Desa],dan [Atribute], kemudian dari menu [Transform], pilih [Group by]
5. Lakukan setting sebagai berikut:

    

   

   Kemudian click OK

    

6. Expand tombol Rangkuman, kemudian pilih [Agregate]
7. Pada Agregate [tanggal], pilih Minimum dan Maximum untuk mengetahui tanggal awal dan akhir pengukuran

   

8. Kemudian pilih Sum of Value untuk menampilkan pemjumlah progress tiap bloknya.
9. Clik [OK]
10. Rubah nama header, kemudian lakukan sorting [Desa] kemudian [Blok] sehingga tabel menjadi

     

    

     

    Pada tahap ini table query bantarwuni tidak diperlukan lagi, table tersebut hanya digunakan untuk keperluan copy formula saja. Cara menghapus query, click kanan query di task pane query kemudian pilih [Delete]

11. Tutup dan simpan hasil query dengan memilih menu [Home], kemudian [Close and Load]

     

     

Menampilkan Hasil Query di Worksheet Excel Sebagai Tabel

1. Pada menu [Data] di Microsoft excel, pilih [Queries & Connections]

   

2. Pada group queries, click kanan query tbl_gsheet kemdian pilih [Load To]
3. Pilih [Table] kemudian pilih [New Worksheet] atau [Existing Worksheet]
4. Maka table akan ditampilkan dalam excel.
5. Lakukan format angka dan tanggal jika diperlukan.

Tabel ini bersifat dinamis, jika ada perubahan di google sheet, maka dengan menkan tombol [Refresh All], maka data otomatis akan terupdate

#silakan dicoba#

• Excel