Impor google sheet ke postgres

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

1. Buka proyek di GitHub. xlwings-googlesheets-sql
2. 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

3. 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 = "https://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 = "https://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 bawah

   Setelah menekan

   const base_url = "https://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)

4. Buat salinan template Google Sheet dengan mengklik

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   3 di kanan atas
5. Di Google Sheets, buka editor Apps Script dengan membuka

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   4 >

   const base_url = "https://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)

6. Di Google Sheets, klik tombol

   const base_url = "https://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 = "https://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 = "https://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 = "https://your-url.com";
const token = ScriptApp.getOAuthToken();

function selectEmployees() {
  runPython(base_url + "/employees/select", { apiKey: token });
}

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

@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

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

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 memanggil titik akhir ini, gunakan fungsi runPython di bawah Google Apps Script seperti itu

const base_url = "https://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

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

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

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

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

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

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

Bagaimana cara mengekspor lembar Google ke PostgreSQL?

Bagaimana cara mengimpor data ke PostgreSQL?

Bisakah saya mengimpor Excel ke PostgreSQL?

Bagaimana cara mengonversi Google Sheets ke JSON?