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 渲染。
在发出拉取请求之前，请务必阅读贡献指南。
