Google Spreadsheets Python API
Manage your spreadsheets with gspread in Python.
Features:
- Open a spreadsheet by its title or url.
- Extract range, entire row or column values.
- Independent of Google Data Python client library.
- Python 3 support.
Basic Usage
import gspread
# Login with your Google account
gc = gspread.login('thedude@abid.es', 'password')
# Open a worksheet from spreadsheet with one shot
wks = gc.open("Where is the money Lebowski?").sheet1
wks.update_acell('B2', "it's down there somewhere, let me take another look.")
# Fetch a cell range
cell_list = wks.range('A1:B7')Alternate Authorization Using OAuth2
import gspread
# Login with your Google account
gc = gspread.authorize(OAuth2Credentials)
# Open a worksheet from spreadsheet with one shot
wks = gc.open("Where is the money Lebowski?").sheet1OAuth2Credentials must be an object with a valid access_token attribute, such as one created with the oauth2client library from Google. See "Using OAuth2 for Authorization" for more information.
Two Factor Authorization
In case your Google Account protected with Two Factor Authorization, you have to create an application-specific password and use your email to login as usual.
Otherwise you will get an AuthenticationError: Unable to authenticate. 403 code when trying to login.
Authorization Problems
If you're not using Two Factor Authorization and you get gspread.exceptions.AuthenticationError: Unable to authenticate. 403 code while trying to logon with gspread, do this:
- In your webbrowser logon to gmail/google with the account you're using in gspread.
- Visit the URLs https://www.google.com/settings/security/lesssecureapps and https://accounts.google.com/DisplayUnlockCaptcha and follow the instructions on those pages.
More Examples
Opening a Spreadsheet
# 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')Selecting a Worksheet
# 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()Creating a Worksheet
worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")Deleting a Worksheet
sh.del_worksheet(worksheet)Getting a Cell Value
# With label
val = worksheet.acell('B1').value
# With coords
val = worksheet.cell(1, 2).valueGetting All Values From a Row or a Column
# 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)Getting All Values From a Worksheet as a List of Lists
list_of_lists = worksheet.get_all_values()Finding a Cell
# 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)Finding All Matched Cells
# 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)Cell Object
Each cell has a value and coordinates properties.
value = cell.value
row_number = cell.row
column_number = cell.colUpdating Cells
worksheet.update_acell('B1', 'Bingo!')
# Or
worksheet.update_cell(1, 2, 'Bingo!')
# Select a range
cell_list = worksheet.range('A1:C7')
for cell in cell_list:
cell.value = 'O_o'
# Update in batch
worksheet.update_cells(cell_list)Requirements
Python 2.6+ or Python 3+
Installation
From GitHub
git clone https://github.com/burnash/gspread.git
cd gspread
python setup.py installFrom PyPI
pip install gspreadIf you're on easy_install, it's:
easy_install gspreadDocumentation
Testing
-
Go to Google Drive and create an empty spreadsheet you will use for testing.
-
Create a configuration file from config dummy:
cp tests/tests.config.example tests/tests.config
-
Open
tests.configwith your favorite editor and fill up config parameters with your testing spreadsheet's info. -
Install Nose.
-
Run tests:
nosetests
Suggestions & Code Contribution
Feedback
Please report bugs and suggest features via GitHub Issues.