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

importgspread# Login with your Google accountgc=gspread.login('thedude@abid.es', 'password')
# Open a worksheet from spreadsheet with one shotwks=gc.open("Where is the money Lebowski?").sheet1wks.update_acell('B2', "it's down there somewhere, let me take another look.")
# Fetch a cell rangecell_list=wks.range('A1:B7')

Alternate Authorization Using OAuth2

importgspread# Login with your Google accountgc=gspread.authorize(OAuth2Credentials)
# Open a worksheet from spreadsheet with one shotwks=gc.open("Where is the money Lebowski?").sheet1

OAuth2Credentials 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:

1. In your webbrowser logon to gmail/google with the account you're using in gspread.
2. 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 Docssh=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 urlsht2=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 zeroworksheet=sh.get_worksheet(0)
# By titleworksheet=sh.worksheet("January")
# Most common case: Sheet1worksheet=sh.sheet1# Get a list of all worksheetsworksheet_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 labelval=worksheet.acell('B1').value# With coordsval=worksheet.cell(1, 2).value

Getting All Values From a Row or a Column

# Get all values from the first rowvalues_list=worksheet.row_values(1)
# Get all values from the first columnvalues_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 valuecell=worksheet.find("Dough")
print("Found something at R%sC%s"% (cell.row, cell.col))
# Find a cell matching a regular expressionamount_re=re.compile(r'(Big|Enormous) dough')
cell=worksheet.find(amount_re)

Finding All Matched Cells

# Find all cells with string valuecell_list=worksheet.findall("Rug store")
# Find all cells with regexpcriteria_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.valuerow_number=cell.rowcolumn_number=cell.col

Updating Cells

worksheet.update_acell('B1', 'Bingo!')
# Orworksheet.update_cell(1, 2, 'Bingo!')
# Select a rangecell_list=worksheet.range('A1:C7')
forcellincell_list:
cell.value='O_o'# Update in batchworksheet.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 install

From PyPI

pip install gspread

If you're on easy_install, it's:

easy_install gspread

Documentation

API Reference

Testing

1. Go to Google Drive and create an empty spreadsheet you will use for testing.

2. Create a configuration file from config dummy:

   cp tests/tests.config.example tests/tests.config

3. Open tests.config with your favorite editor and fill up config parameters with your testing spreadsheet's info.

4. Install Nose.

5. Run tests:

   nosetests

Suggestions & Code Contribution

• Javier Candeira
• Peter "Mash" Morgan
• ptlu
• aginor

All contributors

Feedback

Please report bugs and suggest features via GitHub Issues.