Cara Menganalisis Data di Google Sheets dengan Python: Panduan Langkah demi Langkah

3. Siapkan koneksi

Saat berada di dalam workbook, klik View > Databases lalu klik ikon +. Anda akan melihat ringkasan semua teknologi basis data yang dapat dihubungkan oleh DataLab:

Pilih Google Sheets. Sekarang Anda diminta untuk memberikan izin kepada DataCamp untuk mengakses file di Google Drive Anda.

Klik "Grant permission to Google Drive." Terakhir, pemilih file akan muncul:

Pilih file Spreadsheet yang ingin Anda baca datanya. Ini bisa berupa salinan Google Sheet contoh yang kami sediakan atau Google Sheet milik Anda.

4. Query file Google Sheets

Sekarang Anda dapat melakukan query pada file Google Sheets yang baru saja Anda hubungkan! Anda akan melakukannya dengan sel SQL, yaitu blok bangunan di DataLab untuk melakukan query basis data dengan SQL. Jika Anda membuat workbook dari templat yang disediakan, sel SQL ini sudah tersedia di notebook Anda. Jika Anda memulai workbook dari awal, Anda dapat menambahkannya dengan membuka launcher dan mengklik "SQL":

Di dalam sel SQL, klik dropdown Source di pojok kiri atas.

Pilih spreadsheet yang ingin Anda analisis (dalam contoh ini namanya "Unicorn Companies"). Selanjutnya, tulis query SQL berikut:

SELECT * FROM unicorn_companies

Jika Anda mengklik Run, query ini akan mengambil semua baris pada sheet unicorn_companies di dalam Google Sheet Unicorn Companies, dan menyimpan hasilnya dalam Pandas DataFrame baru bernama df:

Pandas DataFrame adalah struktur data mirip tabel yang dapat Anda gunakan di Python untuk menyimpan dan memanipulasi data. Mari tambahkan sel Python, lalu jalankan kode berikut yang menghitung median pendanaan untuk perusahaan unicorn dalam dataset berdasarkan negara:

median_funding_df = df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

Pada sel SQL di atas, kita menggunakan query SQL langsung untuk mengambil semua data, tetapi Anda juga dapat menggunakan pernyataan SQL yang lebih canggih untuk mengambil 10 perusahaan unicorn paling bernilai di Amerika Serikat, misalnya:

SELECT *
FROM unicorn_companies
WHERE country = 'United States'
ORDER BY valuation DESC
LIMIT 10

Itu saja! Untuk kiat lanjutan tentang cara menyesuaikan query SQL lebih jauh, misalnya untuk menyoroti bagian tertentu dari sebuah sheet, lihat artikel dokumentasi DataLab terkait.

Seperti disebutkan sebelumnya, koneksi mulus dengan Google Sheets ini hanya dapat digunakan untuk membaca data dari Google Sheet. Jika Anda ingin membaca data sekaligus menulis hasil kembali ke Google Sheets, lihat bagian berikut yang menjelaskan cara memanfaatkan Google Sheets API untuk penyiapan yang sangat fleksibel.

Menggunakan Google Sheets API

Pilih opsi ini jika Anda ingin membaca data dari Google Sheet sekaligus menulis hasil perhitungan Anda di Python kembali ke Google Sheet yang sama atau lainnya.

Seperti disebutkan dalam pengantar, peningkatan fleksibilitas ini memerlukan penyiapan yang lebih mendalam. Secara khusus, langkah-langkah yang akan kita lakukan adalah:

• Konfigurasikan Google Service Account
• Buat Google spreadsheet dengan data
• Buat workbook baru
• Simpan kredensial service account Anda di DataLab
• Baca data Google Sheets
• Analisis data di Python
• Tulis hasil ke Google Sheets

Mari kita bahas masing-masing secara terpisah.

1. Konfigurasikan Google Service Account

Untuk mengakses data di Google Sheets secara terprogram, Anda perlu membuat Google service account, yaitu jenis akun khusus yang dapat digunakan program untuk mengakses sumber daya Google seperti spreadsheet. Anda kemudian akan menggunakan service account ini untuk menghubungkan DataLab ke Google Sheets.

Anda hanya perlu menyiapkan Google service account ini sekali untuk setiap akun Google tempat Anda ingin mengakses spreadsheet; Anda dapat melewati langkah ini saat berikutnya ingin bekerja dengan data spreadsheet di akun yang sama.

Ikuti langkah-langkah di bawah ini untuk membuat service account dan menghasilkan kredensial yang diperlukan.

• Pastikan Anda sudah masuk dengan akun Google Anda.
• Navigasikan ke Google API Library
• Buat proyek baru dengan mengklik dropdown di navbar.
• Cari "Google Sheets API" dan aktifkan. Ini dapat memakan waktu hingga 10 detik.
• Di navbar "APIs and services" di sebelah kiri, buka "Credentials tab"
• Klik "+ CREATE CREDENTIALS" dan pilih "Service Account"
• Pada langkah pertama (service account details), berikan nama untuk service account, misalnya "gsheet-operator" lalu klik "Create and continue"
• Pada langkah kedua, pilih peran "Owner" lalu klik "Continue"
• Pada langkah ketiga, jangan ubah apa pun dan klik "Done"
• Setelah kembali ke halaman Credentials, klik service account yang baru saja Anda buat.
• Buka tab Keys, klik "Add Key > Create new key"
• Pilih "JSON", lalu klik "Create." File JSON dengan kredensial service account Anda akan otomatis diunduh ke komputer Anda.

Sekarang Anda memiliki service account dan file kredensial JSON! Buka folder Downloads atau lokasi tempat file JSON diunduh, buka, dan lihat isinya. Seharusnya terlihat seperti ini:

{
  "type": "service_account",
  "project_id": "<your-project-name>",
  "private_key_id": "<something-private>",
  "private_key": "-----BEGIN PRIVATE KEY-----\nM<some-very-private-stuff\n",
  "client_email": "gsheets-operator@steam-verve-386214.iam.gserviceaccount.com",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets-operator%40<project-name>.iam.gserviceaccount.com"
}

Ada kolom "client_email" di sana: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Salin email ini ke papan klip Anda; Anda akan membutuhkannya pada langkah berikutnya.

2. Buat Google Spreadsheet Dengan Data

Sebelum Anda dapat menganalisis data di spreadsheet, pastikan Anda memiliki spreadsheet yang berisi data. Jika Anda tidak memiliki dataset, Anda dapat mulai dari Google Sheet yang kami siapkan untuk tutorial ini: Buka spreadsheet contoh dan saat berada di Google Sheet, klik "File > Make a copy", tentukan nama, lalu klik "Make a copy." Jika Anda sudah memiliki spreadsheet dengan data yang ingin dianalisis, cukup buka spreadsheet tersebut.

Baik Anda bekerja dengan duplikat spreadsheet contoh maupun spreadsheet milik Anda, Anda perlu memberikan akses kepada Google service account yang Anda buat pada langkah pertama ke spreadsheet tersebut:

• Klik "Share"
• Tambahkan email service account yang Anda salin ke papan klip pada langkah sebelumnya sebagai editor ke spreadsheet (misalnya gsheets-operator@<google-project-name>.iam.gserviceaccount.com)
• Klik "Send"

Baik, service account: beres. Google spreadsheet dengan akses yang tepat: beres. Saatnya menulis kode Python!

3. Buat Workbook Baru

Berikutnya adalah membuat proyek data baru di dalam DataLab, juga disebut workbook. Anda bisa membuat workbook kosong dari awal, yang berguna jika Anda ingin menulis semua kode sendiri, atau membuat workbook yang sudah menyertakan semua kode.

4. Simpan Kredensial Service Account Anda di DataLab

Untuk menggunakan file JSON kredensial service account di workbook yang baru Anda buat, Anda perlu menyimpannya di DataLab.

Anda bisa menyalin dan menempelkan isi JSON ke dalam sel kode di workbook baru Anda:

Ini bukan pendekatan yang aman dan tidak direkomendasikan. Jika Anda membagikan notebook Anda dengan orang lain, mereka dapat melihat kredensial tersebut dalam teks jelas. Mereka kemudian dapat menyamar sebagai Anda dan mendapatkan akses ke sumber daya yang dapat diakses oleh service account ini.

Pendekatan ini juga mengharuskan Anda mengulangi kode ini di setiap workbook yang ingin Anda gunakan untuk mengakses data spreadsheet. Jika Anda ingin memutar (rotate) kredensial, Anda harus membuka dan mengedit setiap workbook.

Pendekatan yang lebih aman dan skalabel adalah menyimpan JSON dalam environment variable. Saat Anda menghubungkan environment variable ini, variabel tersebut akan tersedia di sesi Python Anda. Di workbook Anda:

• Klik tab "Environment" di sisi kiri
• Klik ikon plus di sebelah "Environment Variables"
• Di modal "Add Environment Variables":
  • Set "Name" menjadi GOOGLE_JSON
  • Set "Value" ke seluruh isi file JSON service account yang diunduh. Anda dapat melakukannya dengan membuka file JSON, pilih semua, salin ke papan klip, lalu tempelkan ke kolom Value.
  • Set "Environment Variables Set Name" menjadi "Google Service Account" (nama ini bebas)

Setelah semua kolom diisi, klik "Create", "Next", dan akhirnya "Connect." Sesi DataLab Anda akan dimulai ulang, dan GOOGLE_JSON sekarang tersedia sebagai environment variable di workbook Anda. Anda dapat memverifikasinya dengan membuat sel Python berisi kode berikut lalu menjalankannya:

import os
google_json = os.environ["GOOGLE_JSON"]
print(google_json)

Jika Anda ingin menggunakan ulang kredensial service account yang sama di workbook lain, Anda tidak perlu menyiapkan environment variable lagi: Anda dapat menggunakan ulang environment variable tersebut di workbook lain.

5. Baca Data Google Sheets

Sekarang bagian yang menyenangkan: menulis kode Python untuk terhubung ke data Google Sheets.

Instal dan impor paket yang tepat

Mulailah dengan memasang dan mengimpor semua paket yang diperlukan untuk menyiapkan koneksi ke akun Google API Anda. Kita akan menggunakan google-auth untuk melakukan autentikasi ke Google API, gspread untuk mempermudah antarmuka dengan Google spreadsheet, dan pandas untuk memanipulasi data tabular di Python.

%%capture
!pip install gspread
from google.oauth2 import service_account
import pandas as pd
import gspread
import json
import os

Siapkan klien dengan kredensial berscope

Muat environment variable GOOGLE_JSON dan simpan dalam variabel:

import os
google_json = os.environ["GOOGLE_JSON"]

Buat objek kredensial dari JSON kredensial service account:

service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)

Tetapkan 'API scope' yang tepat pada kredensial agar memiliki izin yang sesuai untuk berinteraksi dengan spreadsheet Google Sheet:

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)

Buat klien baru dengan kredensial yang telah diberi scope ini:

client = gspread.authorize(creds_with_scope)

Baca data spreadsheet ke DataFrame

Dengan klien siap, kita dapat membaca data dari spreadsheet yang kita buat sebelumnya.

Pertama, buat instance sheet baru dari URL spreadsheet:

spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/spreadsheet_id/edit#gid=0')

Selanjutnya, fokus pada worksheet (tab di Google spreadsheet) yang ingin Anda ambil datanya. Mari ambil sheet pertama menggunakan indeks 0:

worksheet = spreadsheet.get_worksheet(0)

Pertama, ambil semua record sebagai JSON:

records_data = worksheet.get_all_records()

Selanjutnya, ubah record JSON menjadi pandas DataFrame:

records_df = pd.DataFrame.from_dict(records_data)
records_df

Berhasil!

6. Analisis Data Spreadsheet di Python

Sekarang Anda dapat menggunakan Python untuk menganalisis data yang Anda baca dari Google spreadsheet. Kode minimal di bawah ini mengasumsikan bahwa Anda bekerja dengan spreadsheet contoh. Jika Anda bekerja dengan dataset lain, langkah-langkah ini mungkin tidak berlaku. Lihat kursus Data Manipulation with pandas dari DataCamp jika Anda ingin mendalami lebih jauh!

Mari hitung median pendanaan per negara:

median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

7. Tulis Hasil ke Google Sheets

Jika Anda ingin para pengguna spreadsheet yang bekerja sama dengan Anda melihat hasil kerja Anda di spreadsheet, Anda dapat menulis kembali hasil analisis Python ke worksheet (tab) Google baru di dalam spreadsheet asli.

Dalam contoh ini, kita membuat worksheet baru median_country jika belum ada, lalu memasukkan konten DataFrame median_funding_df, termasuk header dengan nama kolom. Kode ini dibuat dengan bantuan AI Assistant!

# Convert the median_funding_df DataFrame to a list of lists
medians_list = median_funding_df.values.tolist()

# Add the header row to the list
medians_list.insert(0, median_funding_df.columns.tolist())

# Check if the 'median_by_country' sheet already exists
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # Create a new worksheet named 'median_by_country' in the Google Sheet
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# Write the medians_list to the new worksheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)

Jika Anda membuka kembali spreadsheet, Anda akan melihat worksheet baru dengan data dari DataFrame! Penulisan kembali hasil ini hanya dimungkinkan jika Anda menggunakan cara koneksi berbasis Google Sheets API; bukan jika Anda menggunakan konektor Google Sheets bawaan dari DataCamp yang diperkenalkan sebelumnya dalam tutorial ini.

Kesimpulan

Bergantung pada kebutuhan Anda, menganalisis data di Google Sheets dengan Python bisa berjalan mulus atau memerlukan penyiapan awal yang cukup banyak. Jika Anda hanya ingin membaca data dari sebuah sheet, gunakan konektor bawaan dan Anda siap dalam kurang dari lima klik. Jika Anda ingin membaca data sekaligus menulis data ke Google Sheets, ada baiknya mengaktifkan Google Sheets API dan menyiapkan service account.

Pada kedua kasus, setelah semuanya dikonfigurasi dengan benar, mengakses data dari Google Sheets hampir semudah melakukan query pada file CSV. Keuntungan besar lainnya adalah Anda dapat bertemu para pemangku kepentingan di tempat mereka berada: kemungkinan sebagian rekan Anda sangat mengandalkan spreadsheet.

Sudahi masa ekspor CSV yang melelahkan dan sambut alur kerja bertenaga otomatis untuk bekerja dengan data Google Sheets tanpa pernah keluar dari DataLab.