Jika Anda terus-menerus mengekspor data PostgreSQL ke Google Sheets untuk membuat laporan bisnis dan Anda bertanya-tanya apakah ada cara sederhana untuk mengatasi tantangan ini, maka Anda telah tiba di tempat yang tepat
Salah satu cara yang sangat mudah untuk menghubungkan PostgreSQL ke Google Spreadsheet adalah KPIBees. Untuk menulis kueri SQL langsung di spreadsheet Google, cukup instal Add-on Google Sheet ini. Anda tidak perlu menulis kode apa pun atau Skrip Google Apps
Dengan Add-on ini, Anda dapat mengekspor data PostgreSQL ke Google Sheets dan menampilkan hasil kueri Anda langsung di sel spreadsheet yang dipilih. Selain itu, Anda dapat menjadwalkan waktu penyegaran otomatis sehingga data selalu diperbarui saat Anda membuka spreadsheet.
Tutorial ini menunjukkan cara melakukan query database PostgreSQL dari Google Sheets dengan Python dan xlwings PRO. PostgreSQL adalah database sumber terbuka yang sangat kuat dan salah satu pilihan paling populer di dunia Python. Tutorial ini berkonsentrasi pada menanyakan database PostgreSQL langsung dari Google Sheets. Ini menyelamatkan Anda dari tugas manual dan rawan kesalahan karena harus mengekspor data dari PostgreSQL ke file CSV, hanya untuk mengimpornya lagi ke Google Sheets. Anda juga dapat memberi pengguna akses hanya baca atau tulis ke data tertentu
Mencari database yang berbeda?
SQLite. MySQL. MariaDB. Server SQL. Peramal
Daftar isi
Mulai cepat
Anda akan menggunakan fungsi runPython xlwings di Google Apps Script untuk memanggil backend Python yang berjalan di server atau layanan cloud Anda. Untuk mengetahui seberapa cepat Anda bisa membuatnya bekerja, ikuti langkah-langkah ini
- Buka proyek di GitHub. xlwings-googlesheets-sql
- Klik tombol Deploy to Render_ pada README proyek untuk menjalankan aplikasi Render gratis (Anda juga dapat melakukan fork repo terlebih dahulu di GitHub agar dapat mengubahnya). Alih-alih Render, Anda juga dapat menerapkan aplikasi ke layanan apa pun yang dapat menangani Python atau Docker
Isi informasi berikut pada Render.
(1) Nama, mis. g. , xlwings-googlesheets-sql_
(2) Domain ruang kerja Google Anda dalam notasi daftar Python. ["your-domain.com"] atau ["your-first-domain.com", "your-other-domain.com"]. Ini adalah domain ruang kerja Google yang akan diberi akses ke aplikasi
(3) Kunci lisensi xlwings PRO Anda (dapatkan kunci uji coba gratis Anda di sini). Perhatikan bahwa xlwings PRO gratis untuk penggunaan non-komersial, lihat dokumennya
(4) String koneksi database Anda. postgresql://user:password@host:port/database. Ini mengasumsikan bahwa Anda memiliki database yang sudah ada yang dapat Anda gunakan. Untuk menggunakan kueri dari template, Anda dapat menggunakan file create_data.py di direktori const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 0 repo GitHub, yang akan membuat tabel const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 1 dengan data dari database sampel AdventureWorks. Di bawah ini kita akan melihat bagaimana kita dapat menjalankan instance PostgreSQL secara lokal menggunakan Docker jika Anda belum memilikinya. Atau Anda dapat menggunakan versi PostgreSQL yang dihosting, seperti yang ditawarkan oleh Render (Anda dapat bermain-main dengannya secara gratis selama 3 bulan). Alih-alih mengisi tabel sampel, Anda juga dapat mengubah kueri SQL agar bekerja dengan salah satu tabel yang ada, lihat di bawahSetelah menekan const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } _2, perlu waktu satu atau dua menit hingga aplikasi Anda aktif dan berjalan. Periksa progres di Dasbor Render (klik logo Render di kiri atas untuk membukanya)
- Buat salinan template Google Sheet dengan mengklik const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 3 di kanan atas
- Di Google Sheets, buka editor Apps Script dengan membuka const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 4 > const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 5, lalu tempelkan URL aplikasi Render Anda (Anda akan menemukannya saat mengeklik proyek Anda di Dasbor Render, di bagian atas halaman)
Di Google Sheets, klik tombol const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 6 untuk menanyakan basis data, secara opsional berikan bagian dari const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 7 di B5 dan (hapus) centang kotak const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 8.
Catatan. saat Anda menjalankan ini pertama kali, skrip akan meminta izin yang diperlukan, yang perlu Anda konfirmasi. Setelah dikonfirmasi, jalankan skrip lagi
Setelah pengantar singkat ini, mari kita lihat apa yang terjadi di balik layar dan bagaimana Anda dapat mengubah kueri SQL
Tunjukkan kodenya
Aplikasi sampel adalah aplikasi web FastAPI sederhana dan seluruh keajaiban terjadi di titik akhir const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); } 9 di bawah version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 0 (periksa repo untuk kode lengkap termasuk impor)
@router.post("/select") def select_employees( data: dict = Body, current_user: User = Security(authenticate), db: Session = Depends(get_db), ): # Spreadsheet objects book = xw.Book(json=data) sheet = book.sheets[0] result_cell = sheet["D1"] # Get the query parameters as dictionary params = sheet["A5:B5"].options(dict, expand="down").value # You can log who is running the query logger.info(f"Running 'select employees' query for user {current_user.email}") # SQL Query using SQLAlchemy placeholders sql = """ SELECT * FROM employees WHERE salaried_flag = :salaried_flag """ if params["job title"]: sql += "AND LOWER(job_title) LIKE LOWER(:job_title)" # Execute the query via SQLAlchemy result = db.execute( text(sql), { "salaried_flag": params["salaried?"], "job_title": f"%{params['job title']}%", }, ) # Delete existing data in the spreadsheet and write the result back result_cell.expand().clear_contents() result_cell.value = process_cursor_result(result) return book.json()
Catatan. sementara repo menggunakan FastAPI, Anda dapat menggunakan xlwings dengan kerangka web Python lainnya, termasuk Django, Flask, dll
Seperti yang dapat Anda lihat dari cuplikan, xlwings memudahkan untuk membaca parameter dari Google Sheets (kotak centang secara otomatis kembali sebagai version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 1 atau version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 2). version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data _3 adalah sesi SQLAlchemy yang kami gunakan untuk mengeksekusi kueri. Di akhir cuplikan, Anda dapat melihat bagaimana xlwings memungkinkan Anda mendorong hasilnya kembali ke sel yang diinginkan di Google Sheets
Untuk memanggil titik akhir ini, gunakan fungsi runPython di bawah Google Apps Script seperti itu
const base_url = "//your-url.com"; const token = ScriptApp.getOAuthToken(); function selectEmployees() { runPython(base_url + "/employees/select", { apiKey: token }); }
runPython_ adalah fungsi dari modul version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 6 Apps Script, yang sudah ada dalam template tutorial ini. Jika Anda akan memulai dari awal dengan Google Sheet baru, Anda dapat menyalin/menempel modul xlwings dengan menjalankan version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 7 pada mesin tempat Anda memasang Python dan xlwings
Meskipun men-deploy aplikasi web ke layanan seperti Render adalah ide bagus untuk produksi, itu akan terlalu rumit untuk tujuan pengembangan. Oleh karena itu, bagian selanjutnya memberi Anda gambaran singkat tentang seperti apa pengembangan itu
Pengaturan pengembangan
Untuk pengembangan yang mudah, Anda dapat menjalankan
- aplikasi dari laptop lokal Anda dan ekspos dengan menggunakan layanan seperti ngrok sehingga Google Sheets dapat mengakses server web Anda yang berjalan di localhost, atau
- gunakan IDE berbasis web seperti GitPod atau GitHub Workspaces. mereka dapat dengan mudah mengekspos port aplikasi web Anda sehingga Google Sheets dapat mengaksesnya (lihat tombol GitPod pada README di GitHub)
Untuk database berbasis server seperti PostgreSQL, Docker membuatnya sangat mudah untuk menjalankan instance lokal untuk tujuan pengembangan. Repo di GitHub berisi file version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 8 di mana Anda hanya perlu menghapus komentar pada layanan version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 9 sebelum menjalankan runPython0 di Command Prompt atau Terminal. ini akan menjalankan aplikasi web dalam satu wadah dan database PostgreSQL dalam wadah lain. Pastikan untuk mengikuti instruksi yang lebih rinci di README. misalnya, itu akan memberi Anda instruksi untuk menyediakan file runPython1 dengan string koneksi yang tepat. Dalam kasus PostgreSQL berjalan melalui docker-compose, ini adalah. runPython_2. Setelah menghapus komentar pada layanan version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data _9, version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data 8 Anda akan terlihat seperti ini (pastikan semuanya terindentasi dengan benar. )
version: '3.7' services: app: build: context: . command: uvicorn app.main:app --host 0.0.0.0 ports: - "8000:8000" volumes: - ./app:/app - ./scripts:/scripts - ./sqlite-data:/sqlite-data env_file: - .env postgres: image: postgres:14-alpine ports: - "5432:5432" environment: POSTGRES_PASSWORD: 'MyPassw0rd' POSTGRES_DB: xlwings volumes: - ./postgres-data/postgres:/var/lib/postgresql/data
Untuk detail lebih lanjut tentang alur kerja pengembangan dengan Google Sheets dan xlwings, lihat dokumen juru bahasa jarak jauh
Tetapi mengapa menggunakan Python untuk terhubung ke database PostgreSQL Anda merupakan ide yang bagus dan bagaimana cara membandingkannya dengan menggunakan add-on Google Sheets yang tersedia?
xlwings vs. Add-on Google Sheets untuk PostgreSQL
Dengan menggunakan backend Python khusus, Anda mendapatkan beberapa keuntungan dibandingkan dengan add-on Google Sheets ("tanpa kode") yang tersedia
- Tidak perlu mempercayai vendor pihak ketiga dengan data sensitif Anda. Anda dapat menjalankan kueri pada infrastruktur yang Anda inginkan (Render hanyalah contoh cara menerapkannya). Anda dapat memilih penyedia dan lokasi—Anda bahkan dapat menjalankannya di server perusahaan fisik atau Raspberry Pi
- Pengaya Google biasanya diberi harga per pengguna, yang bisa menjadi mahal dengan sangat cepat. xlwings PRO hanya membutuhkan lisensi berbayar untuk pengembang dan bebas digunakan untuk pengguna akhir
- Anda dapat menyimpan string koneksi dan data sensitif lainnya dari Google Spreadsheet—mereka dapat diamankan dengan baik sebagai rahasia di sisi server dan tidak dapat diakses oleh pengguna spreadsheet
- Anda dapat menggunakan server yang sangat kuat jika Anda ingin melakukan beberapa CPU atau hal-hal yang intensif memori selain menanyakan PostgreSQL
- Anda mendapatkan akses ke pengguna yang menjalankan kueri dan Anda dapat mencatat setiap kali seseorang mengeksekusi kueri
- Anda dapat dengan mudah membuat beberapa tabel atau seluruh database PostgreSQL hanya-baca untuk pengguna tertentu sambil memberikan akses tulis kepada pengguna lain. Untuk info selengkapnya tentang otorisasi, lihat https. //github. com/xlwings/xlwings-googlesheets-auth
- Anda dapat menggunakan seluruh spreadsheet untuk mendesain antarmuka pengguna Anda sendiri daripada harus mengikuti desain solusi tanpa kode
- Jika Anda memiliki sumber data lain yang perlu Anda tarik dan gabungkan dengan hasil SQL, Anda dapat melakukannya dengan mudah, karena ini hanya kode Python
- Ingin menyegarkan kueri setiap hari atau setiap jam?
- Ingin mengubah sumber data dari PostgreSQL ke database SQL lain seperti MySQL atau SQL Server?
Kesimpulan
xlwings memungkinkan Anda mengotomatiskan Google Sheets dengan menggunakan Python alih-alih Google Apps Script (mis. e. , JavaScript). Karena Python dapat terhubung ke hampir semua sumber data, koneksi ke PostgreSQL hanya berjarak beberapa baris kode. Hal yang sama berlaku untuk database SQL lainnya seperti MySQL, SQL Server, Oracle, atau SQLite—tutorial terpisah akan mengikuti