Geekly articles weekly

Memulai dengan Google Sheets di Python. Dari mendaftar hingga membaca data

Untuk beberapa waktu saya berhasil mengunggah data ke Excel, tetapi modnya berubah, pengguna menginginkannya di awan.

Setelah mulai menerjemahkan sejumlah proyek ke dalam Python, saya memutuskan bahwa sudah waktunya untuk mengubah (atau menambah) Excel dengan sesuatu yang lebih modern.

Ketika saya pertama kali menemukan kebutuhan untuk bekerja dengan Google spreadsheet dari Python, saya berada di bawah ilusi bahwa semua ini dapat dilakukan dalam beberapa klik. Realitas ternyata tidak terlalu cerah, tetapi kita tidak memiliki globe yang lain.

Artikel-artikel itu banyak membantu saya:


Seperti biasa - ketika Anda pertama kali mengambil sesuatu, Anda dihadapkan dengan banyak pertanyaan yang kemudian hanya menyebabkan kebingungan - bagaimana mungkin ada orang yang berpikir tentang hal ini. SD!

Mungkin saya hanya berjalan jauh - saya akan senang jika Anda memperbaiki saya.

Semua tindakan dilakukan pada komputer yang menjalankan Windows + Python 3.6.6, dan Notebook Jupyter juga digunakan.

Kesulitan utama yang saya miliki pada tahap pengaturan awal. Menemukan kode yang bisa diterapkan tidak sulit.

Kode yang digunakan dalam artikel tersedia di repositori.

Mendaftar untuk layanan Google dan instal perpustakaan


Untuk bekerja dengan tabel, Anda harus mendaftar dengan Google, mengatur proyek dan menginstal perpustakaan yang diperlukan.

Dokumentasi resmi dalam bahasa Inggris ada di sini .

Pertama, Anda perlu mendaftar di gmail.com (Anda dapat melakukannya sendiri). Maka Anda perlu membuat proyek (karena Google menyediakan akses ke layanannya).

Ini adalah proses yang panjang dan melelahkan yang memungkinkan Anda memahami mengapa antarmuka Google disebut bukan yang paling nyaman dan intuitif (beberapa percaya bahwa jaringan sosial Google+ tidak lepas landas karena alasan ini).

Untuk melakukan ini, buka console.developers.google.com/cloud-resource-manager dan klik "Buat Proyek"



Masukkan nama proyek dan klik "Buat"



Dalam daftar proyek yang diperbarui buka menu "Izin"


Di jendela yang terbuka, klik "Tambah", masukkan alamat email Anda dari domain gmail.com dan pilih grup "Proyek" - "Pemilik"



Simpan perubahannya.

Mungkin terlihat aneh bahwa Anda membuat proyek tetapi dipaksa untuk mengeluarkan hak untuk diri sendiri. Dan ini sebenarnya aneh, tetapi itu adalah jalan yang harus diambil pada saat menulis kursus ini, sehingga semuanya akan mulai berfungsi sebagaimana mestinya.

Kunjungi console.developers.google.com/cloud-resource-manager lagi

Pilih menu Pengaturan pada proyek Anda



Di jendela yang terbuka, pilih "Akun layanan" dan kemudian "Buat akun layanan"



Masukkan nama akun dan klik "Buat"



Pilih peran Pemilik dan klik Lanjutkan.



Di jendela yang muncul, klik Buat Kunci



Pilih jenis kunci json dan klik Buat



File dengan kunci akan dibuat dan segera diunduh. Simpan itu, berkat itu kita akan dapat mengakses layanan Google.

Klik tombol dengan tiga goresan horizontal, di sebelah kiri tulisan "Google API", pilih "API dan Layanan", dan di dalamnya ada sub-item "Panel Kontrol".



Di jendela yang terbuka, klik "Aktifkan API dan Layanan"



Ketik "google drive" ke dalam bilah pencarian dan klik layanan "Google Drive API"



Klik Aktifkan



Situs ini akan memberi tahu Anda bahwa API diaktifkan dan memperingatkan Anda bahwa kredensial harus dibuat. Abaikan peringatan ini (kami telah membuat akun layanan).



Kembali ke panel kontrol



Di jendela yang terbuka, klik "Aktifkan API dan Layanan"



Masukkan "sheet" di bilah pencarian dan klik layanan "Google Sheets API"



Pastikan API ini terhubung. Ini akan hidup secara otomatis ketika Anda menghubungkan Google Drive API. Jika terhubung, Anda akan melihat tombol "Kelola API", jika tidak, tombol "Aktifkan". Nyalakan jika perlu.

Terakhir kali, kunjungi console.developers.google.com/cloud-resource-manager

Pilih menu Pengaturan pada proyek Anda


Di jendela yang terbuka, pilih "Akun layanan", lalu salin dan simpan alamat email akun layanan. Ini akan berguna bagi Anda untuk memberikan akses ke tabel.



Sekarang kita lanjutkan ke instalasi perpustakaan. Jalankan perintah di konsol

pip3 install --upgrade google-api-python-client

dan kemudian

 pip3 install oauth2client

Ada kemungkinan bahwa ketika Anda menjalankan perintah kedua, Anda akan menerima pesan bahwa pustaka oauth2client sudah diinstal.

Pergi ke raw.githubusercontent.com/gsuitedevs/python-samples/master/sheets/quickstart/quickstart.py

Tekan tombol kanan mouse dan pilih "Save As"



Simpan file sebagai quickstart.py

dan jalankan dengan perintah

 python quickstart.py

Halaman baru akan terbuka di browser (mungkin itu akan mengatakan bahwa halaman itu tidak aman, tapi terus terang saja) dan Anda harus menerima persyaratannya.

Pada jalan ini kita selesai.

Mengisi dan memformat tabel


Buat tabel pertama

 #   import httplib2 import apiclient.discovery from oauth2client.service_account import ServiceAccountCredentials CREDENTIALS_FILE = 'seraphic-effect-248407-7ac2c44ec709.json' #     ,     #     credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']) httpAuth = credentials.authorize(httplib2.Http()) #    service = apiclient.discovery.build('sheets', 'v4', http = httpAuth) #      4  API spreadsheet = service.spreadsheets().create(body = { 'properties': {'title': '  ', 'locale': 'ru_RU'}, 'sheets': [{'properties': {'sheetType': 'GRID', 'sheetId': 0, 'title': '  ', 'gridProperties': {'rowCount': 100, 'columnCount': 15}}}] }).execute() spreadsheetId = spreadsheet['spreadsheetId'] #    print('https://docs.google.com/spreadsheets/d/' + spreadsheetId)

Jika semuanya berjalan tanpa kesalahan, tautan ke tabel akan ditampilkan.

Pengidentifikasi file digunakan dalam tautan ini, kami menyimpannya dalam variabel spreadsheetId dan akan menggunakannya di masa mendatang.

Ikuti itu. Google akan memberi tahu Anda bahwa Anda tidak memiliki akses



Jangan minta izin! Anda akan menerima pemberitahuan bahwa tidak mungkin mengirimkan surat permintaan ke alamat yang ditetapkan Google sendiri ke akun sistem. Tetapi Anda tidak dapat mengubah alamat ini. Mungkin ini tidak hanya bekerja dalam mode bebas.

Tetapi kami dapat memberikan diri kami akses melalui Google Drive. Anda perlu mengganti my_test_address@gmail.com dengan milik Anda.

 driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) #    Google Drive  3  API access = driveService.permissions().create( fileId = spreadsheetId, body = {'type': 'user', 'role': 'writer', 'emailAddress': 'my_test_address@gmail.com'}, #     fields = 'id' ).execute()

Sekarang Anda memiliki akses, jangan tutup tabel, kami akan mengelolanya dan segera melihat perubahannya.

Setiap dokumen memiliki kode sendiri - spreadsheetId - persis seperti yang ditampilkan di bilah alamat ketika kita membuka tabel di browser (di URL halaman dengan tabel terbuka, itu berada di antara "https://docs.google.com/spreadsheets/d/" dan "/ edit # gid = 0").

Kami menyimpannya di variabel spreadsheetId dan akan terus bekerja dengannya.

Pertama, sedikit teori.

Di setiap file (spreadsheet) ada sheet-tabs (sheet).

Setiap lembar memiliki kode numeriknya sendiri (sheetId). Lembar pertama yang dibuat dalam dokumen memiliki Id ini sama dengan 0. Lembar yang tersisa memiliki Id non-nol yang sangat berbeda (yaitu, mereka tidak diberi nomor secara berurutan).

Pastikan ini

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "addSheet": { "properties": { "title": "  ", "gridProperties": { "rowCount": 20, "columnCount": 12 } } } } ] }).execute() #   ,  Id   spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute() sheetList = spreadsheet.get('sheets') for sheet in sheetList: print(sheet['properties']['sheetId'], sheet['properties']['title']) sheetId = sheetList[0]['properties']['sheetId'] print('     Id = ', sheetId)

Sesuatu seperti ini akan muncul di layar:

0 Lembar nomor satu
415832263 Daun lagi
Kami akan menggunakan sheet dengan Id = 0


Bahkan, lembar pertama memiliki Id nol, dan yang kedua diberi nomor berbeda.

Satu pertanyaan lagi: bagaimana menentukan rentang sel. Rupanya, spreadsheet Google dikembangkan oleh tim yang berbeda, di bawah bimbingan manajer yang berbeda dan dengan bantuan arsitek yang berbeda. Karena koordinat sel diatur dalam dua cara berbeda.

Opsi 1: dalam format teks "Lembar nomor satu! B2: D5", mis. nama lembar, diikuti dengan tanda seru, setelah - sel kiri atas dalam format "letter (kolom) + number (string)" + sel kanan bawah dalam format yang sama.

 {"range": "  !B2:D5"}

Opsi 2: dalam format json, menunjukkan ID sheet dan koordinat sel kiri atas dan kanan bawah dalam bentuk numerik (nomor baris dan nomor kolom)

 {"range": { "sheetId": sheetId, # ID  "startRowIndex": 1, #    startRowIndex "endRowIndex": 5,#  endRowIndex - 1 (endRowIndex  !) "startColumnIndex": 0, #    startColumnIndex "endColumnIndex": 1 #  endColumnIndex - 1 }}

Fungsi yang berbeda menggunakan format yang berbeda.

Sekarang kita cukup tahu untuk mengisi sel dengan data, menggambar bingkai dan menyorot header.

 results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B2:D5", "majorDimension": "ROWS", #   ,   "values": [ [" B2", " C2", " D2"], #    ['25', "=6*6", "=sin(3,14/2)"] #    ]} ] }).execute()

Isi beberapa sel dengan data. Karena parameter USER_ENTERED ditentukan, tabel memahami data ini karena akan menerima input pengguna - mengonversi nilai numerik menjadi angka, dan nilai yang dimulai dengan tanda sama dengan rumus.

Lihatlah meja Anda, itu diisi dengan data



Atur lebar kolom. Fungsi batchUpdate dapat menerima beberapa perintah sekaligus, jadi kami akan mengatur lebar tiga grup kolom dengan satu permintaan. Ada satu kolom di kelompok pertama dan ketiga, dan dua di kelompok kedua.

 results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheetId, body = { "requests": [ #    A: 20  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", #    "startIndex": 0, #     "endIndex": 1 #    startIndex  endIndex - 1 (endIndex  !) }, "properties": { "pixelSize": 20 #    }, "fields": "pixelSize" # ,     pixelSize } }, #    B  C: 150  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 1, "endIndex": 3 }, "properties": { "pixelSize": 150 }, "fields": "pixelSize" } }, #    D: 200  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 3, "endIndex": 4 }, "properties": { "pixelSize": 200 }, "fields": "pixelSize" } } ] }).execute()

Lihat tabel, lebar kolom telah berubah.



Gambarlah bingkai di sekeliling meja

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'updateBorders': {'range': {'sheetId': sheetId, 'startRowIndex': 1, 'endRowIndex': 3, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'bottom': { #      'style': 'SOLID', #   'width': 1, #  1  'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, #   'top': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'left': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'right': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerHorizontal': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerVertical': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}} }} ] }).execute()



Gabungkan sel-sel di atas tabel dan masukkan judul di dalamnya.

 #   A2:D1 results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'mergeCells': {'range': {'sheetId': sheetId, 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'mergeType': 'MERGE_ALL'}} ] }).execute() #    results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B1", "majorDimension": "ROWS", #   ,   "values": [[" " ] ]} ] }).execute()



Tetapkan format untuk sel header tabel

 #    results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "repeatCell": { "cell": { "userEnteredFormat": { "horizontalAlignment": 'CENTER', "backgroundColor": { "red": 0.8, "green": 0.8, "blue": 0.8, "alpha": 1 }, "textFormat": { "bold": True, "fontSize": 14 } } }, "range": { "sheetId": sheetId, "startRowIndex": 1, "endRowIndex": 2, "startColumnIndex": 1, "endColumnIndex": 4 }, "fields": "userEnteredFormat" } } ] }).execute()



Ada cara mudah untuk mengetahui berapa lebar atau warna yang Anda butuhkan untuk sebuah sel. Untuk melakukan ini, cukup dengan memformat salah satu sel secara manual dan membaca propertinya.

 ranges = ["  !C2:C2"] # results = service.spreadsheets().get(spreadsheetId = spreadsheetId, ranges = ranges, includeGridData = True).execute() print(' ') print(results['properties']) print('\n  ') print(results['sheets'][0]['data'][0]['rowData'] ) print('\n ') print(results['sheets'][0]['data'][0]['rowMetadata']) print('\n ') print(results['sheets'][0]['data'][0]['columnMetadata'])

Kami mendapat tanggapan
Data master
{'title': 'Dokumen uji pertama', 'lokal': 'ru_RU', 'autoRecalc': 'ON_CHANGE', 'timeZone': 'Etc / GMT', 'defaultFormat': {'backgroundColor': {'backgroundColor': {'red' : 1, 'hijau': 1, 'biru': 1}, 'padding': {'top': 2, 'right': 3, 'bottom': 2, 'left': 3}, 'verticalAlignment': 'BOTTOM', 'wrapStrategy': 'OVERFLOW_CELL', 'textFormat': {'foregroundColor': {}, 'fontFamily': 'arial, sans, sans-serif', 'fontSize': 10, 'bold': False, 'italic': False, 'strikethrough': False, 'underline': False}}}

Nilai dan pewarnaan
[{'values': [{'userEnteredValue': {'stringValue': 'Cell C2'}, 'effectiveValue': {'stringValue': 'Cell C2'}, 'formattedValue': 'Cell C2', 'userEnteredFormat' : {'backgroundColor': {'red': 1, 'green': 0.6}, 'horizontalAlignment': 'CENTER', 'textFormat': {'fontSize': 14, 'bold': True, 'italic': True }}, 'efektifFormat': {'backgroundColor': {'red': 1, 'green': 0.6}, 'padding': {'top': 2, 'right': 3, 'bottom': 2, ' kiri ': 3},' horizontalAlignment ':' CENTER ',' verticalAlignment ':' BOTTOM ',' wrapStrategy ':' OVERFLOW_CELL ',' textFormat ': {' foregroundColor ': {},' fontFamily ':' Arial ', 'fontSize': 14, 'bold': True, 'italic': True, 'strikethrough': False, 'underline': False}, 'hyperlinkDisplayType': 'PLAIN_TEXT'}}]}]]]

Tinggi sel
[{'pixelSize': 21}]

Lebar sel
[{'pixelSize': 150}]
Kode ini akan menampilkan properti sel C2. Anda dapat memilih font dan mengisi warna secara manual (dalam tabel), kemudian melihat bagaimana mereka tercermin dalam json.

Membaca data dari sebuah tabel


Untuk sepenuhnya mengungkapkan fitur pembacaan data, saya secara manual mengisi sel B4, C7 dan D5 seperti yang ditunjukkan pada gambar.



Kode untuk membaca data

 ranges = ["  !A2:F8"] # results = service.spreadsheets().values().batchGet(spreadsheetId = spreadsheetId, ranges = ranges, valueRenderOption = 'FORMATTED_VALUE', dateTimeRenderOption = 'FORMATTED_STRING').execute() sheet_values = results['valueRanges'][0]['values'] print(sheet_values)

Hasil
[['', 'Sel B2', 'Sel C2', 'Sel D2'], ['', '25', '36', '0,9999996829']]

Beberapa parameter fungsi: valueRenderOption - format untuk membaca data numerik.

  • FORMATTED_VALUE - membaca berdasarkan format tampilan. Yaitu apa yang terlihat di tabel akan dibaca. Misalnya, dalam sel D3, angkanya adalah 0,9999999, tetapi formatnya adalah "dua tempat desimal", sehingga "1,00" ditampilkan, dalam format inilah ia akan dibaca.
  • UNFORMATTED_VALUE - konten sel dibaca, pemformatan tidak diperhitungkan (mis., 0,9999999 akan dibaca)
  • FORMULA - rumus ditampilkan (dalam hal ini "= sin (3,14 / 2)." Jika suatu angka dimasukkan dalam sel, maka dalam mode ini akan dibaca.

Kode ini membaca data dan menampilkannya baris demi baris. Rentang yang Dapat Dibaca A2: F8.
Seperti yang terlihat di layar:

  • Jika tidak ada sel di baris yang sedang dibaca yang terisi, data pada saluran tersebut tidak ditampilkan.
  • Data setelah sel terisi terakhir tidak ditampilkan.

Source: https://habr.com/ru/post/id483302/

More articles:


All Articles