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 3.8+.

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([[1, 2], [3, 4]], "A1")

# Or update a single cell
wks.update_acell("B42", "it's down there somewhere, let me take another look.")

# Format the header
wks.format('A1:B1', {'textFormat': {'bold': True}})

v5.12 to v6.0 Migration Guide

Upgrade from Python 3.7

Python 3.7 is end-of-life. gspread v6 requires a minimum of Python 3.8.

Change Worksheet.update arguments

The first two arguments (values & range_name) have swapped (to range_name & values). Either swap them (works in v6 only), or use named arguments (works in v5 & v6).

As well, values can no longer be a list, and must be a 2D array.

- file.sheet1.update([["new", "values"]])
+ file.sheet1.update([["new", "values"]]) # unchanged

- file.sheet1.update("B2:C2", [["54", "55"]])
+ file.sheet1.update([["54", "55"]], "B2:C2")
# or
+ file.sheet1.update(range_name="B2:C2", values=[["54", "55"]])

More

See More Migration Guide

Change colors from dictionary to text

v6 uses hexadecimal color representation. Change all colors to hex. You can use the compatibility function gspread.utils.convert_colors_to_hex_value() to convert a dictionary to a hex string.

- tab_color = {"red": 1, "green": 0.5, "blue": 1}
+ tab_color = "#FF7FFF"
file.sheet1.update_tab_color(tab_color)

Switch lastUpdateTime from property to method

- age = spreadsheet.lastUpdateTime
+ age = spreadsheet.get_lastUpdateTime()

Replace method Worksheet.get_records

In v6 you can now only get all sheet records, using Worksheet.get_all_records(). The method Worksheet.get_records() has been removed. You can get some records using your own fetches and combine them with gspread.utils.to_records().

+ from gspread import utils
  all_records = spreadsheet.get_all_records(head=1)
- some_records = spreadsheet.get_all_records(head=1, first_index=6, last_index=9)
- some_records = spreadsheet.get_records(head=1, first_index=6, last_index=9)
+ header = spreadsheet.get("1:1")[0]
+ cells = spreadsheet.get("6:9")
+ some_records = utils.to_records(header, cells)

Silence warnings

In version 5 there are many warnings to mark deprecated feature/functions/methods. They can be silenced by setting the GSPREAD_SILENCE_WARNINGS environment variable to 1

Add more data to gspread.Worksheet.__init__

  gc = gspread.service_account(filename="google_credentials.json")
  spreadsheet = gc.open_by_key("{{key}}")
  properties = spreadsheet.fetch_sheet_metadata()["sheets"][0]["properties"]
- worksheet = gspread.Worksheet(spreadsheet, properties)
+ worksheet = gspread.Worksheet(spreadsheet, properties, spreadsheet.id, gc.http_client)

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

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

from gspread.utils import GridRangeType
list_of_lists = worksheet.get(return_type=GridRangeType.ListOfLists)

Getting a range of values

Receive only the cells with a value in them.

>>> worksheet.get("A1:B4")
[['A1', 'B1'], ['A2']]

Receive a rectangular array around the cells with values in them.

>>> worksheet.get("A1:B4", pad_values=True)
[['A1', 'B1'], ['A2', '']]

Receive an array matching the request size regardless of if values are empty or not.

>>> worksheet.get("A1:B4", maintain_size=True)
[['A1', 'B1'], ['A2', ''], ['', ''], ['', '']]

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_acell('B1', 'Bingo!')

# Update a range
worksheet.update([[1, 2], [3, 4]], 'A1:B2')

# Update multiple ranges at once
worksheet.batch_update([{
    'range': 'A1:B2',
    'values': [['A1', 'B1'], ['A2', 'B2']],
}, {
    'range': 'J42:K43',
    'values': [[1, 2], [3, 4]],
}])

Get unformatted cell value or formula

from gspread.utils import ValueRenderOption

# Get formatted cell value as displayed in the UI
>>> worksheet.get("A1:B2")
[['$12.00']]

# Get unformatted value from the same cell range
>>> worksheet.get("A1:B2", value_render_option=ValueRenderOption.unformatted)
[[12]]

# Get formula from a cell
>>> worksheet.get("C2:D2", value_render_option=ValueRenderOption.formula)
[['=1/1024']]

Add data validation to a range

import gspread
from gspread.utils import ValidationConditionType

# Restrict the input to greater than 10 in a single cell
worksheet.add_validation(
  'A1',
  ValidationConditionType.number_greater,
  [10],
  strict=True,
  inputMessage='Value must be greater than 10',
)

# Restrict the input to Yes/No for a specific range with dropdown
worksheet.add_validation(
  'C2:C7',
   ValidationConditionType.one_of_list,
   ['Yes',
   'No',]
   showCustomUi=True
)

Documentation

Documentation: https://gspread.readthedocs.io/

Ask Questions

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

Contributors

List of contributors

How to Contribute

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

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.