Automasi Helaian Google Menggunakan Python | Analytics Vidhya

Helaian Google adalah salah satu alternatif yang paling popular dan digunakan secara meluas untuk cemerlang, menawarkan persekitaran kerjasama dengan ciri-ciri seperti penyuntingan masa nyata, kawalan versi, dan integrasi lancar dengan Google Suite, yang membolehkan pengguna memanggil Helaian Google di Google Docs dan memanfaatkan yang terbaik dari Google Workspace. Walaupun anda boleh dengan mudah memuatkan dan bekerja dengan fail Excel secara programatik menggunakan PANDAS, perpustakaan sains data terkemuka, persediaan yang sama boleh direplikasi dengan Helaian Google. Sebagai tawaran SaaS, mengakses data Google Sheets memerlukan menggunakan APInya, yang menyokong pelbagai bahasa pengaturcaraan, termasuk Java, JavaScript, Node.js, PHP, Ruby, Python , dan Google's Own AppScript. Artikel ini memberi tumpuan kepada menggunakan Python untuk memuatkan data Helaian Google secara efisien ke dalam bingkai data Pandas , membolehkan transformasi dan analisis data cepat. Sebaik sahaja perubahan selesai, Perpustakaan Python Gspread boleh digunakan untuk menolak data yang dikemas kini kembali ke Helaian Google, menyediakan antara muka yang mudah untuk berinteraksi dengan API Google Sheets.

Artikel ini diterbitkan sebagai sebahagian daripada Blogathon Sains Data.

Jadual Kandungan

• Menetapkan Projek Awan Google anda
• Memahami Gspread
• Mewujudkan sambungan dan pembukaan spreadsheet
• Membuat dan Menguruskan Helaian Google
• Menguruskan lembaran kerja
• Sifat sel
• Menambah baris dan lajur baru
• Mengambil sel dan nilai julat
• Mengemas kini sel dan julat
• Memadam baris dan lajur
• Mencari sel
• Sel pemformatan
• Julat sel dan lembaran kerja yang jelas
• Batasan API Google
• Soalan yang sering ditanya

Menetapkan Projek Awan Google anda

Seperti yang dinyatakan sebelum ini, Google Sheets adalah tawaran SaaS, jadi anda perlu menyediakan langkah tambahan untuk automasi. Platform Cloud Google (GCP), platform pengkomputeran awan yang popular, menawarkan pelbagai perkhidmatan yang membantu berinteraksi dengan produk Google bersama -sama dengan penggunaan projek tersuai anda.

Secara umum, kita perlu mengikuti 3 langkah ini untuk memulakan dengan Automasi Google Sheets.

Membuat dan Mengkonfigurasi Projek Awan Google

Kepala ke https://console.cloud.google.com/ dan daftar untuk akaun percuma. Seterusnya dari kiri atas, klik pada menu pemilihan projek dan pilih projek baru. Sediakan nama projek, meninggalkan organisasi sebagai "tiada organisasi", Hit Create dan projek GCP anda ditetapkan sekarang.

Menjana dan menjamin kelayakan API

Kami perlu membolehkan API Google Helaian dan Google Drive dan membuat akaun perkhidmatan. Jenis akaun khas ini membolehkan kami mengakses dan menguruskan sumber Google Cloud tanpa memerlukan interaksi manusia. Untuk membolehkan API Google Sheets, cari helaian di bar carian teratas dan pilih "Google Sheets API". Klik Benarkan dan ia akan mengarahkan kami ke halaman Butiran API. Di sini klik pada "Buat Kelayakan" dan ia akan membuka borang penciptaan kelayakan.

Pilih "Data Aplikasi" dari kotak pemilihan dan klik Seterusnya. Pada skrin seterusnya, berikan nama yang bermakna untuk akaun Perkhidmatan, kerana anda akan menggunakannya dalam langkah -langkah kemudian. Kemudian, pilih peranan "Editor". Akhirnya, klik dilakukan di bahagian bawah.

Begitu juga, API Google Drive boleh diaktifkan. Sila ambil perhatian bahawa kami tidak perlu membuat akaun perkhidmatan lain untuk API ini. Akaun perkhidmatan yang sedia ada akan dapat mengakses kedua -dua API. Sekarang, kita perlu memuat turun kelayakan JSON, yang skrip Python kami akan dimakan untuk mengakses Helaian Google. Klik pada e -mel akaun perkhidmatan yang baru dijana, beralih ke tab Kekunci, klik pada butang Tambah Kekunci untuk memilih pilihan Buat Kunci Baru, pilih JSON, dan kemudian buat.

Memberi akses ke Helaian Google

Skrip Python kami akan menggunakan kelayakan yang dihasilkan untuk mengakses Helaian Google. Walau bagaimanapun, kita perlu memberikan akses kepada fail yang akan digunakan secara manual. Untuk melakukan ini, salin e -mel yang dijana untuk akaun Perkhidmatan (terdapat dalam tab Butiran Akaun Perkhidmatan) dan tambahkan e -mel tersebut sebagai editor ke fail yang dikehendaki.

Memahami Gspread

GSPREAD adalah pembungkus API Python untuk Helaian Google. Ia merangkumi banyak fungsi yang ditawarkan oleh Google SheetsAPI di bawah kelas berasingan dan kaedah akses. Ia menjadikan interaksi dengan SheetsAPI mudah menavigasi dan seseorang dapat dengan cepat mengambilnya.

Untuk menubuhkan perpustakaan di persekitaran tempatan, seseorang boleh menggunakan arahan PIP yang mudah, seperti mana -mana pakej Python yang lain. Pasang perpustakaan dalam persekitaran yang berasingan sebagai amalan terbaik untuk mengelakkan sebarang konflik pergantungan.

 PIP Pasang GSpread

Nota ringkas mengenai rujukan sel

Alamat sel di Helaian Google boleh dirujuk menggunakan dua notasi popular:

• Notasi A1: Rujukan sel ini terdiri daripada nama lembaran, nombor baris, dan huruf lajur. Rujukan ini berfungsi tanpa menyebut nama lembaran dan membolehkan anda merujuk kepada sel tunggal, pelbagai sel, atau seluruh lajur.
• Rentang Dinamakan: Ini adalah pelbagai sel yang mempunyai nama tersuai untuk pengenalpastian mudah dan rujukan mudah di seluruh lembaran Google.

Mewujudkan sambungan dan pembukaan spreadsheet

Sekarang bahawa kami telah menyediakan akses dan perpustakaan yang diperlukan, mari kita menguji kod kami. Dalam spreadsheet yang mengandungi e -mel akaun perkhidmatan, masukkan beberapa teks rawak di sel pertama. Kami akan cuba mendapatkan nilai ini menggunakan skrip Python kami.

Kami akan menggunakan modul JSON untuk memuatkan kelayakan kami dan menyampaikannya kepada fungsi "Service_Account_From_Dict () GSPREAD. Ini akan mengembalikan objek klien Google Sheets dan objek ini boleh digunakan untuk membuka mana -mana lembaran Google menggunakan fungsi "Terbuka ()". Lihat kod di bawah.

 Import GSpread
Import JSON

dengan terbuka ('creds.json') sebagai f:
    kelayakan = json.load (f)

gc = gspread.service_account_from_dict (kelayakan)

sh = gc.Open ("Articledemo")

Terdapat dua cara alternatif untuk membuka lembaran Google dan bukannya nama tajuk. Cara alternatif ini menghapuskan ketergantungan nama tajuk seperti dalam ruang kerja Google, pelbagai fail boleh mempunyai tajuk yang sama. Dalam kes spreadsheet, jika terdapat dua fail dengan tajuk yang sama, maka fail terkini akan diakses oleh API. Kami boleh mengakses spreadsheet menggunakan URL fail atau ID unik spreadsheet yang meneruskan pautan berikut: "https://docs.google.com/spreadsheets/d//Edit". Berikut adalah kod untuk mengakses spreadsheet melalui URL atau ID unik.

 ## akses melalui ID unik
sh = gc.open_by_key ("1R97TWCM0FFFNSSRH_0FJDDG-HCQF5PLHBHRXU9PTV_Q")


## akses melalui URL
sh = gc.open_by_url ("https://docs.google.com/spreadsheets/d/1r97twcm0fffnssrh_0fjddg-hcqf5plhbhrxu9ptv_q/edit?gid=0#gid=0")

Kod berikut akan membaca nilai yang dimasukkan sebelum ini dalam helaian. Kod yang berfungsi akan dijelaskan di bahagian kemudian artikel.

 cetak (sh.sheet1.acell ('a1'). Nilai)

Ini akan mengembalikan nilai yang terdapat dalam sel A1 dalam lembaran, yang dalam kes kami adalah "GSHEET: Ini adalah sel pertama". Sekarang kita semua bersedia untuk menyelam ke dalam Perpustakaan Gspread dan meneroka semua pilihan yang ada.

Nota: shvariable memegang objek spreadsheet dan ia akan dirujuk ke seluruh panduan

Membuat dan Menguruskan Helaian Google

Terdapat banyak kes penggunaan di mana spreadsheet dicipta secara programatik. Orang boleh membina penyelesaian untuk menerbitkan data dan pandangan untuk pengguna mereka. Seiring dengan ini, mereka mungkin mahu berkongsi helaian ini secara langsung dengan pengguna.

• Untuk membuat spreadsheet baru, gunakan fungsi create () klien gspread. Lulus tajuk spreadsheet baru sebagai parameter, dan jika anda ingin menentukan lokasi, gunakan parameter folder_id.
• Spreadsheet baru yang dibuat hanya boleh diakses oleh pengguna akaun perkhidmatan. Ini bermakna spreadsheet tidak akan dapat dilihat walaupun kepada pengguna yang membuat akaun perkhidmatan. Untuk tujuan ini, kita boleh menggunakan fungsi "SHARE ()" objek spreadsheet. Fungsi ini memerlukan 3 parameter wajib: "e -mel_address" (alamat e -mel), "perm_type" (jenis kebenaran), dan "peranan". Jenis kebenaran boleh mengambil nilai berikut: Pengguna, Kumpulan, Domain, atau sesiapa sahaja. Bagi kebanyakan kes penggunaan, nilai pengguna akan berfungsi. Perm_type juga mempunyai nombor tetap yang boleh diterima: 'pembaca', 'commenter', 'penulis', 'fileorganizer', 'penganjur', dan 'pemilik'. Terdapat beberapa parameter pilihan tambahan, menawarkan tahap maklumat berbutir.
  • "Beritahu": Nilai Boolean untuk mengawal jika pengguna harus mendapatkan pemberitahuan fail yang dikongsi.
  • "Email_message": Nilai rentetan untuk mesej dihantar bersama dengan e -mel pemberitahuan.

 sh = gc.create ('articledemotest') ## Membuat spreadsheet baru
sh.share (email_address = '[dilindungi e -mel]', perm_type = 'user', role = 'writer', notify = true, e -mel_message = "Ini adalah fail ujian")

Menguruskan lembaran kerja

Setiap spreadsheet adalah koleksi lembaran kerja. Analogi mudah untuk ini adalah bagaimana buku mempunyai banyak halaman. Menggunakan GSPREAD, pengguna boleh mengakses, mengubah suai, memadam, atau membuat lembaran kerja baru. Mari kita lihat setiap fungsi ini.

Pemilihan lembaran kerja

Lembaran kerja objek spreadsheet boleh diakses menggunakan kaedah berikut objek lembaran kerja:

• Akses oleh Indeks: Fungsi "get_worksheet ()" mengambil dalam indeks lembaran kerja yang perlu diakses.
• Akses mengikut Tajuk: Fungsi "Lembaran Kerja ()" mengambil tajuk lembaran kerja. Perhatikan bahawa tajuk lembaran kerja adalah unik secara semula jadi dan oleh itu, tidak ada dua lembaran kerja yang boleh mempunyai tajuk yang sama.
• Akses oleh Pintasan Notasi Dot: Pintasan notasi DOT membolehkan mengakses lembaran kerja pertama spreadsheet tanpa memberikan tajuk, indeks, atau ID.
• Akses Semua: Fungsi "Lembaran Kerja ()" mengembalikan semua lembaran kerja spreadsheet. Ia mengembalikannya sebagai objek lembaran kerja GSPREAD. "Tajuk" dan "ID" adalah beberapa sifat penting dalam kelas ini yang membantu mengakses lembaran kerja yang dikehendaki secara pukal.
• Akses oleh ID: Semasa membangunkan skrip automasi, mungkin terdapat beberapa kes penggunaan di mana kita berurusan dengan ID lembaran kerja dan bukannya tajuk. Dalam senario sedemikian, fungsi "get_worksheet_by_id ()" boleh digunakan.

Berikut adalah kod sampel untuk semua kaedah yang disenaraikan.

 Cetak (sh.get_worksheet (0))
Cetak (sh.worksheet ("ArtikelWorkSheet1"))
Cetak (sh.sheet1)
cetak (sh.get_worksheet_by_id (0))

Cetak ("Sekarang Ambil Semua Lembaran ...")

## Mengembalikan semua lembaran kerja
Untuk WS di sh.worksheets ():
    Cetak (WS)

Semua pernyataan cetak ini mengembalikan objek lembaran kerja

Membuat lembaran kerja baru

Selain daripada lembaran kerja yang sedia ada dalam spreadsheet, kita boleh membuat programatik membuat lembaran kerja baru dalam spreadsheet yang sama. Pendekatan ini berguna apabila memproses data dari lembaran kerja sedia ada dan menerbitkan hasil dalam lembaran kerja yang berasingan.

Untuk membuat lembaran kerja baru, kita perlu menggunakan fungsi "add_worksheet ()" objek lembaran kerja. Ia mengambil parameter berikut.

• Tajuk: Tajuk lembaran kerja
• Baris, Lajur, Indeks (Pilihan): Kita perlu menentukan bilangan baris dan lajur untuk lembaran kerja yang baru dibuat. Parameter "Indeks" adalah pilihan dan ia mengawal pesanan lembaran kerja.

Kod di bawah akan membuat lembaran kerja dengan 100 baris dan 20 lajur dan letakkan lembaran kerja di kedudukan kedua.

 sh.add_worksheet ('ArtikelWorkSheet1.5', baris = 100, cols = 20, indeks = 1)

Dan ia meletakkannya di kedudukan kedua (indeks 1)

Menamakan semula lembaran kerja

Anda boleh menamakan semula tajuk lembaran kerja menggunakan fungsi Update_title () objek lembaran kerja, yang menerima tajuk baru sebagai parameter.

 cetak (sh.worksheet ("ArtikelWorkSheet3"). Update_title ("ArticleWorkSheet2.5"))

Memadam lembaran kerja

Lembaran kerja boleh dipadam dari spreadsheet menggunakan fungsi berikut objek lembaran kerja:

• Padam lembaran kerja menggunakan objek lembaran kerja: fungsi "del_worksheet ()" mengambil objek lembaran kerja sebagai parameter dan memadamkan lembaran kerja dari spreadsheet.
• Padam lembaran kerja menggunakan ID lembaran kerja: "DEL_WORKSHEET_BY_ID ()" Fungsi mengambil ID lembaran kerja sebagai input untuk memadam lembaran kerja.

Pemilihan fungsi yang digunakan bergantung kepada kes penggunaan skrip dibuat. Berikut adalah sampel kod yang menunjukkan penggunaan kedua -dua fungsi.

 sh.del_worksheet (sh.worksheet ("artikelworksheet2.5"))
sh.del_worksheet_by_id ('602396579')

Sifat sel

Kami perlahan -lahan menyempitkan dari atas ke bawah dan mencapai unit terkecil (dan paling penting) lembaran kerja kami, sel. Sel adalah persimpangan baris dan lajur. Untuk Perpustakaan Gspread, ia memegang sifat berikut:

• Baris: Nombor baris untuk sel
• col: nombor lajur untuk sel
• Nilai: Nilai sel
• Alamat: Alamat sel dalam notasi A1

Kod sampel di bawah mengakses semua sifat sel. Sel tertentu untuk pemeriksaan dikembalikan menggunakan fungsi sel lembaran kerja.

 sampleCell = sh.worksheet ("ArtikelWorkSheet1"). Sel (baris = 1, col = 1)
cetak ('row: {} \ ncolumn: {} \ nValue: {} \ naddress: {}'.

Semua aksesor ini akan dimainkan sebaik sahaja kami berurusan dengan fungsi pesanan tinggi perpustakaan.

Menambah baris dan lajur baru

Mari mula menambah baris dan lajur baru ke lembaran kerja sampel sedia ada kami untuk mempunyai beberapa data untuk berfungsi untuk bahagian -bahagian kemudian panduan ini. Penyisipan sebagai operasi disokong dalam dua cara oleh Perpustakaan GSPREAD.

Penyisipan pada kedudukan tertentu

Anda boleh memasukkan baris atau lajur pada kedudukan tertentu menggunakan Insert_Row (), Insert_Rows (), dan Insert_COLS () fungsi objek lembaran kerja. Fungsi ini membolehkan kami menambah baris atau lajur di lokasi tertentu dalam lembaran kerja. Fungsi spesifik seperti di bawah:

• Insert_row: Fungsi memerlukan parameter "nilai" sebagai senarai nilai untuk dimasukkan. Perintah nilai dalam senarai menentukan susunan baris yang dimasukkan. Parameter "Indeks", yang mungkir kepada 1, menentukan kedudukan untuk penyisipan baris. Parameter pilihan seperti "value_input_option" dan "mewarisi_from_before" mengawal bagaimana fungsi menafsirkan data input dan baris, dan sama ada ia harus menolak data secara langsung atau menghuraikannya seolah -olah pengguna menaip dalam UI.
• Insert_rows: Ia mengambil senarai senarai dalam parameter "Nilai" untuk memasukkan beberapa baris. Setiap senarai bertindak sebagai satu baris. Secara dalaman, ini adalah pelaksanaan sebenar bagaimana baris dimasukkan ke dalam lembaran kerja melalui GSPREAD. Fungsi "Insert_Row ()" memanggil fungsi "Insert_Rows ()" dan oleh itu, semua parameter yang diterangkan untuk fungsi "Insert_Row ()" berdiri benar untuk "Insert_rows ()" kecuali satu parameter. Dalam fungsi Insert_Row (), anda menentukan offset menggunakan parameter indeks, manakala dalam fungsi Insert_Rows (), anda menentukannya dengan parameter baris.
• Insert_cols: Fungsi ini adalah replika fungsi "Insert_rows ()" dengan nama parameter yang diubah untuk diimbangi dari "baris" ke "col". Selebihnya fungsi parameter pilihan tetap sama.

Sisipan selepas julat meja

Penyisipan ini hanya boleh digunakan untuk baris. Ia membolehkan kita memasukkan baris selepas julat jadual tertentu, di mana kedudukannya tidak diketahui. Sekali lagi, penyisipan boleh dilakukan dengan cara tunggal atau berbilang.

• APPEND_ROW: Ia mengambil nilai baris sebagai senarai melalui parameter "Nilai". Parameter "Table_Range" membantu menentukan julat jadual selepas penyisipan baris harus berlaku. Julat diberikan dalam notasi A1.
• Append_rows: Begitu juga "sisip_rows ()", "append_rows ()" adalah pelaksanaan sebenar penyisipan baris selepas julat jadual. Semua parameter untuk kedua -dua fungsi tetap sama dengan perbezaan yang "append_rows ()" mengambil senarai senarai dalam parameter "Nilai".

Inilah kod sampel yang:

• Menambah baris untuk lajur: a, b, c, dan d
• Menambah 4 baris di bawah lajur ini

 Sampleworksheet.insert_row (
    ['A', 'b', 'c', 'd']
)
Sampleworksheet.insert_rows (
    [
        ['Kg', 54, 23, 12],
        ['OG', 34, 12, 34],
        ['Saya', 23, 45, 90],
        ['Ye', 65, 12, 54]
    ], baris = 2
)

Sekarang, mari kita lakukan langkah -langkah berikut di atas ini:

• Tambah 2 baris untuk meneruskan dalam julat jadual ini
• Tambah lajur lain e

 SampleWorksheet.Append_Rows (
    [
        ['Sn', 67, 87, 45],
        ['Ar', 56, 23, 65]
    ],
    Table_range = "A1: D5"
)
SampleWorksheet.insert_cols (
    [
        ['E', 56, 34, 65, 34, 76, 45]
    ],
    col = 5
)

Nota: Pembolehubah SampleWorkSheet memegang objek lembaran kerja dan ia akan dirujuk ke seluruh panduan.

Mengambil sel dan nilai julat

Di bahagian terakhir, kami telah menyediakan data kami secara programatik menggunakan pelbagai operasi penyisipan. Sekarang, kita boleh mengambil data yang dimasukkan menggunakan pelbagai fungsi baca. Kami akan melihat bagaimana untuk mengambil sel dan kemudian bergerak untuk mengambil nilai dari pelbagai sel dan keseluruhan lembaran kerja.

Mengambil sel tunggal

Operasi bacaan yang paling asas pada lembaran kerja melibatkan nilai atau mana -mana harta sel lain seperti yang diterangkan dalam bahagian sebelumnya. Untuk mengambil satu sel, terdapat dua fungsi:

• ACELL: Ini mengambil alamat sel dalam notasi A1 dan mengembalikan objek sel.
• Sel: Ini mengambil koordinat sel dalam urutan (baris, lajur).

Kedua -dua fungsi ini mengembalikan objek sel dan kami telah melihat bagaimana untuk mendapatkan nilai dari objek ini. Fungsi ACELL digunakan di bahagian di mana kami menyediakan sambungan dengan API Google Sheets.

 Cetak (SampleWorksheet.Acell ('A1').
cetak (sampelworksheet.cell (1, 1) .value)

Mengambil semua sel lembaran kerja atau julat

• Kita boleh mendapatkan semua sel lembaran kerja dalam senarai objek sel menggunakan fungsi "get_all_cells ()" objek lembaran kerja. Tiada parameter untuk fungsi ini dan ia boleh dipanggil secara langsung pada objek lembaran kerja.
• Untuk mengambil objek sel untuk julat tertentu, gunakan fungsi julat () objek lembaran kerja. Fungsi ini menerima pelbagai bentuk input, seperti notasi A1, sempadan angka, atau julat yang dinamakan. Jika digunakan tanpa input, ia mengembalikan semua sel dalam lembaran kerja dalam panggilan API tunggal. Untuk kes penggunaan yang melibatkan sifat sel, fungsi ini membantu menapis sel yang dikehendaki dan melakukan tindakan selanjutnya.

 cetak (sampelworksheet.get_all_cells ())
cetak (sampelworksheet.range ('b4: e5'))

Mengambil nilai julat sel

Pengguna biasanya membuat beberapa jadual kecil dalam lembaran kerja yang sama untuk kebolehcapaian yang lebih baik. Dalam kes sedemikian, kita perlu memperbaiki julat pengambilan kita ke alamat yang tepat dari julat jadual ini. Untuk mengambil julat jadual sedemikian, kita boleh menggunakan dua fungsi berikut objek lembaran kerja:

• GET: Fungsi "GET ()" mengambil julat jadual dalam notasi A1 atau julat yang dinamakan dan mengembalikan senarai senarai nilai.
• Batch_get: Fungsi "GET ()" hanya boleh mengambil satu julat, tetapi jika kita berurusan dengan pelbagai julat, kita boleh menggunakan batch_get. Fungsi ini menjadikan satu panggilan API, menjimatkan kos.

 cetak ('Get Range: {}'. Format (SampleWorkSheet.get ("A1: D4")))
Cetak ('Batch Get Range: {}'. Format (sampelWorksheet.batch_get ([[
    "A1: D4",
    "B4: E3"
))))

Mengambil semua nilai dari baris atau lajur

Kita boleh mengambil semua nilai baris atau lajur menggunakan fungsi "row_values ​​()" dan "col_values ​​()" objek lembaran kerja. Kedua -dua fungsi mengambil kedudukan (berjumlah dari 1) baris atau lajur dan mengembalikan nilai dalam senarai.

 cetak (sampelworksheet.row_values ​​(1))
cetak (sampelworksheet.col_values ​​(4))

Mengambil keseluruhan nilai lembaran kerja

Salah satu kes penggunaan terbaik untuk mengambil lembaran kerja keseluruhan adalah untuk memuatkan data ini secara langsung ke dalam bingkai data Pandas dan kemudian melakukan pemprosesan atau analisis selepas keperluan. Seluruh data boleh dikembalikan menggunakan fungsi berikut objek lembaran kerja:

• Senarai Senarai: Fungsi "get_all_values ​​()" mengembalikan setiap baris sebagai senarai dan kemudian semua baris senarai dalam satu senarai. Fungsi ini adalah alias kepada fungsi "get_values ​​()" tetapi menarik, fungsi "get_values ​​()" dilaksanakan menggunakan fungsi "get ()". Fungsi "mendapatkan ()" tanpa sebarang input mengembalikan senarai senarai. Oleh itu semua 3 fungsi adalah sama.
• Senarai kamus: fungsi "get_all_records ()" mengembalikan senarai kamus. Setiap kamus adalah pemetaan nilai utama di mana kunci adalah nilai baris pertama dan nilai-nilai sebagai nilai baris seterusnya. Setiap baris mendapat kamus. Secara lalai, ia mengandaikan bahawa baris pertama adalah kunci tetapi kita dapat menyedari baris yang berbeza sebagai kunci menggunakan parameter "header". Terdapat beberapa parameter tambahan yang boleh membantu dalam mengendalikan sel -sel kosong, dan tajuk yang dijangkakan.

Anda boleh secara langsung lulus output kedua -dua fungsi ke fungsi data Pandas untuk mendapatkan jadual lembaran kerja sebagai data Pandas.

 Import Pandas sebagai PD
cetak (pd.dataFrame (sampelworksheet.get_all_records ()))
cetak (pd.dataFrame (sampleworksheet.get_all_values ​​()))

Mengemas kini sel dan julat

Mengemas kini data sedia ada spreadsheet adalah fungsi yang paling penting yang boleh dilakukan dengan mudah menggunakan fungsi perpustakaan GSPREAD. Terdapat pelbagai cara untuk mengemas kini sel -sel spreadsheet, pengemaskinian sel tunggal ke pelbagai sel dan kemudian ke pelbagai julat dengan panggilan API tunggal.

Mengemas kini sel tunggal

Sel tunggal lembaran kerja boleh dikemas kini menggunakan fungsi berikut objek lembaran kerja.

• UPDATE_ACELL: Fungsi ini mengambil dua parameter, alamat sel dalam notasi A1 dan nilai yang akan dikemas kini
• UPDATE_CELL: Fungsi ini mengambil koordinat sel dalam urutan baris lajur dan nilai untuk dikemas kini
• UPDATE: Walaupun fungsi ini mempunyai skop yang lebih besar untuk mengemas kini pelbagai sel, ia juga boleh digunakan untuk mengemas kini sel tunggal. Perintah parameter input berbeza dari dua fungsi di atas. Fungsi "Update ()" mengambil senarai senarai sebagai nilai pertama dan kemudian alamat sel.

 cetak (sampelworksheet.update_acell ('a2', 'kaustubh'))
cetak (sampelworksheet.update_acell ('a3', 'oggy'))
cetak (sampelworksheet.update ([['hello']], 'a4'))

Mengemas kini pelbagai sel

Anda boleh mengemas kini pelbagai sel dalam lembaran kerja menggunakan dua fungsi berikut dari objek lembaran kerja.

• UPDATE_CELLS: Fungsi ini berfungsi dengan baik dalam kombinasi dengan fungsi "julat ()". Fungsi "update_cells ()" mengambil input sebagai senarai sel. Senarai sel ini boleh mempunyai nilai mereka diubah dengan gelung ke atas objek sel yang dikembalikan dari fungsi julat dan mengakses harta nilai mereka.
• UPDATE: Seperti yang dilihat sebelum ini dalam kemas kini sel tunggal, anda boleh menggunakan fungsi ini untuk mengemas kini julat bernama atau julat yang dinamakan A1.

 RangeOfCells = SampleWorksheet.Range ('B2: B7')
untuk sel dalam rangeofcells:
    newValue = int (cell.value) 10
    Cell.Value = newValue
cetak (sampelworksheet.update_cells (rangeOfCells))

Kod di atas mengambil pelbagai sel, menambah 10 nilai mereka, dan mengemas kini mereka dalam satu panggilan API.

Mengemas kini pelbagai sel

Di bahagian di atas, kami dapat mengemas kini pelbagai sel dalam julat dengan panggilan API tunggal. Tingkah laku ini boleh diperluaskan kepada pelbagai julat juga. Ini bermakna kita boleh mengemas kini pelbagai kumpulan sel dengan satu panggilan. Fungsi "batch_update ()" mengambil senarai kamus dengan kunci sebagai julat dan nilai. Nilai kunci julat mestilah julat notasi A1 atau julat yang dinamakan dan nilai kunci nilai sebagai senarai senarai nilai.

 Range1 = 'C2: C7'
Range2 = 'E2: E7'
BothRangEvalues ​​= sampleworksheet.batch_get ([[
    Range1,
    Julat2
])
Range1Values, Range2Values ​​= BothrangEvalues
Range1UpdatedValues ​​= [[int (x [0]) 10] untuk x dalam Range1Values]
Range2UpdatedValues ​​= [[int (x [0]) 20] untuk x dalam Range2Values]
cetak (sampelworksheet.batch_update ([[
    {
        'Julat': Range1,
        'Nilai': Range1UpdatedValue
    },

    {
        'Julat': Range2,
        'Nilai': Range2UpdatedValue
    }
))))

Kod di atas mengambil dua julat menggunakan fungsi "batch_get ()", kemudian mengemas kini nilai mereka secara tempatan, dan kemudian menggunakan fungsi "batch_update ()" untuk menolak kembali nilai yang dikemas kini ke lembaran Google. Output kemas kini ini kelihatan seperti ini:

Memadam baris dan lajur

Sehingga ini, kami telah memasukkan, membaca, dan mengemas kini data dalam lembaran kerja. Kami boleh melakukan operasi memadam untuk mengeluarkan data yang berlebihan atau tidak perlu dari lembaran kerja. Fungsi "delete_rows ()" dan "delete_colums ()" mengambil "start_index" untuk dipadam. Jika "end_index" ditentukan, maka ia memadam semua lajur dalam julat indeks permulaan dan akhir.

 cetak (sampelworksheet.delete_columns (4))
cetak (sampelworksheet.delete_rows (6))

Mencari sel

API Helaian Google membolehkan anda mencari sel dengan memadankan rentetan atau ungkapan biasa. Anda boleh melakukan carian sensitif atau kes-insensive kes dan sempit carian ke baris atau lajur tertentu jika dikehendaki. Gunakan kedua -dua fungsi lembaran kerja ini untuk mencari sel yang sepadan:

• Cari: Fungsi "Cari ()" mengembalikan kejadian pertama perlawanan. Ini mengambil rentetan carian atau parameter "in_row" atau "in_column" untuk menyempitkan carian dan bendera "case_sensitive" untuk mengawal jenis carian. Parameter "In" mengambil kedudukan baris atau lajur (indeks 1)
• FindAll: "findall ()" adalah tahap lanjutan fungsi "cari ()" di mana ia mengembalikan semua perlawanan carian.

 Import Re
cetak (sampelworksheet.find ('64 ', in_column = 2))
searchRe = re.compile (r '(a | a)')
cetak (sampelworksheet.findall (searchre))

Sel pemformatan

Dalam Excel, anda boleh memformat lembaran kerja dalam pelbagai cara, termasuk sorotan teks, pemformatan, sempadan, penjajaran, dan pelbagai fungsi. Lembaran Google juga menawarkan pelbagai pilihan pemformatan untuk sel. Seluruh senarai bidang boleh didapati dalam dokumentasi sel Google Sheets .

Anda boleh menggunakan fungsi format () objek lembaran kerja gspread untuk menentukan sel atau julat di mana anda ingin memohon pemformatan. Sediakan format sebagai kamus JSON, yang merangkumi semua medan utama pemformatan dan nilai mereka.

Kod di bawah akan menggunakan sempadan ke semua sel meja.

 borderFormatting = {
    "gaya": "pepejal",
    "ColorStyle": {"rgbcolor": {"Red": 0, "Green": 0, "Blue": 0, "Alpha": 1}},
}

Cetak (
    SampleWorksheet.Format (
        "A1: D6",
        format = {
            "Sempadan": {
                "Atas": BorderFormatting,
                "Bawah": BorderFormatting,
                "Kiri": BorderFormatting,
                "betul": borderformatting,
            },
        },
    )
)

Kami juga boleh memohon pemformatan batch untuk memformat pelbagai julat pada masa yang sama. Ini menjimatkan banyak masa menulis panggilan format yang berbeza untuk setiap perubahan baru. Fungsi "batch_format ()" mengambil senarai kamus yang mengandungi dua kunci penting. Kunci pertama ialah kunci julat yang mentakrifkan skop sel dan kekunci format yang mengandungi kamus pemformatan.

Mari kita lakukan yang berikut pada jadual sampel kami menggunakan fungsi "batch_format ()":

• Sapukan sempadan ke semua sel meja.
• Berani teks baris pertama, menunjukkan bahawa mereka adalah lajur jadual kami.
• Selaraskan semua data di tengah.
• Tambah warna biru muda untuk lajur (baris pertama).

 borderFormatting = {
    "gaya": "pepejal",
    "ColorStyle": {"rgbcolor": {"Red": 0, "Green": 0, "Blue": 0, "Alpha": 1}},
}
Format = [
    {
        "Julat": "A1: D6",
        "Format": {
            "Sempadan": {
                "Atas": BorderFormatting,
                "Bawah": BorderFormatting,
                "Kiri": BorderFormatting,
                "betul": borderformatting,
            },
            "HorizontalAlignment": "Center",
        },
    },
    {
        "Julat": "A1: D1",
        "Format": {
            "TextFormat": {
                "berani": benar,
            },
            "Latar BelakangColorStyle": {
                "rgbcolor": {"merah": 0.8, "hijau": 0.8, "biru": 1, "alpha": 0.8}
            },
        },
    },
]

cetak (sampelworksheet.batch_format (format))

Dan ini adalah keadaan terakhir meja kami.

Julat sel dan lembaran kerja yang jelas

Mungkin kita ingin membersihkan julat sebelum menjalankan operasi. Untuk membersihkan julat sel, fungsi "batch_clear ()" objek lembaran kerja digunakan. Ini mengambil senarai julat yang perlu dibersihkan. Mari kita jelaskan lajur C dari jadual sampel kami.

 cetak (sampelworksheet.batch_clear (["c1: c6"]))

Nota: Fungsi jelas hanya membersihkan nilai dan bukan pemformatan yang digunakan.

Keseluruhan lembaran kerja boleh dibersihkan menggunakan fungsi "jelas ()" objek lembaran kerja.

 Cetak (sampelWorksheet.clear ())

Semak artikel ini mengenai Cara Menggunakan Python untuk mengautomasikan Helaian Google

Batasan API Google

Kami telah melakukan banyak operasi di Helaian Google menggunakan Perpustakaan GSPREAD. Perpustakaan ini hanyalah pembungkus yang menyediakan data yang diluluskan pengguna ke dalam format yang boleh diterima dan membuat panggilan API ke projek Google yang berkaitan dengan lembaran. Ia berfungsi supaya pemaju tidak perlu memahami panggilan API yang mendasari, muatan, dan respons. Pemaju hanya berinteraksi dengan fungsi yang dicabut.

• Memahami Penggunaan Kuota API : Pemaju mesti memahami bagaimana panggilan API menggunakan kuota, terutamanya untuk pengeluaran dan tugas kritikal.
• Penggunaan Percuma Tetapi Terhad : API Helaian Google adalah percuma tetapi mempunyai sekatan ke atas bilangan panggilan API yang boleh dibuat.
• Kesalahan biasa - 429 ("terlalu banyak permintaan") : Banyak pengguna menghadapi ralat 429 kerana melebihi had panggilan API.
• Contoh Had Kuota : Kuota semasa membolehkan 300 permintaan per minit setiap projek.
• Melebihi kuota : Jika skrip menghantar lebih daripada 300 permintaan, permintaan tambahan tidak diproses.
• Penyelesaian backoff eksponen : Mekanisme semula seperti backoff eksponen, yang menggunakan masa tunggu rawak, dapat membantu menguruskan batasan -batasan ini.

Kesimpulan

Dalam panduan ini, kami membuat akaun Perkhidmatan Google untuk melaksanakan semua operasi yang akan dilakukan pada UI Helaian Google. Kami meneroka banyak fungsi seperti menambah, mengemas kini, dan memadam data. Kami juga meneroka cara memformat helaian dan automasi Google Sheets menggunakan Python.

API Helaian Google menawarkan lebih banyak fungsi seperti penggabungan sel, membuat julat yang dilindungi, menyembunyikan sel, menambah nota, salinan salinan, atau menambah penapis, semua operasi secara programatik! Walaupun dokumentasi untuk Perpustakaan GSPREAD tidak mempunyai penjelasan ini, seseorang boleh meneruskan untuk meneroka dokumentasi rasmi Google Sheets dan juga menyemak bahagian rujukan API dokumentasi GSPREAD yang memberikan maklumat peringkat tinggi mengenai semua fungsi yang dilaksanakan di perpustakaan.

Takeaways utama

• Automasi Helaian Google memerlukan menyediakan projek Google Cloud, yang tidak dikenakan biaya. Walau bagaimanapun, jika anda tidak mengoptimumkan kod tersebut, anda mungkin akan membuang kuota, yang membawa kepada kesilapan kemudian dalam kod. Bertujuan untuk menggunakan fungsi batch di mana mungkin.
• Terdapat banyak kemungkinan apa yang dapat dicapai menggunakan API Google Sheets dan Perpustakaan GSPREAD hanyalah titik permulaan. Seseorang harus meneroka dokumentasi rasmi untuk melaksanakan fungsi yang hilang di perpustakaan (dan mungkin menyumbang kembali)
• Anda boleh menggunakan persediaan automasi Google Sheets untuk membina saluran paip sumber yang mengekalkan rekod induk dan menolaknya ke pangkalan data yang lebih maju, seperti pangkalan data OLAP. Sambungkan pangkalan data ini ke perisian BI, seperti Tableau, untuk menyelesaikan projek akhir-ke-akhir.
• Automasi Helaian Google boleh membantu anda menghapuskan kemasukan data manual dengan menyediakan aliran kerja yang secara automatik mengemas kini, mengimport, atau menyusun data, meningkatkan kecekapan dengan ketara.
• Menggunakan Automasi Helaian Google, anda boleh menjadualkan tugas berulang seperti menghantar laporan, melakukan pengiraan, atau mengemas kini sel, mengurangkan keperluan untuk tindakan manual berulang.

Soalan yang sering ditanya

Q1. Apa yang boleh dilakukan untuk mengelakkan ralat "429: terlalu banyak permintaan"?

A. Mekanisme semula perlu dilaksanakan yang cuba membuat permintaan lagi dalam beberapa waktu. Salah satu contohnya ialah algoritma backoff eksponen

Q2.Bakakah anda melakukan automasi di Helaian Google?

A. Ya, anda boleh mengautomasikan tugas dalam Helaian Google menggunakan ciri-ciri terbina dalam seperti Macros , skrip Google Apps , dan alat pihak ketiga seperti Zapier atau Make (dahulu Integromat) . Ini membolehkan anda mengautomasikan tugas berulang seperti kemasukan data, pemformatan, dan pengiraan.

Q3. Adakah Google mempunyai alat automasi?

A. Ya, Google menawarkan skrip Google Apps , platform berasaskan JavaScript untuk mengautomasikan tugas di seluruh aplikasi Google Workspace (misalnya, lembaran, dokumen, Gmail). Additionally, tools like Google Cloud Workflows and Zapier integrations can be used for broader automation across Google services.

Q4. How can I set up Google Sheets automations to save time on repetitive tasks?

A. You can set up Google Sheets automations using built-in tools like Macros and Google Apps Script. Macros allow you to record actions and replay them, while Google Apps Script lets you create custom automations using JavaScript. These automations can streamline repetitive tasks such as data entry, formatting, or running specific functions automatically in Google Sheets.

S5. What are some popular Google Sheets automations for data analysis?

A. Popular Google Sheets automations for data analysis include automated data import from external sources, scheduled reports using Google Apps Script, and conditional formatting to highlight trends. These automations help optimize the data analysis process, making Google Sheets a powerful tool for managing and interpreting large datasets efficiently.

Atas ialah kandungan terperinci Automasi Helaian Google Menggunakan Python | Analytics Vidhya. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!