Need help with gspread?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

burnash
5.2K Stars 789 Forks MIT License 797 Commits 162 Opened issues

Description

Google Sheets Python API

Services available

!
?

Need anything else?

Contributors list

Google Spreadsheets Python API v4

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.

Installation

pip install gspread

Requirements: Python 2.7+ or Python 3+.

Basic Usage

  1. Create credentials in Google API Console

  2. 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}})

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') # 

Creating a Spreadsheet

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…

Sharing a Spreadsheet

sh.share('[email protected]', perm_type='user', role='writer')

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.get('B1').first()

With coords

val = worksheet.cell(1, 2).value

Getting 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)

Updating Cells

# 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

Contributors

How to Contribute

Please make sure to take a moment and read the Code of Conduct.

Ask Questions

The best way to get an answer to a question is to ask on Stack Overflow with a gspread tag.

Report Issues

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.

Improve Documentation

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.

Contribute code

Please make sure to read the Contributing Guide before making a pull request.

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.