gspread

by burnash

burnash / gspread

Google Sheets Python API

5.0K Stars 760 Forks Last release: 6 months ago (v3.6.0) MIT License 790 Commits 30 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:

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.