latest workflow GitHub version PyPi downloads doc
Simple interface for working with Google Sheets.
Features:
- Open a spreadsheet by title, key or URL.
- Read, write, and format cell ranges.
- Sharing and access control.
- Batching updates.
pip install gspread
Requirements: Python 3.6+.
-
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}})
# 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()
# 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.