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')
# WARNING: The code above uses ClientLogin and was disabled # on April 20, 2015.# Please use OAuth2 authorization to access Google Sheets.# 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')

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.

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.