gspread
v6.1.4
用於使用 Google Sheets 的簡單介面。
特徵：
pip install gspread需求：Python 3.8+。
在 Google API 控制台中建立憑證
開始使用 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 已結束生命。 gspread v6 至少需要 Python 3.8。
Worksheet.update參數前兩個參數（ values ＆ range_name ）已交換（至range_name ＆ values ）。要麼交換它們（僅適用於 v6），要麼使用命名參數（適用於 v5 和 v6）。
同樣， values不能再是列表，而必須是二維數組。
- 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 使用十六進位顏色表示。將所有顏色變更為十六進制。您可以使用相容性函數gspread.utils.convert_colors_to_hex_value()將字典轉換為十六進位字串。
- 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在 v6 中，您現在只能使用Worksheet.get_all_records()取得所有工作表記錄。 Worksheet.get_records()方法已被刪除。您可以使用自己的提取來取得一些記錄，並將它們與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)在版本 5 中，有許多警告標記已棄用的特性/函數/方法。可以透過將GSPREAD_SILENCE_WARNINGS環境變數設為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 )僅接收其中包含值的儲存格。
>> > worksheet . get ( "A1:B4" )
[[ 'A1' , 'B1' ], [ 'A2' ]]接收單元格周圍的矩形數組，其中包含值。
>> > worksheet . get ( "A1:B4" , pad_values = True )
[[ 'A1' , 'B1' ], [ 'A2' , '' ]]接收與請求大小相符的數組，無論值是否為空。
>> > 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
)文件：https://gspread.readthedocs.io/
獲得問題答案的最佳方法是使用 gspread 標籤在 Stack Overflow 上提問。
貢獻者名單
請務必花一點時間閱讀行為準則。
請透過 GitHub Issues 回報錯誤並建議功能。
在開啟問題之前，請在追蹤器中搜尋可能的重複項。如果您發現重複的內容，請新增評論，說明您也遇到了該問題。
文件與程式碼一樣重要。如果您知道如何使其更加一致、可讀和清晰，請提交拉取請求。文件檔案位於docs資料夾中，使用 reStructuredText 標記並由 Sphinx 渲染。
在發出拉取請求之前，請務必閱讀貢獻指南。
