gspread
v6.1.4
Antarmuka sederhana untuk bekerja dengan Google Spreadsheet.
Fitur:
pip install gspreadPersyaratan: Python 3.8+.
Buat kredensial di Konsol API Google
Mulai gunakan gspread
import gspread
gc = gspread . service_account ()
# Open a sheet from a spreadsheet in one go
wks = gc . open ( "Where is the money Lebowski?" ). sheet1
# Update a range of cells using the top left corner address
wks . update ([[ 1 , 2 ], [ 3 , 4 ]], "A1" )
# Or update a single cell
wks . update_acell ( "B42" , "it's down there somewhere, let me take another look." )
# Format the header
wks . format ( 'A1:B1' , { 'textFormat' : { 'bold' : True }})Python 3.7 sudah habis masa pakainya. gspread v6 membutuhkan minimal Python 3.8.
Worksheet.update Dua argumen pertama ( values & range_name ) telah ditukar (ke range_name & values ). Tukarkan keduanya (hanya berfungsi di v6), atau gunakan argumen bernama (berfungsi di v5 & v6).
Selain itu, values tidak bisa lagi berupa daftar, dan harus berupa array 2D.
- file.sheet1.update([["new", "values"]])
+ file.sheet1.update([["new", "values"]]) # unchanged
- file.sheet1.update("B2:C2", [["54", "55"]])
+ file.sheet1.update([["54", "55"]], "B2:C2")
# or
+ file.sheet1.update(range_name="B2:C2", values=[["54", "55"]]) v6 menggunakan representasi warna heksadesimal. Ubah semua warna menjadi hex. Anda dapat menggunakan fungsi kompatibilitas gspread.utils.convert_colors_to_hex_value() untuk mengonversi kamus menjadi string hex.
- tab_color = {"red": 1, "green": 0.5, "blue": 1}
+ tab_color = "#FF7FFF"
file.sheet1.update_tab_color(tab_color) - age = spreadsheet.lastUpdateTime
+ age = spreadsheet.get_lastUpdateTime()Worksheet.get_records Di v6 sekarang Anda hanya bisa mendapatkan semua catatan sheet, menggunakan Worksheet.get_all_records() . Metode Worksheet.get_records() telah dihapus. Anda bisa mendapatkan beberapa catatan menggunakan pengambilan Anda sendiri dan menggabungkannya dengan gspread.utils.to_records() .
+ from gspread import utils
all_records = spreadsheet.get_all_records(head=1)
- some_records = spreadsheet.get_all_records(head=1, first_index=6, last_index=9)
- some_records = spreadsheet.get_records(head=1, first_index=6, last_index=9)
+ header = spreadsheet.get("1:1")[0]
+ cells = spreadsheet.get("6:9")
+ some_records = utils.to_records(header, cells) Di versi 5 ada banyak peringatan untuk menandai fitur/fungsi/metode yang tidak digunakan lagi. Mereka dapat dibungkam dengan menyetel variabel lingkungan GSPREAD_SILENCE_WARNINGS ke 1
gspread.Worksheet.__init__ gc = gspread.service_account(filename="google_credentials.json")
spreadsheet = gc.open_by_key("{{key}}")
properties = spreadsheet.fetch_sheet_metadata()["sheets"][0]["properties"]
- worksheet = gspread.Worksheet(spreadsheet, properties)
+ worksheet = gspread.Worksheet(spreadsheet, properties, spreadsheet.id, gc.http_client) # You can open a spreadsheet by its title as it appears in Google Docs
sh = gc . open ( 'My poor gym results' ) # <-- Look ma, no keys!
# If you want to be specific, use a key (which can be extracted from
# the spreadsheet's url)
sht1 = gc . open_by_key ( '0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE' )
# Or, if you feel really lazy to extract that key, paste the entire url
sht2 = gc . open_by_url ( 'https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl' ) sh = gc . create ( 'A new spreadsheet' )
# But that new spreadsheet will be visible only to your script's account.
# To be able to access newly created spreadsheet you *must* share it
# with your email. Which brings us to… sh . share ( '[email protected]' , perm_type = 'user' , role = 'writer' ) # Select worksheet by index. Worksheet indexes start from zero
worksheet = sh . get_worksheet ( 0 )
# By title
worksheet = sh . worksheet ( "January" )
# Most common case: Sheet1
worksheet = sh . sheet1
# Get a list of all worksheets
worksheet_list = sh . worksheets () worksheet = sh . add_worksheet ( title = "A worksheet" , rows = "100" , cols = "20" ) sh . del_worksheet ( worksheet ) # With label
val = worksheet . get ( 'B1' ). first ()
# With coords
val = worksheet . cell ( 1 , 2 ). value # Get all values from the first row
values_list = worksheet . row_values ( 1 )
# Get all values from the first column
values_list = worksheet . col_values ( 1 ) from gspread . utils import GridRangeType
list_of_lists = worksheet . get ( return_type = GridRangeType . ListOfLists )Terima hanya sel dengan nilai di dalamnya.
>> > worksheet . get ( "A1:B4" )
[[ 'A1' , 'B1' ], [ 'A2' ]]Terima array persegi panjang di sekitar sel dengan nilai di dalamnya.
>> > worksheet . get ( "A1:B4" , pad_values = True )
[[ 'A1' , 'B1' ], [ 'A2' , '' ]]Terima array yang cocok dengan ukuran permintaan terlepas dari apakah nilainya kosong atau tidak.
>> > worksheet . get ( "A1:B4" , maintain_size = True )
[[ 'A1' , 'B1' ], [ 'A2' , '' ], [ '' , '' ], [ '' , '' ]] # Find a cell with exact string value
cell = worksheet . find ( "Dough" )
print ( "Found something at R%sC%s" % ( cell . row , cell . col ))
# Find a cell matching a regular expression
amount_re = re . compile ( r'(Big|Enormous) dough' )
cell = worksheet . find ( amount_re ) # Find all cells with string value
cell_list = worksheet . findall ( "Rug store" )
# Find all cells with regexp
criteria_re = re . compile ( r'(Small|Room-tiering) rug' )
cell_list = worksheet . findall ( criteria_re ) # Update a single cell
worksheet . update_acell ( 'B1' , 'Bingo!' )
# Update a range
worksheet . update ([[ 1 , 2 ], [ 3 , 4 ]], 'A1:B2' )
# Update multiple ranges at once
worksheet . batch_update ([{
'range' : 'A1:B2' ,
'values' : [[ 'A1' , 'B1' ], [ 'A2' , 'B2' ]],
}, {
'range' : 'J42:K43' ,
'values' : [[ 1 , 2 ], [ 3 , 4 ]],
}]) from gspread . utils import ValueRenderOption
# Get formatted cell value as displayed in the UI
>> > worksheet . get ( "A1:B2" )
[[ '$12.00' ]]
# Get unformatted value from the same cell range
>> > worksheet . get ( "A1:B2" , value_render_option = ValueRenderOption . unformatted )
[[ 12 ]]
# Get formula from a cell
>> > worksheet . get ( "C2:D2" , value_render_option = ValueRenderOption . formula )
[[ '=1/1024' ]] import gspread
from gspread . utils import ValidationConditionType
# Restrict the input to greater than 10 in a single cell
worksheet . add_validation (
'A1' ,
ValidationConditionType . number_greater ,
[ 10 ],
strict = True ,
inputMessage = 'Value must be greater than 10' ,
)
# Restrict the input to Yes/No for a specific range with dropdown
worksheet . add_validation (
'C2:C7' ,
ValidationConditionType . one_of_list ,
[ 'Yes' ,
'No' ,]
showCustomUi = True
)Dokumentasi: https://gspread.readthedocs.io/
Cara terbaik untuk mendapatkan jawaban atas sebuah pertanyaan adalah dengan bertanya di Stack Overflow dengan tag gspread.
Daftar kontributor
Harap pastikan untuk meluangkan waktu sejenak dan membaca Kode Etik.
Silakan laporkan bug dan sarankan fitur melalui Masalah GitHub.
Sebelum membuka terbitan, telusuri pelacak untuk kemungkinan duplikat. Jika Anda menemukan duplikat, silakan tambahkan komentar yang mengatakan bahwa Anda juga mengalami masalah.
Dokumentasi sama pentingnya dengan kode. Jika Anda tahu cara membuatnya lebih konsisten, mudah dibaca, dan jelas, silakan kirimkan pull request. File dokumentasi ada di folder docs , menggunakan markup reStructuredText dan dirender oleh Sphinx.
Harap pastikan untuk membaca Panduan Berkontribusi sebelum membuat permintaan penarikan.
