Google Spreadsheets Python API
Simple interface for working with Google Sheets.
Features:
pip install gspread
Requires Python 3.7+
Start using 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('A1', [[1, 2], [3, 4]])
# Or update a single cell
wks.update('B42', "it's down there somewhere, let me take another look.")
# Format the header
wks.format('A1:B1', {'textFormat': {'bold': True}})
Python 3.7 is end-of-life. gspread v6 requires a minimum of Python 3.8.
Worksheet.update
argumentsThe first two arguments (values
& range_name
) have swapped (to range_name
& values
). Either swap them (works in v6 only), or use named arguments (works in v5 & v6).
As well, values
can no longer be a list, and must be a 2D array.
- 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 uses hexadecimal color representation. Change all colors to hex. You can use the compatibility function gspread.utils.convert_colors_to_hex_value()
to convert a dictionary to a hex string.
- 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()
In version 5 there are many warnings to mark deprecated feature/functions/methods.
They can be silenced by setting the GSPREAD_SILENCE_WARNINGS
environment variable to 1
# 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('otto@example.com', 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)
list_of_lists = worksheet.get_values()
Receive only the cells with a value in them
>>> worksheet.get_values("A1:B4")
[['A1', 'B1'], ['A2']]
Receive a lists of lists matching the requested size regardless if values are empty or not
>>> worksheet.get_values("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('B1', 'Bingo!')
# Update a range
worksheet.update('A1:B2', [[1, 2], [3, 4]])
# Update multiple ranges at once
worksheet.batch_update([{
'range': 'A1:B2',
'values': [['A1', 'B1'], ['A2', 'B2']],
}, {
'range': 'J42:K43',
'values': [[1, 2], [3, 4]],
}])
Documentation: https://gspread.readthedocs.io/
The best way to get an answer to a question is to ask on Stack Overflow with a gspread tag.
Please make sure to take a moment and read the Code of Conduct.
Please report bugs and suggest features via the GitHub Issues.
Before opening an issue, search the tracker for possible duplicates. If you find a duplicate, please add a comment saying that you encountered the problem as well.
Documentation is as important as code. If you know how to make it more consistent, readable and clear, please submit a pull request. The documentation files are in docs
folder, use reStructuredText markup and rendered by Sphinx.
Please make sure to read the Contributing Guide before making a pull request.