gspread

KO | EN

gitlite — search

#javascript #typescript #rust #linux #macos #nodejs #windows #awesome #cli #vue #pytorch #security #mcp #ios

(★ 7,500)

Google Sheets Python API

File Explorer

Download Latest Version (.zip)

- - bug_report.md
  - config.yml
  - feature_request.md
- - main.yaml
  - release.yaml
- CODE_OF_CONDUCT.md
- CONTRIBUTING.md
- dependabot.yaml
- - layout.html
- - auth.rst
  - client.rst
  - exceptions.rst
  - http_client.rst
  - index.rst
  - top-level.rst
  - utils.rst
- advanced.rst
- community.rst
- conf.py
- index.rst
- oauth2.rst
- requirements.txt
- user-guide.rst
- __init__.py
- auth.py
- cell.py
- client.py
- exceptions.py
- http_client.py
- py.typed
- spreadsheet.py
- urls.py
- utils.py
- worksheet.py
- - CellTest.test_a1_value.json
  - CellTest.test_define_named_range.json
  - CellTest.test_delete_named_range.json
  - CellTest.test_equality.json
  - CellTest.test_merge_cells.json
  - CellTest.test_numeric_value.json
  - CellTest.test_properties.json
  - CellTest.test_update_named_range.json
  - CellTest.test_update_named_range_no_args.json
  - ClientTest.test_access_non_existing_spreadsheet.json
  - ClientTest.test_access_private_spreadsheet.json
  - ClientTest.test_add_timeout.json
  - ClientTest.test_client_export_spreadsheet.json
  - ClientTest.test_copy.json
  - ClientTest.test_create.json
  - ClientTest.test_import_csv.json
  - ClientTest.test_list_spreadsheet_files.json
  - ClientTest.test_no_found_exeption.json
  - ClientTest.test_open_all_has_metadata.json
  - ClientTest.test_open_by_key_has_metadata.json
  - ClientTest.test_open_by_name_has_metadata.json
  - ClientTest.test_openall.json
  - SpreadsheetTest.test_add_del_worksheet.json
  - SpreadsheetTest.test_bad_json_api_error.json
  - SpreadsheetTest.test_creationTime_prop.json
  - SpreadsheetTest.test_export_spreadsheet.json
  - SpreadsheetTest.test_get_all_worksheet_values.json
  - SpreadsheetTest.test_get_lastUpdateTime.json
  - SpreadsheetTest.test_get_worksheet.json
  - SpreadsheetTest.test_get_worksheet_by_id.json
  - SpreadsheetTest.test_lastUpdateTime_prop.json
  - SpreadsheetTest.test_properties.json
  - SpreadsheetTest.test_sheet1.json
  - SpreadsheetTest.test_timezone_and_locale.json
  - SpreadsheetTest.test_update_title.json
  - SpreadsheetTest.test_values_batch_get.json
  - SpreadsheetTest.test_values_get.json
  - SpreadsheetTest.test_worksheet.json
  - SpreadsheetTest.test_worksheet_iteration.json
  - SpreadsheetTest.test_worksheets.json
  - SpreadsheetTest.test_worksheets_exclude_hidden.json
  - WorksheetTest.test_acell.json
  - WorksheetTest.test_add_protected_range_normal.json
  - WorksheetTest.test_add_protected_range_warning.json
  - WorksheetTest.test_add_validation.json
  - WorksheetTest.test_append_row.json
  - WorksheetTest.test_append_row_with_empty_value.json
  - WorksheetTest.test_append_row_with_empty_value_and_table_range.json
  - WorksheetTest.test_attributes.json
  - WorksheetTest.test_auto_resize_columns.json
  - WorksheetTest.test_basic_filters.json
  - WorksheetTest.test_batch_clear.json
  - WorksheetTest.test_batch_get.json
  - WorksheetTest.test_batch_merged_cells.json
  - WorksheetTest.test_batch_update.json
  - WorksheetTest.test_cell.json
  - WorksheetTest.test_cell_return_first.json
  - WorksheetTest.test_clear.json
  - WorksheetTest.test_clear_tab_color.json
  - WorksheetTest.test_copy_cut_range.json
  - WorksheetTest.test_delete_cols.json
  - WorksheetTest.test_delete_columns_blocks.json
  - WorksheetTest.test_delete_dimension_blocks_validation.json
  - WorksheetTest.test_delete_protected_range.json
  - WorksheetTest.test_delete_row.json
  - WorksheetTest.test_delete_rows_blocks.json
  - WorksheetTest.test_find.json
  - WorksheetTest.test_findall.json
  - WorksheetTest.test_format.json
  - WorksheetTest.test_freeze.json
  - WorksheetTest.test_get_all_records.json
  - WorksheetTest.test_get_all_records_different_header.json
  - WorksheetTest.test_get_all_records_duplicate_keys.json
  - WorksheetTest.test_get_all_records_numericise_unformatted.json
  - WorksheetTest.test_get_all_records_pad_more_than_one_key.json
  - WorksheetTest.test_get_all_records_pad_one_key.json
  - WorksheetTest.test_get_all_records_pad_values.json
  - WorksheetTest.test_get_all_records_value_render_options.json
  - WorksheetTest.test_get_all_records_with_all_values_blank.json
  - WorksheetTest.test_get_all_records_with_blank_final_headers.json
  - WorksheetTest.test_get_all_records_with_keys_blank.json
  - WorksheetTest.test_get_all_records_with_some_values_blank.json
  - WorksheetTest.test_get_all_values.json
  - WorksheetTest.test_get_all_values_date_time_render_options.json
  - WorksheetTest.test_get_all_values_title_is_a1_notation.json
  - WorksheetTest.test_get_and_get_values_have_same_signature.json
  - WorksheetTest.test_get_merge_cells_and_unmerge_cells.json
  - WorksheetTest.test_get_notes.json
  - WorksheetTest.test_get_notes_2nd_sheet.json
  - WorksheetTest.test_get_returns_ValueRange_with_metadata.json
  - WorksheetTest.test_get_values_and_combine_merged_cells.json
  - WorksheetTest.test_get_values_and_maintain_size.json
  - WorksheetTest.test_get_values_can_emulate_get_with_kwargs.json
  - WorksheetTest.test_get_values_merge_cells_from_centre_of_sheet.json
  - WorksheetTest.test_get_values_merge_cells_outside_of_range.json
  - WorksheetTest.test_get_values_merge_cells_with_named_range.json
  - WorksheetTest.test_get_values_returns_padded_get_as_listoflists.json
  - WorksheetTest.test_get_values_with_args_or_kwargs.json
  - WorksheetTest.test_group_columns.json
  - WorksheetTest.test_group_rows.json
  - WorksheetTest.test_hide_columns_rows.json
  - WorksheetTest.test_hide_gridlines.json
  - WorksheetTest.test_hide_show_worksheet.json
  - WorksheetTest.test_insert_cols.json
  - WorksheetTest.test_insert_row.json
  - WorksheetTest.test_range.json
  - WorksheetTest.test_range_get_all_values.json
  - WorksheetTest.test_range_reversed.json
  - WorksheetTest.test_range_unbounded.json
  - WorksheetTest.test_reorder_worksheets.json
  - WorksheetTest.test_resize.json
  - WorksheetTest.test_set_tab_color.json
  - WorksheetTest.test_show_gridlines.json
  - WorksheetTest.test_sort.json
  - WorksheetTest.test_update_acell.json
  - WorksheetTest.test_update_and_get.json
  - WorksheetTest.test_update_cell.json
  - WorksheetTest.test_update_cell_multiline.json
  - WorksheetTest.test_update_cell_objects.json
  - WorksheetTest.test_update_cell_unicode.json
  - WorksheetTest.test_update_cells.json
  - WorksheetTest.test_update_cells_noncontiguous.json
  - WorksheetTest.test_update_cells_unicode.json
  - WorksheetTest.test_update_tab_color.json
  - WorksheetTest.test_update_title.json
  - WorksheetTest.test_update_works_with_swapped_values_and_range.json
  - WorksheetTest.test_worksheet_notes.json
  - WorksheetTest.test_worksheet_update_index.json
- __init__.py
- cell_test.py
- client_test.py
- conftest.py
- http_client_test.py
- spreadsheet_test.py
- utils_test.py
- worksheet_test.py
.gitignore
.readthedocs.yaml
HISTORY.rst
LICENSE.txt
lint-requirements.txt
pyproject.toml
README.md
test-requirements.txt
tox.ini

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Google Spreadsheets Python API v4

![main workflow](https://img.shields.io/github/actions/workflow/status/burnash/gspread/main.yaml?logo=github)
![GitHub licence](https://img.shields.io/pypi/l/gspread?logo=github)
![GitHub downloads](https://img.shields.io/github/downloads-pre/burnash/gspread/latest/total?logo=github)
![documentation](https://img.shields.io/readthedocs/gspread?logo=readthedocs)
![PyPi download](https://img.shields.io/pypi/dm/gspread?logo=pypi)
![PyPi version](https://img.shields.io/pypi/v/gspread?logo=pypi)
![python version](https://img.shields.io/pypi/pyversions/gspread?style=pypi)

## Maintainer needed

We are sorry to announce that we are currently unable to maintain Gspread.

We are looking for new maintainers to keep up the good work.
Feel free to reach out to us using this issue [#1570](https://github.com/burnash/gspread/issues/1570)

## Overview

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

```sh
pip install gspread
```

Requirements: Python 3.8+.

## Basic Usage

1. [Create credentials in Google API Console](http://gspread.readthedocs.org/en/latest/oauth2.html)

2. Start using gspread

```python
import gspread

# First you need access to the Google API. Based on the route you
# chose in Step 1, call either service_account(), oauth() or api_key().
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](https://devguide.python.org/versions/). 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.

```diff
- 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

<details><summary>See More Migration Guide</summary>

### 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.

```diff
- 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

```diff
- 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()`.

```diff
+ 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__`

```diff
  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)
```

</details>

## More Examples

### Opening a Spreadsheet

```python
# 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

```python
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

```python
sh.share('otto@example.com', perm_type='user', role='writer')
```

### Selecting a Worksheet

```python
# 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

```python
worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")
```

### Deleting a Worksheet

```python
sh.del_worksheet(worksheet)
```

### Getting a Cell Value

```python
# With label
val = worksheet.get('B1').first()

# With coords
val = worksheet.cell(1, 2).value
```

### Getting All Values From a Row or a Column

```python
# 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

```python
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.

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

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

```python
>>> 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.

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

### Finding a Cell

```python
# 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

```python
# 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

```python
# 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

```python
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

```python
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/][Documentation]

[Documentation]: https://gspread.readthedocs.io/en/latest/

### Ask Questions

The best way to get an answer to a question is to ask on [Stack Overflow with a gspread tag](http://stackoverflow.com/questions/tagged/gspread?sort=votes&pageSize=50).

## Contributors

[List of contributors](https://github.com/burnash/gspread/graphs/contributors)

## How to Contribute

Please make sure to take a moment and read the [Code of Conduct](https://github.com/burnash/gspread/blob/master/.github/CODE_OF_CONDUCT.md).

### Report Issues

Please report bugs and suggest features via the [GitHub Issues](https://github.com/burnash/gspread/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](https://gspread.readthedocs.io/) 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`](https://github.com/burnash/gspread/tree/master/docs) folder, use [reStructuredText](http://www.sphinx-doc.org/en/master/usage/restructuredtext/index.html#rst-index) markup and rendered by [Sphinx](http://www.sphinx-doc.org/).

### Contribute code

Please make sure to read the [Contributing Guide](https://github.com/burnash/gspread/blob/master/.github/CONTRIBUTING.md) before making a pull request.

gspread

File Explorer

Today's picks

Was this content helpful?

Recent Feedback

gspread

File Explorer

Today's picks

Was this content helpful?

Recent Feedback

Download README