Apakah Anda ingin meningkatkan level game google sheets Anda dengan otomatisasi?
Hasil pembelajaran
- Untuk mempelajari cara mengautentikasi dengan Google Sheets dalam a. file py atau file notebook jupyter
- Untuk mempelajari cara membaca dari Google Sheet
- Untuk mempelajari cara menulis (mengunggah) ke Lembar Google
- Untuk mempelajari cara memanipulasi data dalam Google Sheets
- Untuk mempelajari cara menyimpan data ke file lokal dari Google Sheet
- Untuk mempelajari cara berbagi Google Sheet dengan pengguna gmail lainnya
Impor Modul
Untuk menjalankan impor modul di bawah ini, Anda harus menginstal klien python yang direkomendasikan Google
Cara cepat untuk melakukannya adalah dengan menjalankan perintah berikut dan me-restart server Jupyter Notebook Anda
pip install --upgrade google-api-python-clientSelain itu, saya akan mendorong Anda untuk mengunduh perangkat pengembangan perangkat lunak (SDK) gcloud, yang akan berguna untuk menerapkan dan mengakses sumber daya Google Cloud Platform Anda
Anda dapat menemukan informasi lebih lanjut tentang cara melakukannya dari
- https. //awan. google. com/python/setup
- https. //awan. google. com/sdk/docs
Unduh Pygsheets
Kami akan menggunakan paket python yang disebut pygsheets untuk dengan mudah berinteraksi dengan lembar google baru yang akan kami buat
Jika Anda menggunakan anaconda, Anda dapat menavigasi ke terminal Anda dan menginstal versi terbaru dari paket ini
conda install pygsheetsAtau jika Anda menggunakan pip atau pip3 untuk mengelola paket Anda, Anda dapat mengetik
pip install pygsheets pip3 install pygsheets !pip install pygsheetsSeperti yang Anda lihat dari atas, saya sebelumnya telah menginstal pygsheets, setelah Anda menginstal paket python jika Anda bekerja dari notebook jupyter, silakan mulai ulang Kernel
Mengautentikasi Ke Proyek Google Cloud Dengan A. Kunci Akun Layanan JSON
Menggunakan lokal yang diperoleh sebelumnya. json Anda dapat mengautentikasi ke akun layanan google Anda
with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)Buat Lembar Google Baru + Dapatkan ID Unik
Pertama kita akan membuat lembar google baru, kemudian kita akan mendapatkan id dari lembar google tertentu yang dapat ditemukan di dalam URL
Google Sheet Wizardry Dengan Perintah Python
Otentikasi Dengan Google Sheets Dengan Pygsheets
Anda berhasil mengautentikasi ke google sheets dengan a. kunci json seperti itu
client = pygsheets.authorize(service_account_file='service_account.json')_Cara Menghubungkan ke lembar Google Tertentu
Sekarang setelah kami mengautentikasi pygsheets dengan proyek cloud google kami, mari sambungkan ke lembar google tertentu
Untuk melakukan ini, kami perlu mengambil tautan URL dan membagikan spreadsheet
spreadsheet_url = "//docs.google.com/spreadsheets/d/1OBR8HEE2rjaWqZPnb8mHnp3YpxxELcy5Zqve3gtX22Q/edit?usp=sharing"_- Selain itu, kami akan mengekstrak ID spreadsheet yang dapat ditemukan di URL di atas antara /d/ dan /edit?
- Id ini akan unik untuk Anda, jadi pastikan untuk ikut kode
Ada tiga cara untuk membuka spreadsheet
- Dengan judul spreadsheet
- Dengan ID spreadsheet unik
- Dengan URL yang tepat dari ghseet
🔥 Mari tunjukkan ketiganya dan Anda dapat memilih 🔥
pip install --upgrade google-api-python-client1pip install --upgrade google-api-python-client2Cara Memilih Lembar Kerja Google Tertentu
Mari pilih lembar kerja yang dibuat secara otomatis bernama Sheet1
Mengakses Baris & Kolom Meta-data
Setelah mengunggah bingkai data ke lembar google, Anda dapat melihat berapa banyak kolom dan baris yang Anda miliki
pip install --upgrade google-api-python-client4pip install --upgrade google-api-python-client5pip install --upgrade google-api-python-client6Cara Mengunggah Data Ke Google Sheet Dari Pandas DataFrame
Saya akan menggunakan data kata kunci yang awalnya saya unduh dari Ahrefs, namun jika Anda memiliki csv lain, tidak apa-apa. Saya mendorong Anda untuk membaca file csv apa pun dan membuat kerangka data panda
pip install --upgrade google-api-python-client_7Untuk mengunggah kerangka data ke lembar kerja ini, kita dapat menggunakan sintaks berikut
pip install --upgrade google-api-python-client8pip install --upgrade google-api-python-client9Perlu diingat bahwa ketika Anda menjalankan perintah di atas, Anda akan menimpa nilai sel apa pun.
Namun jika Anda lebih suka menambahkan baris baru secara bertahap ke bagian bawah lembar kerja, silakan gunakan sintaks di bawah ini
import pandas as pd import json import csv from google.oauth2 import service_account0Kiat Pro. Jika Anda tidak memiliki cukup baris dalam lembar kerja yang ada, mengunggah kerangka data panda mungkin gagal. Untuk memperhitungkan hal ini, Anda dapat memeriksa jumlah baris yang ada di dalam lembar kerja vs jumlah baris dalam kerangka data dan menambahkan baris tambahan jika diperlukan. Kami akan melakukan pemeriksaan yang sama untuk kolom juga
import pandas as pd import json import csv from google.oauth2 import service_account1Cara Mengunduh Bingkai Data Pandas Dari Lembar Kerja Khusus Google Sheets
Kami juga bisa mendapatkan data langsung dari lembar kerja google sheets mana pun dalam bentuk kerangka data panda
import pandas as pd import json import csv from google.oauth2 import service_account2import pandas as pd import json import csv from google.oauth2 import service_account3import pandas as pd import json import csv from google.oauth2 import service_account4import pandas as pd import json import csv from google.oauth2 import service_account5Cara Menghapus Lembar Google Dengan Python
Anda dapat menghapus semua baris dan kolom yang ada di dalam sheet dengan mereferensikan variabel ruang kerja yang sama (wks) dan file. hapus() sintaks
conda install pygsheets _9Sekarang saya akan merekomendasikan menghapus nans apa pun sebelum mengunggah kerangka data Anda. Jadi mari kita hapus semua np. nans dalam Kolom Volume
pip install pygsheets pip3 install pygsheets _0Mari coba mengurutkan lagi berdasarkan kolom Volume dengan pygsheets
pip install pygsheets pip3 install pygsheets 1Cara Mengulangi Setiap Baris
Anda dapat mengulangi baris di lembar google Anda dengan loop for
pip install pygsheets pip3 install pygsheets 2Meskipun ini memang telah menggantikan nilai “NaN”, operasi telah terjadi di semua kolom dan baris dalam lembar kerja. Ini mungkin bukan yang Anda cari.
Misalnya, mungkin Anda hanya ingin mengganti nilai sel dalam 2 kolom?
Memperkenalkan Temukan dan Perbarui Nilai
pip install pygsheets pip3 install pygsheets _5Kami akan melakukan operasi berikut
- Temukan semua sel antara kolom 3 dan 6 (eksklusif sehingga hanya dua kolom yang akan dipilih)
- Kemudian cocokkan dengan sel NaN (bukan angka).
Cara Memperbarui Berbagai Nilai
Jika Anda ingin memperbarui rentang nilai, gunakan sintaks berikut, untuk contoh ini kami akan menetapkan nilai nan yang dipilih ke "Lainnya"
pip install pygsheets pip3 install pygsheets 9!pip install pygsheets0Seperti yang Anda lihat, kami hanya memperbarui nilai kolom BPK dan Klik “NaN” dengan “Lainnya” dan nilai kolom lainnya tetap sama.
Dalam contoh di atas, kami secara khusus mereferensikan daftar_sel, namun Anda juga dapat menggunakan dua metode di bawah ini
!pip install pygsheets1Manipulasi Data Kolom
Cara Menambahkan Banyak Kolom
Untuk menambahkan kolom ke lembar kerja Anda yang sudah ada, Anda dapat menggunakan sintaks ini
Cara Mendapatkan Nama Kolom Dengan Posisi Indeks Kolom
Pertama kita akan mengekstrak semua header pada baris 1, kemudian kita akan membungkus variabel ini dengan menghitung dan mengubahnya menjadi kamus python
Ini akan memungkinkan kami membuat tabel pencarian untuk posisi indeks
Perhatikan di bawah bagaimana kami juga telah berubah
!pip install pygsheets_3Alasan mengapa kami hanya ingin memastikan bahwa jika kami memiliki nilai kolom kosong di antara tabel, kami juga akan mengambil nilai string kosong di antara kolom
!pip install pygsheets4!pip install pygsheets5!pip install pygsheets6Sekarang mari kita hapus salah satu header yang disebutkan yang memiliki string kosong dengan pemahaman daftar
!pip install pygsheets7!pip install pygsheets8!pip install pygsheets9Kami kemudian dapat mengubah daftar tupel ini menjadi kamus python untuk membuat tabel pencarian untuk kolom versus posisi indeksnya
import pygsheets0import pygsheets1import pygsheets2Cara Menghapus Kolom
Anda harus melewati dua parameter saat menghapus kolom melalui pygsheets
import pygsheets3import pygsheets4import pygsheets5Seperti yang kita lihat posisi indeks tertinggi yang kita miliki sebagai kolom sebenarnya dari baris 1 adalah 10. Oleh karena itu mari kita hapus semua kolom dari posisi indeks 12 dan lebih tinggi
Kiat pro. Sekarang alasan mengapa 12 dan bukan 11 adalah karena pygsheets memulai pemosisian indeksnya untuk kolom dan baris pada 1, sementara python memulai pengindeksannya pada posisi 0. Juga pygsheets sudah termasuk dengan indeks awal (1 berarti dari 1 dan seterusnya, bukan kolom 2 dan seterusnya)
import pygsheets_6Anda sekarang dapat dengan mudah menghapus banyak kolom dengan membuat indeks kolom Anda sendiri dengan menghitung
Cara Menyisipkan Kolom
Sintaks berikut memungkinkan Anda untuk menyisipkan banyak kolom
import pygsheets_7Cara Memperbarui Satu Kolom
Untuk memperbarui satu kolom, gunakan sintaks ini
import pygsheets_8Kita dapat menggunakan tabel pencarian sebelumnya yang kita buat untuk mencari kolom tertentu
import pygsheets_9Tips pro. Jika Anda perlu membuat daftar ukuran tertentu di python gunakan kode berikut.
with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)0Cara Menyesuaikan Lebar Kolom
Apakah Anda perlu menyesuaikan kolom tertentu dengan lebar tertentu?
with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)1Juga perlu diingat bahwa setiap kali Anda menggunakan wks. clear(), semua pemformatan akan dihapus dari lembar kerja
Kami hanya dapat menyediakan pygsheets dengan indeks awal dan indeks akhir untuk kolom dengan ukuran piksel tertentu seperti itu
with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)2Manipulasi Data Baris
Cara Memilih Baris Tunggal
Untuk memilih satu baris Anda bisa menggunakan
Cara Menyisipkan Beberapa Baris
Memilih banyak baris sangat mudah dan bisa dilakukan seperti itu
with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)6Mari cari baris terakhir untuk menambahkan baris, dan kita dapat menyisipkan baris baru di posisi indeks 3
with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)7with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)8with open('service_account.json') as source: info = json.load(source) credentials = service_account.Credentials.from_service_account_info(info)9Tips Pro. Saat Anda menyisipkan baris baru, baris tersebut harus berada di bawah nomor indeks baris terakhir. Jika Anda perlu membuat lebih banyak baris, sebaiknya gunakan metode ini.
client = pygsheets.authorize(service_account_file='service_account.json')_0Cara Menghapus Baris
Untuk menghapus baris, gunakan sintaks ini
client = pygsheets.authorize(service_account_file='service_account.json')1client = pygsheets.authorize(service_account_file='service_account.json')2Memformat Google Sheets Dengan Python
Baris + Kolom Beku
Anda dapat dengan mudah mengatur jumlah Baris + Kolom Beku dengan dua perintah
client = pygsheets.authorize(service_account_file='service_account.json')3Cara Menebalkan Sel
Mari tebalkan semua sel di baris pertama
client = pygsheets.authorize(service_account_file='service_account.json')4client = pygsheets.authorize(service_account_file='service_account.json')5Gambar pertama di bawah menunjukkan SEBELUM, dan gambar kedua menunjukkan SETELAH setelah menerapkan DataRange berdasarkan sel model
Sebelum
Setelah
Mengelompokkan Panggilan API Anda
Jika Anda akan membuat banyak permintaan ke Google Sheets API, disarankan untuk mengelompokkan permintaan API Anda. Ini mengurangi kemungkinan permintaan API Anda tidak berhasil karena batas tarif. Cara pygsheets mengimplementasikan kumpulan API adalah sebagai berikut
client = pygsheets.authorize(service_account_file='service_account.json')_6Cara Membagi Lembar Google Dengan Alamat Email Dengan Python
Anda dapat dengan mudah membagikan spreadsheet yang terhubung dengan
client = pygsheets.authorize(service_account_file='service_account.json')_7Selain itu, Anda dapat menyertakan pesan email ke pengguna Anda
client = pygsheets.authorize(service_account_file='service_account.json')_8Cara Mengekspor Lembar Google Ke A. CSV
Juga dimungkinkan untuk dengan mudah mengekspor lembar google Anda ke a. file csv dengan
client = pygsheets.authorize(service_account_file='service_account.json')_9Namun kami harus mengaktifkan Google Drive API agar ini berfungsi, jadi segera buka Proyek Google Cloud Anda dan aktifkan API ini agar perintah berikut berfungsi
Cara Mengonversi Spreadsheet Google Menjadi JSON
Terkadang saat berkomunikasi dengan developer lain, mereka mungkin ingin menerima data Anda dalam format JSON
Anda dapat dengan mudah mengubah data google sheets Anda menjadi JSON API dan memasukkannya langsung ke REST API lain dengan merantai kedua perintah ini
spreadsheet_url = "//docs.google.com/spreadsheets/d/1OBR8HEE2rjaWqZPnb8mHnp3YpxxELcy5Zqve3gtX22Q/edit?usp=sharing"_1Ini akan melakukan dua hal
- Pertama kita akan mendapatkan seluruh lembar kerja sebagai kerangka data panda
- Kedua, kami akan mengonversi kerangka data panda menjadi objek JSON
Cara Menambahkan Kotak Validasi Data Dengan batchUpdate
Menambahkan kotak validasi data relatif mudah, kode di bawah ini akan menambahkan beberapa kotak validasi data pada kolom kedua