Dapatkah Anda mengotomatiskan lembar google?

Dimungkinkan untuk mengotomatiskan Google Sheets melalui Google Sheets API selama bertahun-tahun dan ada beberapa paket Python yang tersedia yang membungkus API ini. ada gspread, pygsheets, ezsheets, dan Google-api-python-client milik Google. Semua paket ini berbicara dari Python ke Google Sheets dan mengharuskan Anda membuat proyek Google Cloud Platform, mengunduh file kredensial, dan berpotensi membagikan spreadsheet Anda dengan pengguna dari file kredensial. Ini bisa menjadi proses yang relatif menyakitkan, terutama jika Anda baru menggunakan Google Cloud Console. Namun demikian, pendekatan Sheets API adalah solusi yang tepat untuk banyak kasus penggunaan. misalnya, membuang data ke Google Sheets untuk tujuan pelaporan atau menggunakan Google Sheets sebagai database di program Python Anda

Namun, saya kehilangan pengalaman Google Apps Script di Python, i. e. , saya ingin mengembangkan aplikasi spreadsheet interaktif langsung di Google Sheets. Jadi saya melanjutkan dan menambahkan dukungan Google Sheets ke xlwings. alih-alih menggunakan Sheets API untuk berbicara dari Python ke Google Sheets, xlwings sebaliknya dan berbicara dari Google Sheets ke Python. Semuanya benar-benar hanya sebuah aplikasi web dengan frontend Google Sheets (ya, itu bekerja dengan kerangka web favorit Anda. ). Selain menghemat pekerjaan frontend Anda, pendekatan ini hadir dengan beberapa fitur bagus, seperti

• Kemudahan penggunaan. tidak memerlukan proyek Google Cloud Platform, file kredensial, atau berbagi dengan pengguna tertentu
• Otorisasi. memanfaatkan identitas Google dan membatasi fungsi tertentu untuk pengguna atau grup tertentu
• Penjadwal. jalankan skrip Python Anda secara otomatis (mis. g. setiap jam) melalui Google Sheets Triggers
• Keamanan. tidak perlu menyimpan kredensial, kode sumber kepemilikan, atau data sensitif lainnya di Google Spreadsheet
• API. Anda tidak dibatasi oleh Sheets API, tetapi dapat menggunakan semua yang ditawarkan oleh Google Apps Script API

Sebelum memandu Anda melalui poin-poin ini dengan sedikit lebih detail, izinkan saya menegaskan kembali bahwa xlwings bukanlah pengganti pendekatan Sheets API (sebenarnya, saya juga berencana untuk membungkus Sheets API dengan xlwings). Sebaliknya, ini adalah alternatif yang memungkinkan Anda membuat jenis aplikasi spreadsheet baru yang sebelumnya hanya dapat dilakukan dengan menulis kode JavaScript. Anda juga bisa melihatnya sebagai pesaing alat low-code untuk membangun aplikasi internal (Retool, Internal, dll. ). Sebagai keuntungan sampingan, kode xlwings juga akan berfungsi dengan Microsoft Excel di Windows, macOS, dan web

Perhatikan bahwa dukungan Google Sheets adalah bagian dari xlwings PRO. gratis untuk penggunaan non-komersial tetapi memerlukan paket berbayar untuk penggunaan komersial. Dan akhirnya. posting ini tetap tingkat tinggi, jadi jika Anda ingin detail lebih lanjut, lihat contoh repo GitHub yang akan saya tautkan di posting ini, lihat dokumen xlwings yang sesuai, atau periksa webinar terbaru saya. Omong-omong, tangkapan layar dasbor masalah GitHub diambil dari repo ini

Daftar isi

Bagaimana cara kerja Sheets API tradisional?

Karena semua paket Python yang tersedia saat ini seperti gspread build di Sheets API, semua prasyaratnya terlihat sama

• Di Google Cloud Console
  • Buat proyek Google Cloud Platform
  • Aktifkan API yang benar
  • Buat kredensial yang tepat dan unduh sebagai file JSON
• Anda mungkin perlu membagikan spreadsheet Anda dengan pengguna dari kredensial yang dibuat
• Jalankan kode Python Anda dari luar Google Sheets, mis. g. , dengan menjalankannya dari baris perintah di laptop atau server Anda

Ada banyak kasus penggunaan di mana pendekatan ini berfungsi dengan baik. menjalankan tugas cron di server untuk memasukkan data secara teratur ke dalam sheet Anda adalah salah satu yang populer. Namun, ada beberapa batasan dengan Sheets API

• Anda hanya dapat mengotomatiskan apa yang diekspos oleh Sheets API. misalnya, menyisipkan gambar di atas sel tidak didukung
• Meskipun pekerjaan cron berfungsi dengan baik, Anda tidak dapat membuat pengguna bisnis menjalankan kode Anda secara ad-hoc tanpa membuat semacam aplikasi untuk mereka
• Tidak semua orang di organisasi Google Workspace Anda berhak menyiapkan Google Cloud Project
• Anda perlu memastikan bahwa file kredensial diamankan dengan benar

Di bagian selanjutnya, kita akan melihat cara kerja xlwings dan cara mengatasi masalah ini

Quickstart xlwings untuk Google Sheets

xlwings untuk Google Sheets sebenarnya hanyalah aplikasi web yang menggunakan Google Sheets sebagai frontend, sehingga Anda tidak perlu berurusan dengan HTML, CSS, dan JavaScript. Anda tidak perlu menyiapkan Proyek Google Cloud, dan bermuara hanya pada dua langkah

1. Salin/tempel modul JavaScript xlwings ke editor Apps Script
2. Jalankan server web Python dengan kode xlwings Anda

Satu-satunya peringatan adalah Google Sheets harus dapat menjangkau aplikasi web Anda

• Untuk pengembangan, Anda dapat menggunakan IDE berbasis cloud seperti GitPod atau GitHub Codespaces atau—jika Anda lebih suka bekerja di laptop lokal—ekspos port server web Anda dengan alat seperti ngrok
• Untuk produksi, tidak ada hal khusus yang perlu dipertimbangkan selama server/cloud Anda dapat dijangkau dari Google Sheets

Untuk bangun dan berjalan dalam waktu 2 menit, buka proyek quickstart di GitHub dan tekan tombol Gitpod di README. Kemudian, di Google Sheet Anda, buka Extensions > Apps Script dan tempel kode yang Anda temukan di bawah js/xlwings_google.js. Itu dia

Tekan tombol Save di editor Apps Script sebelum mengklik tombol Run untuk memanggil fungsi hello. ini akan menulis Hello xlwings!_ ke sel A1 spreadsheet Anda, dan mengekliknya lagi akan mengubahnya menjadi

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

Bagaimana cara kerjanya?

function hello() {
    runPython("https://your-url/hello", { apiKey: "your-api-key" });
}

Panggilan

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

Catatan. Saya menggunakan FastAPI di semua repo sampel, tetapi Anda dapat menggunakan kerangka web lain seperti Django atau Flask

Jika Anda pernah menggunakan xlwings dengan Excel sebelumnya, Anda akan merasa seperti di rumah sendiri, karena Anda dapat menggunakan sintaks yang sama. Anda dapat melihat kode lengkap dari sampel quickstart minimal baik dengan melihat repo di GitHub atau dengan menjalankan perintah berikut secara lokal (memerlukan xlwings untuk diinstal).

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

Jika ini terlalu cepat, tonton video ini di mana saya akan memandu Anda melalui setiap langkah

Sekarang kita telah bermain-main dengan proyek quickstart, mari kita lihat beberapa fitur yang ditawarkan xlwings sehubungan dengan Google Sheets

Otentikasi dan otorisasi

Otentikasi adalah salah satu bagian terpenting dari setiap aplikasi dan biasanya sulit dilakukan dengan benar. Google Sheets membuat bagian ini sangat sederhana. ganti kunci API dari contoh quickstart dengan

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

• Hapus kunci API sensitif dari modul Apps Script
• Autentikasi semua orang dari domain Workspace Anda secara otomatis
• Otorisasi pengguna untuk tindakan tertentu. misalnya, Anda dapat membatasi akses tulis ke database Anda untuk beberapa pengguna, sambil memberikan akses baca kepada semua orang

Untuk mendapatkan ide yang lebih baik, lihat contoh repo berikut

https. //github. com/xlwings/xlwings-googlesheets-fastapi-auth

Ini menunjukkan Anda

• Cara menyiapkan autentikasi
• Cara mendapatkan objek pengguna
• Cara melakukan otorisasi (menggunakan pendekatan berbasis cakupan FastAPI)

Seberapa tepatnya Anda mengotorisasi pengguna terserah Anda. Anda dapat menggunakan layanan identitas seperti Active Directory atau Okta atau mendefinisikan grup sebagai variabel lingkungan. Contoh repo menunjukkan kepada Anda cara memverifikasi keanggotaan grup secara langsung terhadap Google Workspace Anda—namun, ini mengharuskan Anda untuk menyiapkan Google Cloud Project, lihat README repo untuk semua detailnya

Terkait dengan autentikasi/otorisasi adalah keamanan secara umum, yang akan kita lihat selanjutnya

Keamanan

Berbagi Google Sheet semudah mengetikkan alamat email, tetapi itu juga berarti Anda dapat membagikannya secara tidak sengaja dengan orang yang salah. Untuk meminimalkan kerusakan, xlwings mempermudah penyimpanan data sensitif dari spreadsheet Anda

• Kredensial untuk database, API, dll
• Data sensitif lainnya seperti data klien
• Logika bisnis (kode sumber Python Anda)

Modul Apps Script hanya akan berisi informasi non-sensitif, selama Anda menggunakan

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

Yang penting, kredensial dapat diperlakukan dengan benar sebagai rahasia di backend Anda, menggunakan cara apa pun yang ditawarkan penyedia Anda. Anda juga dapat mengontrol siapa yang harus memiliki akses ke kode Python (tentu saja melalui kontrol versi Git)

Sekarang setelah aplikasi Anda aman, mari kita lihat berbagai cara menjalankan kode Anda

Jalankan kode Anda melalui tombol, menu khusus, dan pemicu

xlwings dapat memanfaatkan kemungkinan yang sama dengan yang Anda miliki saat menjalankan kode Google Apps Script

• Tombol. di Google Sheets, Anda dapat mengklik

  @app.post("/hello")
  def hello(data: dict = Body):
      # Instantiate a Book object with the deserialized request body
      book = xw.Book(json=data)
  
      # Use xlwings as usual
      sheet = book.sheets[0]
      if sheet["A1"].value == "Hello xlwings!":
          sheet["A1"].value = "Bye xlwings!"
      else:
          sheet["A1"].value = "Hello xlwings!"
  
      # Pass the following back as the response
      return book.json()
  

  _9 >

  ax = df.plot(figsize=(12, 8))
  mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])
  

  0, menggambar sesuatu yang terlihat seperti tombol (e. g. , persegi panjang bulat), dan tekan

  ax = df.plot(figsize=(12, 8))
  mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])
  

  1. Kemudian, klik kanan pada tombol sehingga tiga titik muncul, klik di atasnya dan pilih

  ax = df.plot(figsize=(12, 8))
  mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])
  

  2, lalu tulis nama fungsinya, untuk contoh quickstart, ini akan menjadi hello
• Menu Kustom. dengan beberapa baris kode JavaScript, Anda dapat menjalankan fungsi Anda dari menu, lihat dokumen resmi
• Pemicu. di editor Apps Script, klik ikon jam alarm di sidebar sebelah kiri. Kemudian, di kanan bawah, klik tombol biru

  ax = df.plot(figsize=(12, 8))
  mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])
  

  4. ini akan memungkinkan Anda untuk menjalankan fungsi Anda secara berkala atau berdasarkan tindakan spreadsheet seperti acara terbuka

Jika Anda menyukai komputasi ilmiah atau pembelajaran mesin, kemungkinan besar tombol dan menu Anda akan menjalankan beberapa kode panda. Mari kita lihat lebih dekat bagaimana Anda bekerja dengan tumpukan Python ilmiah di bagian selanjutnya

DataFrames dan plot

Array NumPy, panda DataFrames, dan Matplotlib berada di jantung komputasi ilmiah dengan Python. Dengan demikian, xlwings mendukung semuanya di luar kotak. Plotly juga didukung, dan setiap pustaka plot lainnya juga akan berfungsi, selama plot dapat diekspor ke gambar. Tangkapan layar di awal bagian ini diambil dari https. //github. com/xlwings/xlwings-googlesheets-pandas-plots dan seperti yang akan Anda lihat di sana, menulis DataFrame

ax = df.plot(figsize=(12, 8))
mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])

Menggunakan plot Matplotlib—seperti yang dihasilkan oleh panda—terlihat seperti ini

ax = df.plot(figsize=(12, 8))
mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])

Untuk detail lebih lanjut, lihat repo

Di bagian selanjutnya, kita akan melihat bagaimana Anda dapat menerapkan contoh aplikasi ini ke dalam produksi

Penyebaran produksi

Masalah nomor satu dengan xlwings selalu menjadi penerapan Python — itulah alasan saya sangat menyukai bagian ini. lempar gambar Docker atau Dockerfile ke layanan favorit Anda untuk menjalankan kontainer Docker dan selesai. Ada banyak sekali layanan luar biasa di cloud atau di bawah meja Anda, dan Anda dapat memilih salah satu yang sesuai dengan kebutuhan Anda. Untuk informasi lebih lanjut dan beberapa saran terkait dengan layanan, lihat

Sebagian besar repo sampel yang disebutkan dalam posting ini menyertakan Dockerfile yang membuatnya mudah untuk menerapkannya langsung dari GitHub. Opsi yang lebih mudah adalah dengan mengeklik tombol

ax = df.plot(figsize=(12, 8))
mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])

Mari akhiri postingan ini dengan ulasan singkat tentang roadmap

Peta jalan

Ini masih tahap awal untuk dukungan Google Sheets di xlwings, namun, dengan dataFrames panda dan plot Python saja, Anda akan dapat membangun hal-hal menakjubkan. Langkah selanjutnya adalah

• Tambahkan dukungan untuk fungsi kustom (a. k. a. fungsi yang ditentukan pengguna)
• Tambahkan dukungan untuk API xlwings lengkap, termasuk bagan, rentang bernama, dll
• Menjadikan segalanya lebih efisien

Urutan persisnya akan ditentukan oleh Anda, pengguna, tentu saja

Kesimpulan

Dukungan Google Sheets baru di xlwings memungkinkan Anda membuat alat dengan Python yang sebelumnya hanya bisa Anda buat di Google Apps Script, i. e. , JavaScript. xlwings memungkinkan Anda untuk memanfaatkan autentikasi bawaan Google, Anda dapat membuat alat spreadsheet aman dengan menyimpan data sensitif dari spreadsheet Anda, dan Anda memiliki kebebasan total sehubungan dengan kerangka web dan infrastruktur untuk backend Anda

Apakah ada cara untuk mengotomatiskan Google Sheets?

Bisakah Google Sheet menarik data dari Excel secara otomatis?

Bagaimana cara mengisi data secara otomatis di Google Sheets berdasarkan sel lain?

Bagaimana cara mengotomatiskan Google Sheets di Excel?